seq number

[elams]

How to generate a seq number in TSQL. My need is for every record
insert ,I need to insert a seq number paded with letter 'I'.
Please help me.

Adv Thanks mate

--
Posted via http://dbforums.com

[Erland Sommarskog]

elams (me*********@dbforums.com) writes:

How to generate a seq number in TSQL. My need is for every record
insert ,I need to insert a seq number paded with letter 'I'.

The normal way to achieve something like this is to use one of the
following to methods:

o Declare a column to have the IDENTITY property and pick up the
value with the scope_identity() function.
o Retrieve the current max value and then add one:
SELECT @nextid = coalesce(MAX(id), 0) + FROM tbl (UPDLOCK)
This must be done in a transaction to prevent two processes getting
the same ID.

Now, if I understand you correctly, you want the value to be I1, I2, I3,
etc. In this case, none of the methods above work directly, because
the I confuse matters.

My first attempt would be to see if I could somehow get this "I" out of
the design, because that would simplify things a lot.

If business reasons require presence of the letter, I would look into
to make the column with I1, I2 etc a computed column, where the forumla
would be 'I' + str(ltrim(id)), where id is the name of a numeric column
as above. Probably the column should be indexed, so that you can use it
for lookup from user input. Reference to the table from other tables
would be on the numeric column though.


--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mystery Man]

I agree with Erland in that the best way (by far) is to use Identity
columns. However, rather than having a computed column to store the
number prefixed with the 'I' character, I would advocate a database
view (see below) or add the extra 'I' character at the business level.
That way derived data is not stored.

eg

create view WhatAView as
select id, ('I' + cast(id as varchar)) "seq", name from sysobjects
go
Select * from WhatAView
go

BUT,BUT,BUT, I would also be extremely reluctant to use the other
solution to get the next key from the database table itself (ie
MAX(id)) for two reasons:

(1) Different user sessions may end up getting the same ID. Consider
the following scenario:

(a) User 1 gets an ID
(b) User 2 gets an ID (will be the same as user 1 as we have not saved
any data)
(c) User 1 persists data - this is OK
(d) User 2 persists data - key violation due to the duplicate ID

(2) Ths may lead to poor performance due to contention on tbl

Instead (if I had to), I would use a database table that simply stored
the next available key. This gets updated when the next key is
fetched.

begin trans
select id from nextKey
update nextKey set id = id + 1
end trans

I would be interested to here what others do with regards to isolation
levels and locking with respect to this pattern to acheive the best
performance and no collisions.

[Erland Sommarskog]

Mystery Man (Pr************@hotmail.com) writes:

I agree with Erland in that the best way (by far) is to use Identity
columns. However, rather than having a computed column to store the
number prefixed with the 'I' character, I would advocate a database
view (see below) or add the extra 'I' character at the business level.
That way derived data is not stored.
Neither are computed columns materialized, unless you index it. I assumed
that if you need to generate such a value, you probably also want to use
for retrieval, in which case an index may be required.

The same applies if you go for a view.
solution to get the next key from the database table itself (ie
MAX(id)) for two reasons:

(1) Different user sessions may end up getting the same ID. Consider
the following scenario:
You need to do this:

BEGIN TRANSACTION
SELECT @id = coalesce(MAX(id), 0) FROM tbl (UPDLOCK)
INSERT tbl (...)
SELECT @id, ....
COMMIT TRANSACTION

This avoids duplicates id:s.
(2) Ths may lead to poor performance due to contention on tbl

Instead (if I had to), I would use a database table that simply stored
the next available key. This gets updated when the next key is
fetched.

This is an alternative solution, but you may have the same contention
problems.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp