Auto-Number and Cumulative sum in SQL Server Query results

SQL Server developers and
database architects often find they have a need to sequence query results or generate
a cumulative sum for a group of rows in a table. SQL Server does not have a
Pseudo row-id similar to other RDBMS.

In this article, I am going to
guide the developers and database architects to use co-related sub-queries and/or
identity functions to generate such sequential numbers and cumulative summations
in query results.

Example 1:

Generate sequential
number for a table, which has at least one unique numeric column.

Let us consider we have a table
as shown below.


Use tempdb
go
Create table Mytable1 (au_id int, authors_name varchar(100))
Go
insert into MyTable1 select 100,’Mathew Arnold’
insert into MyTable1 select 140,’Keith Davis’
insert into MyTable1 select 76,’David Taylor’
insert into MyTable1 select 127,’Agatha Christy’
insert into MyTable1 select 12,’Sidney Sheldon’
go

Query

select au_id,authors_name from Mytable1 order by au_id

Results

12

Sidney
Sheldon

76

David
Taylor

100

Mathew
Arnold

127

Agatha
Christy

140

Keith
Davis

au_id is unique in this table, so
it is easy to use a co-related sub-query to generate sequential numbers.

Query


SELECT (SELECT count(au_id) FROM Mytable1 AS x WHERE x.au_id<= y.au_id) AS
Sequence, au_id,authors_name
FROM Mytable1 AS y order by au_id

Results

1

12

Sidney
Sheldon

2

76

David
Taylor

3

100

Mathew Arnold

4

127

Agatha
Christy

5

140

Keith
Davis

Note: "au_id" is a
unique column.

Example 2:

Generate unique
sequence numbers for a table that has no unique column.

Let us consider the table shown
below. For tables with no unique columns, it is easy to use the identity
function to generate unique sequence numbers.


Use tempdb
go
Create table Mytable2 (au_id int, authors_name varchar(100))
Go
insert into MyTable2 select 100,’Mathew Arnold’
insert into MyTable2 select 140,’Keith Davis’
insert into MyTable2 select 76,’David Taylor’
insert into MyTable2 select 127,’Agatha Christy’
insert into MyTable2 select 12,’Sidney Sheldon’
insert into MyTable2 select 12,’Mcarthur’
insert into MyTable2 select 76,’Alan Smiles’
insert into MyTable2 select 100,’Kreisler’
go

Query

select * from mytable2 order by au_id

Results

12

Sidney
Sheldon

12

Mcarthur

76

Alan
Smiles

76

David
Taylor

100

Mathew
Arnold

100

Kreisler

127

Agatha
Christy

140

Keith
Davis

Query


select identity(int,1,1) as Sequence, au_id,authors_name into #x from Mytable2 order by au_id
go
select * from #x
go
drop table #x
go

Results

1

12

Sidney
Sheldon

2

12

Mcarthur

3

76

Alan
Smiles

4

76

David
Taylor

5

100

Mathew
Arnold

6

100

Kreisler

7

127

Agatha
Christy

8

140

Keith
Davis

Latest Articles