Auto Increment userdefined id

Question

Hi can anyone give me an idea how to create an auto generated id like ED01,ED02 etc., so that when i am entering data the id should be automatically incremented

Answer 1

Arguably this would be better done on the client side but you could

• create a normal identity column
• add a computed column doing the formatting.

Test script

DECLARE @ED TABLE (
  ID INTEGER PRIMARY KEY IDENTITY(1, 1)
  , UserID AS 'ED' + CAST(ID AS VARCHAR(32))
  , I INTEGER
)

DECLARE @I INTEGER
SET @I = 0
WHILE @I < 100
BEGIN
  SET @I = @I + 1
  INSERT INTO @ED VALUES (@I)
END

SELECT * FROM @ED