Generate sequence number using ROW_NUMBER() without sorting the actual column

As we know about Ranking functions to return a ranking/sequence value for each row in a partition based on a specific/set of column(s). Right ?

Let me define a table and values

If Object_Id('dbo.SequenceData') Is Null

Create Table [dbo].SequenceData
(
Col1 Varchar(5)
)
Go

TRUNCATE TABLE [dbo].SequenceData

Go

Insert [dbo].SequenceData
Select 'A' Union All
Select 'D' Union All
Select 'A' Union All
Select 'C' Union All
Select 'B'
Go

SELECT * FROM [dbo].SequenceData
Go

let me generate a sequence number for the value

SELECT ROW_NUMBER() OVER(),* FROM [dbo].SequenceData

GO

Msg 4112, Level 15, State 1, Line 31

The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

But, It's not working, since we need ORDER BY clause specified as per the syntax. right ?

So. let me include ORDER BY clause and see...

SELECT ROW_NUMBER() OVER(ORDER BY Col1),* FROM [dbo].SequenceData

GO

No..No..No, My actual column "Col1" value should not be Sorted. What should we do ?

Let us try something like this

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),* FROM [dbo].SequenceData

GO

We have specified a virtual column in ORDER BY clause which will return always NULL. So, The actual data will not be sorted.

We get a sequence for the column value without sorting it - "AS IS"

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Can we generate sequence number witout Loop ? YES. Using Common Table Expression - Recursion (CTE Recursion)

When we execute the following script, It throws an error..

;

WITH CTEs

AS

(

SELECT 1 AS Number

UNION ALL

SELECT Number + 1 FROM CTEs

)

SELECT * FROM CTEs

GO

 

Error:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Normally, The default recursion limit in 100. OK

How to genarete upto 1000. Is there any option here ? YES

;

WITH CTEs

AS

(
SELECT 1 AS Number

UNION ALL

SELECT Number + 1 FROM CTEs

)

SELECT * FROM CTEs OPTION(MAXRECURSION 1000)

GO

It generates 1 through 1000 with error when it exceeds the value(1000)

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1000 has been exhausted before statement completion.

Then, what would be the limit here ? The maximum limit is 32767 and default is 100

Ok. How to produce the sequence number (1 to 1000) without any ERROR using CTE Recursion

;

WITH CTEs

AS

(

SELECT 1 AS Number

UNION ALL

SELECT Number + 1 FROM CTEs WHERE [Number] <1000
)

SELECT * FROM CTEs OPTION(MAXRECURSION 1000)

GO 

Now, It generates the sequence number 1 through 1000 without any Error

How to update sequence number for existing records ?

CREATE TABLE #Table1
(
Id INT NULL,
Column1 VARCHAR(50)
)
GO

INSERT #Table1(Column1) VALUES('Failover Clustering')
INSERT #Table1(Column1) VALUES('Mirroring')
INSERT #Table1(Column1) VALUES('Replication')
INSERT #Table1(Column1) VALUES('Log Shipping')
INSERT #Table1(Column1) VALUES('Database Snapshot')
INSERT #Table1(Column1) VALUES('Database Backup')
GO

DECLARE @Seq INT
SELECT @Seq = 0
UPDATE #Table1 SET Id = @Seq , @Seq = @Seq + 1
GO

SELECT * FROM #Table1
GO

DROP TABLE #Table1
GO