|
|
Posted by Erland Sommarskog on 01/25/07 11:48
HandersonVA (handersonva@hotmail.com) writes:
> 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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|