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
About

hi there, I am software engineer, working in e-commerce company and passionate about all thing digital. On this blog, I share my experiments with different technology.

Posted in SQL Server

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

What people say about post?
seo hizmeti on Awesome wordpress ecommerce…

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 38 other followers

%d bloggers like this: