MS SQL

MS SQL RSS

Interesting Unique Id generation Problem

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jan 2009

Posts: 13

Reputation: virang_21 is an unknown quantity at this point

Solved Threads: 0

virang_21

Offline

Newbie Poster

Interesting Unique Id generation Problem

Sep 15th, 2009

Hi All SQL Gurus,

I am working on a project for a college. I have to generate a unique student Id for each student. I know I can use IDENTITY column to achieve this but I have rather complex situation here. My studentID field is string because they want it in a format of
StudentID = Last Two Digit of the year + term they study + 3digit number.

They have 8 different terms so for example if student enroll in 2009 for term 2 his ID will be 0902XXX. where XXX is the unique number part. They want this unique number part to start at 1 for each year and term. So 1st student in year 2009 of term 1 will have Id 0901001 and 1st student of the term 2 will have Id 0902001.

How can I achieve this ?

One of the solution that I though of is to create a table with fields Year, term and currentNumber. My issue with this solution is how can I keep currentNumber field unique for each request. I am making a web application potentially used by many members at same time.

Please suggest any alternative solution.

My Blog: http://technobird.blogspot.com/

•

Join Date: Jun 2009

Posts: 439

Reputation: Ramesh S will become famous soon enough

Solved Threads: 82

Ramesh S

Offline

Posting Pro in Training

Re: Interesting Unique Id generation Problem

Sep 16th, 2009

As per your requirement, the three digit number will be the 'maximum value of 3 digit number (for the year and term ) + 1'

Therefore you calculate the number(ie max + 1) only when you save the student details to the database. Therefore the currentNumber will be unique for each student. Also make the combination of Year, term and currentNumber as composite primary key. Hence concurrent requests cannot have the same maximum currentNumber.

Hope this will help you.

•

Join Date: Jan 2009

Posts: 13

Reputation: virang_21 is an unknown quantity at this point

Solved Threads: 0

virang_21

Offline

Newbie Poster

Re: Interesting Unique Id generation Problem

Sep 21st, 2009

Well I resolve this issue by using a table with just one field ID and made it IDENTITY and set its max value to 999 and then used the following procedure to get the next number for 3 digit number part. My year and term part is in another table and I use those values to create my studentId and then update all the tables in the database with new studentId in a single transaction.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE PROCEDURE [dbo].[get_next_id](@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator];
IF @Current<1000
BEGIN
	UPDATE IdGenerator SET @ID = ID, ID = ID + 1
END
ELSE
BEGIN
	UPDATE IdGenerator SET @ID =1,ID = 2 
END
CREATE PROCEDURE [dbo].[get_next_id](@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator];
IF @Current<1000
BEGIN
	update IdGenerator SET @ID = ID, ID = ID + 1
END
ELSE
BEGIN
	update IdGenerator SET @ID =1,ID = 2 
END

My Blog: http://technobird.blogspot.com/

•

Join Date: Feb 2009

Posts: 3,215

Reputation: sknake has much to be proud of

Solved Threads: 573

sknake

Offline

.NET Enthusiast

Re: Interesting Unique Id generation Problem

Sep 21st, 2009

You should use a trigger on the insert to generate the id instead of a sproc to get the next value. Even then your sproc should probably be a UDF. In your case if the transaction is rolled back the ID will still be incremented even if the record is not inserted I believe.

Scott Knake
Custom Software Development
Apex Software, Inc.