Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have Employee table with Empolyee ID column EmpID (nvarchar(5)) ,
and I want to make EmpID's value to

00001  
00002  
00003  
00004  
....
....    

How can I make Custom Auto-Generated Sequences likes this ?

share|improve this question

closed as off-topic by Jon Seigel, StanleyJohns, Nick Chammas, Mat, dezso Sep 22 '13 at 8:52

If this question can be reworded to fit the rules in the help center, please edit the question.

1  
You shouldn't be crossposting –  marc_s Sep 21 '13 at 10:04
4  
This question appears to be off-topic because it was cross-posted on Stack Overflow and has an accepted answer there. –  Jon Seigel Sep 22 '13 at 2:41
add comment

2 Answers 2

I would recommend to let SQL Server handle the auto-incrementing part by using an INT IDENTITY column. Don't do this in your C# code - that's a database task.

For your second column that you want to have in a specific, formatted fashion, use a computed column based on that identity column.

Try something like this:

CREATE TABLE dbo.Employee
(ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
 EmpID AS RIGHT('0000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED, 

 .... your other columns here.......
)

That way, when you insert rows into the table, you'll get:

ID    EmpID
---------------
 1    00001
 2    00002
........
17    00017
18    00018
......
78    00078

The ID column is your actual primary and clustering key, it's small, fast, never changes - perfect for a clustering key. And your column EmpID is always automatically generated from the ID and shows the value in the formatted fashion as you wanted.

share|improve this answer
    
So cross-asking is discouraged, but cross-answering is okay? –  Jon Seigel Sep 22 '13 at 2:45
add comment

Sequences like this are easy with SQL Server 2012; unfortunately they are not supported in SQL Server 2008 R2. However, you can create your own method of generating sequences like:

WITH cteSequence ( SeqNo) as
(
      SELECT 1
      UNION ALL
      SELECT SeqNo + 1
      FROM cteSequence
      WHERE SeqNo < 1000000
)

SELECT TOP 100 * FROM cteSequence
OPTION ( MAXRECURSION 0);
GO

Look at this site for further details (which is where I obtained the above code!)

http://www.sqlusa.com/bestpractices2005/sequence/

share|improve this answer
    
Sequences and identity handle serialization of the values automatically -- there needs to be wayyyy more going on to write custom code that does this correctly. –  Jon Seigel Sep 22 '13 at 2:44
    
I agree @jon-seigel - had I actually read the entire question I would have seen that an IDENTITY() would have been way better. I thought I learned that skill ages ago. D'oh! –  Max Vernon Sep 23 '13 at 21:30
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.