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.