Padding an auto-incremented Int column to ten zeros

Good day gentleman,
Is it possible to pad columns of Int data type with zeros?

I currently have a primary key column of data type Int which is auto-incremented by 1. My problem is I need to pad the auto-incremented ID's with ten zeros so, 0000000009 instead of 9.

I can pad the ID's in the c# dataaccess layer, but i need to generate/store them padded in the database to allow direct reporting.

I am using Enterprise Manager and i can see a Formula field when the table is in design view, can i use this? i am just guessing here.

Any comments or pointers would be a big help.What you are discussing is a display issue. An int is, by definition, isolated from any particular display formatting. It is just a number. You could add an additional column (nvarchar) and have a trigger on inserts that populates that nvarchar() column with a string value with 0 padding.

Overlapping Sets

I have the following table structure

CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I need to be able to select unique overlapping sets of data based on the minval and maxval.

Simple Example
minval maxval
5 15
16 25
10 20

query would produce
minval maxval
5 10
11 15
16 20
21 25

More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100

query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100

Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Extra points for doing your homework assignment for you?|||Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Gosh - generous and gracious. How super.|||I'll give myself extra credit

ALTER PROCEDURE [dbo].[usp_Select_Disc]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select minval, maxval into #QtyRange from qdisc

declare @.minval int
declare @.maxval int
declare @.xminval int
declare @.xmaxval int

declare @.pmaxval int
declare @.pmaxva2 int

declare @.loopflg bit
set @.loopflg = 1
while @.loopflg = 1
begin
set @.loopflg = 0
DECLARE curSel CURSOR FOR select minval, maxval from #QtyRange order by minval
open curSel
fetch next from curSel into @.minval, @.maxval
WHILE (@.@.FETCH_STATUS <> -1)
begin

set @.xminval = null
set @.xmaxval = null

select top(1) @.xminval = minval, @.xmaxval = maxval from #QtyRange where minval > @.minval or maxval > @.maxval order by minval

if @.maxval > @.xminval
begin
set @.pmaxval = @.maxval
if @.minval = @.xminval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.xminval where minval = @.minval and maxval = @.maxval
update #QtyRange set minval = @.xminval + 1, maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
update #QtyRange set maxval = @.xminval - 1 where minval = @.minval and maxval = @.maxval

if @.xmaxval > @.pmaxval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
if @.xmaxval < @.pmaxval
insert into #QtyRange values (@.xmaxval + 1, @.pmaxval)
set @.loopflg = 1
end

if @.maxval = @.xminval
begin
set @.pmaxval = @.maxval

if @.minval = @.maxval
begin
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
else
begin
update #QtyRange set maxval = @.pmaxval - 1 where minval = @.minval and maxval = @.maxval
if @.xminval <> @.xmaxval
begin
insert into #QtyRange values (@.pmaxval, @.pmaxval)
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
end
end
fetch next from curSel into @.minval, @.maxval
end
Close curSel
DEALLOCATE curSel
end

select distinct(a.minval), a.maxval, sum(qdisc.perval) from #QtyRange a
inner join qdisc on a.minval >= qdisc.minval and a.maxval <= qdisc.maxval
group by a.minval,a.maxval
order by minval
END|||I'll give myself extra credit

You shouldn't, you used a cursor.|||You shouldn't, you used a cursor.
I'm not sure it can be done without a cursor or some other form of looping. I see no way to do it with a simple SELECT statement.|||neither do I, but that was his self-professed requirement for extra points.|||No no no. That was the requirement for YOU to get extra points. He, of course, is free to give himself as much credit as he wants.|||how many points do i need to get a free ship?|||this worked for both the data sets provided by u. i have tried with a couple of other sets and appears to be working. interested to know if it worked at your end or not...

create table #tt1 (Id int identity (1,1), Val int )
insert into #tt1 (Val) select Val from (
select Minval as Val from QDisc
union all
select Maxval as Val from QDisc
) A order by 1

select B.Id,A.Val AVal, B.Val BVal, C.Val CVal into #tt2
from #tt1 A, #tt1 B, #tt1 C where A.Id+1 =* B.Id and C.Id-1 =* B.Id

update #tt2 set BVal=BVal-1 where AVal+1=BVal
update #tt2 set CVal=Null where BVal+1=CVal
update #tt2 set CVal=CVal-1 from #tt2 A where A.CVal= (select BVal from #tt2 where #tt2.id = A.id+1 and BVal=CVal)

select
BVal+ case when BVal=(select min(BVal) from #tt2) then 0 when BVal= CVal then 0 else 1 end as MinVal,
CVal as MaxVal
from #tt2
where CVal is not null

Overlapping Indexes

In examining one of our DBs there is a sales table with the following fields:

WDate: SMALLDATETIME PK
StoreID INT PK
ItemID: INT PK
UnitsSold: SMALLINT
TotalSales: REAL
SalesCode: CHAR(1)

There are also other attributes that do not pertain to the question.

The following Indexes are in the Sales table:

PK_Sales: (Clustered) Composit Primary Key
WDate, StoreID, ItemID

IX_Sales_SalesCode: (Non-Clustered)
SalesCode

IX_Sales_Cover: (Non-Clustered)
WDate, StoreID, ItemID, UnitsSold, TotalSales

Typical queries include:
Querying by the 3 field PK
Querying by the 3 field PK + Sales Code and summing UnitsSold and Total Sales
Querying by the 3 field PK and summing UnitsSold and Total Sales

We have several versions of this same DB with different Data in it for different Store Chains. Since we must keep 2 years of history in the DB, the sales table can be quite large. One of our versions of the DB has nearly 1 billion records and the indexing seems to run quite efficiently. In other words no huge amount of time spend querying (Keep in mind there are nearly a billion records.)

My question is:
Do these indexes look correct? Obviously the PK index is fine but are the other 2 indexes ever being hit? In other words, are the 2 indexes other that the PK index worth keeping or are they just taking up disk space and degradding performance?

Thanks in advance for any help on this.
mcatet

You have multiple ways to find out
(1) you can look at the query plan generated for each query and see what indexes are being used
(2) you can use DMV sys.dm_db_index_usage_stats to find out the usage statistics of each of the indexes since the SQL server started. Please refer to BOL for more details. There are other index related DMVs that you may find useful

Without knowing the text of your queries (we need to know what columns are being selected and what predicated are being used) and the selectivity of each predicate, it is hard to know what indexes will be used.

Some observations:
Query-1: If you are using range predicates and the select clause has columns not covered by indexx-3, then this is definitely useful.
Query-3: since it is covered by index-3 keys, it may be useful as it will avoid accessing the datapage.

|||Besides the advices from Sunil you can consider also using Index Tuning Wizard (in SQL Server 2000) or Database Tuning Advisor (in SQL Server 2005). The best is to provide representative sample of the workload to the tool.

Overlapping Indexes

In examining one of our DBs there is a sales table with the following fields:

WDate: SMALLDATETIME PK
StoreID INT PK
ItemID: INT PK
UnitsSold: SMALLINT
TotalSales: REAL
SalesCode: CHAR(1)

There are also other attributes that do not pertain to the question.

The following Indexes are in the Sales table:

PK_Sales: (Clustered) Composit Primary Key
WDate, StoreID, ItemID

IX_Sales_SalesCode: (Non-Clustered)
SalesCode

IX_Sales_Cover: (Non-Clustered)
WDate, StoreID, ItemID, UnitsSold, TotalSales

Typical queries include:
Querying by the 3 field PK
Querying by the 3 field PK + Sales Code and summing UnitsSold and Total Sales
Querying by the 3 field PK and summing UnitsSold and Total Sales

We have several versions of this same DB with different Data in it for different Store Chains. Since we must keep 2 years of history in the DB, the sales table can be quite large. One of our versions of the DB has nearly 1 billion records and the indexing seems to run quite efficiently. In other words no huge amount of time spend querying (Keep in mind there are nearly a billion records.)

My question is:
Do these indexes look correct? Obviously the PK index is fine but are the other 2 indexes ever being hit? In other words, are the 2 indexes other that the PK index worth keeping or are they just taking up disk space and degradding performance?

Thanks in advance for any help on this.
mcatet

You have multiple ways to find out
(1) you can look at the query plan generated for each query and see what indexes are being used
(2) you can use DMV sys.dm_db_index_usage_stats to find out the usage statistics of each of the indexes since the SQL server started. Please refer to BOL for more details. There are other index related DMVs that you may find useful

Without knowing the text of your queries (we need to know what columns are being selected and what predicated are being used) and the selectivity of each predicate, it is hard to know what indexes will be used.

Some observations:
Query-1: If you are using range predicates and the select clause has columns not covered by indexx-3, then this is definitely useful.
Query-3: since it is covered by index-3 keys, it may be useful as it will avoid accessing the datapage.

|||Besides the advices from Sunil you can consider also using Index Tuning Wizard (in SQL Server 2000) or Database Tuning Advisor (in SQL Server 2005). The best is to provide representative sample of the workload to the tool.

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph

> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Here is a copy of the SP:
/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@.Tag varchar(10),
@.Barcode varchar(10) output
AS
Declare @.eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @.Tag
Open cur_Marriage
Fetch next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @.Barcode = 9
Set @.eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @.Barcode = 9
Set @.eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@.Barcode = 9)
Begin
Execute sp_LogError 1, @.eventid, 0, @.Tag
End
GO
>--Original Message--
>I am trying to run a stored procedure but I get the
>following error:
>Server: Msg 248, Level 16, State 1, Procedure
>sp_MarriageLookup, Line 34
>The conversion of the varchar value '2820103430'
>overflowed an int column. Maximum integer value exceeded.
>Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
>The length of the @.Barcode variable is 10 characters and
>it is a nchar type variable so I don't understand
the 'int
>column' overflow issue.
>Also, the wierd thing is that I can run this same stored
>procedure on another server (similar database, just
>different data) and it works just fine. See below:
>Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
>I don't see anything noticibly different between the two
>databases.
>What causes this problem and how do I correct?
>Roger.
>.
>|||Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph
> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.