Interesting Unique Id generation Problem

[virang_21]

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.

[Ramesh S]

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.

[virang_21]

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.

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

[sknake]

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.