473,390 Members | 1,334 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,390 software developers and data experts.

seq number


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
Jul 20 '05 #1
3 4305
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
Jul 20 '05 #2
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.
Jul 20 '05 #3
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
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Shay Hurley | last post by:
this is probably a stupid question so apologies in advance. I am trying to format a number to look like a phone number with "-"'s between the numbers etc e.g. 15554256987 should be formatted as...
8
by: EAS | last post by:
Hey, I'm new to python (and programming in general) so I'll prolly be around here a lot... Anyways, I've found out how to make a "guess my number game" where the player guesses a number between...
11
by: don | last post by:
Ok, this is a homework assignment, but can you help me out anyway...... I need a routine for figuring out if a number inputted by the user is a prime number or not...... all I'm asking for is Not...
27
by: Gaijinco | last post by:
Sooner or later everytime I found recreational programming challenges I stumble with how I test if a number is has decimal places differnt than 0? For example if I want to know if a number is a...
13
by: Ron | last post by:
Hi all I'm deciding whether to use the PK also as an account number, invoice number, transaction number, etc that the user will see for the respective files. I understand that sometimes a...
19
by: gk245 | last post by:
Trying to write a program that will figure out if a number is perfect or not. Here is my logic: 1) Read in the number 2) Split it up (number - 1) 3) Put all the split up numbers into an...
4
by: SweetLeftFoot | last post by:
Hello, i have designed some code that works out the first 250 prime numbers and prints them to the screen. However i need to implement 2 functions, one of which returns a 1 if the number is a prime...
4
by: fatimahtaher | last post by:
Hi, I am supposed to create a program that generates a random number and then asks the user to guess the number (1-100). The program tells the user if he guessed too high or too low. If he...
5
by: silversnake | last post by:
I'm trying to write a program that take a input number and prints if is a prime numbers but is not working for instance, it says that 4 is prime while 5 is not. can anyone see what the problem is ....
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.