Assigning Consecutive Zero-Based IDs in SQL Server

A technical problem I often deal with when using SQL data is that I need the items in a table to have zero-based IDs. This allows the “zid” to act as an array index in algorithms like Dijkstra’s shortest path algorithm. One of many approaches is to use SQL Server’s ROW_NUMBER function. The idea is best explained by a concrete example. Suppose you have a table of employee information:

use master
create database dbJunk
go

use dbJunk

create table tblEmps
(
eid bigint primary key,
last varchar(20) not null
)

insert into tblEmps values(333, 'Chung')
insert into tblEmps values(222, 'Baker')
insert into tblEmps values(444, 'Dunne')
insert into tblEmps values(111, 'Aiden')

Here eid is an employee ID. You can create a zero-based ID lookup table like so:

create table tblEidZid 
(
 eid bigint not null,
 zid int not null
)

And now this command will populate the lookup table:

insert into tblEidZid
select eid, (ROW_NUMBER() over (order by eid)) - 1 as zid
from tblEmps

The result would be:

eid  zid
========
111   0
222   1
333   2
444   3

The ROW_NUMBER function makes this task very easy.

Advertisements
This entry was posted in Machine Learning, Software Test Automation. Bookmark the permalink.

3 Responses to Assigning Consecutive Zero-Based IDs in SQL Server

  1. mvaneerde says:

    Note that ROW_NUMBER() is a Microsoft extension to SQL. If you’re using a non-Microsoft solution, you can achieve much the same effect as follows:

    insert into tblEidZid
    select eid, (select count(*) from tblEmps as e2 where e2.eid < e.eid) as zid
    from tblEmps as e

  2. mvaneerde says:

    I take that back – I see ROW_NUMBER() is in the standard.

  3. Actually, I thought ROW_NUMBER() was a Microsoft-specific function too. Your trick is clever, and could be useful for database systems that don’t support ROW_NUMBER.

Comments are closed.