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

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.

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.