Sequence with TSQL

We keep an unique column in every table in the database to have primary key clustered index created on it and mostly they are IDENTITY or ROWGUID. Have you ever faced a situation where you need to update a column with sequence number in an existing table or temp table? I will explain 4 different methods here to achieve the same.

SETUP:

CREATE TABLE #Employee
(Name varchar(100)
,Hiredate datetime
,Stat varchar(10))
INSERT INTO #Employee (Name, Hiredate, Stat)
VALUES ('Adams  Alex', '2001-01-01 00:00:00.000', 'Active')
, ('Brown   Barry', '2000-01-01 00:00:00.000', 'On Leave')
, ('Osako   Lee', '2009-06-11 00:00:00.000', 'Active')
, ('Kennson David', '1996-03-16 00:00:00.000', 'Has Tenure  ')
, ('Haire   Terry', '2001-11-15 00:00:00.000', 'On Leave')
, ('Smith   Sally', '1989-04-01 00:00:00.000', 'Active')
Method 01: (Using IDENTITY)

ALTER TABLE #Employee
ADD ID INT IDENTITY(1,1)
GO
SELECT * FROM #Employee
GO
--Droping the column for next examples
ALTER TABLE #Employee
drop column ID
Method 02: (Using local variable)

ALTER TABLE #Employee
ADD ID INT
GO
DECLARE @i INT = 0
UPDATE #Employee
SET @i = ID = @i + 1
SELECT * FROM #Employee
--OR--
GO
DECLARE @i INT = 0
UPDATE #Employee SET ID = NULL
UPDATE #Employee SET Id = @i , @i = @i + 1
SELECT * FROM #Employee
Method 03: (Using CTE)

UPDATE #Employee SET ID = NULL
;WITH CTE AS (
SELECT ROW_NUMBER() OVER(ORDER BY Hiredate) AS ID, Name FROM #Employee
)
UPDATE a
SET ID = b.ID
FROM #Employee a
INNER JOIN CTE b ON a.Name = b.name
SELECT * FROM #Employee
Method 04: (Using SEQUENCE)

UPDATE #Employee SET ID = NULL
GO
CREATE SEQUENCE idnum
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
GO
UPDATE #Employee
SET id = NEXT value FOR idnum
SELECT * FROM #Employee
GO
DROP SEQUENCE idnum --Droping the sequence
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s