00000001
00000002
00000003
...
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanksHanderson,
If you set up a column as an INTEGER IDENTITY column (see BOL), SQL Server
will not store leading zeros. You could get leading zeros out of your select
statments as follows:
select right('00000000' + cast(MyColumn as varchar(25)), 8)
But, the best thing to do is manage leading zeros at the application layer.
Don't have the database always doing that work for you.
-- Bill
"HandersonVA" <handersonva@.hotmail.comwrote in message
news:1169678553.956380.205080@.q2g2000cwa.googlegro ups.com...
Quote:
Originally Posted by
will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanks
>
type.
Thus (from BOL)
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
On Jan 24, 2:42 pm, "HandersonVA" <handerso...@.hotmail.comwrote:
Quote:
Originally Posted by
will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record
like that?
thanks
news:1169681707.525338.145700@.13g2000cwe.googlegro ups.com...
Quote:
Originally Posted by
>
When creating the table, use the Identity keyword, and an int or bigint
type.
>
NOTE: The numbers will be sequential but not necessarily contiguous.
If you have a rollback for example the numbers wioll be "used" up.
Quote:
Originally Posted by
>
Thus (from BOL)
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
>
>
>
>
On Jan 24, 2:42 pm, "HandersonVA" <handerso...@.hotmail.comwrote:
Quote:
Originally Posted by
>will it be possible to increase number as below automatically
>00000001
>00000002
>00000003
>...
>>
>whenever the row is inserted, number will be increased like above
>format.
>which data type should I select and do some other setting to record
>like that?
>thanks
>|||HandersonVA (handersonva@.hotmail.com) writes:
Quote:
Originally Posted by
will it be possible to increase number as below automatically
00000001
00000002
00000003
...
>
whenever the row is inserted, number will be increased like above
format.
which data type should I select and do some other setting to record like
that? thanks
First of all: do you need the numbers to be contiguous. If you cannot
accept gaps, you need to roll your own:
BEGIN TRANSACTION
SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl WITH (UPDLOCK)
INSERT tbl (id, ...)
VALUES (@.id, ...)
COMMIT TRANSACTION
If you want to includ the leading zeroes, I would recommend that you
add a computed column that you persist and can index:
idasstr AS replicate('0', 10 - len(ltrim(str(id))) + ltrim(str(id))
PERSISTED
(Note: the PERSISTED keyword is available in SQL 2005 only.)
If you don't contiguous numbers you can use IDENTITY instead, and this is
partiucularly important if you expect a high insertion frequency from
multiple clients, as the scheme above will incur a serialisation that
reduces throughput. Even with IDENTITY you can would have a computed
column with the leading zeroes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
