How to number rows without using cursors with MS SQL Server
- Most often, people use loops and temporary tables to number the rows.
- Microsoft SQL Server introduced in SQL Server 2005 new functions, including the ROW_NUMBER() to do this.
Data Used
USE sql911;
go
SELECT EMPNO AS 'Emp #',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,'**') AS 'Dept',
isnull(PHONENO,'**') AS 'Local',
HIREDATE AS 'Hired',
JOB AS 'Position'
FROM emps
ORDER BY lastname, firstnme;
go
go
SELECT EMPNO AS 'Emp #',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,'**') AS 'Dept',
isnull(PHONENO,'**') AS 'Local',
HIREDATE AS 'Hired',
JOB AS 'Position'
FROM emps
ORDER BY lastname, firstnme;
go
Emp # First Name Last Name Dept Local Hired Position ------ ------------ --------------- ---- ----- ----------------------- -------- 000150 BRUCE ADAMSON ** ** 1987-02-12 00:00:00.000 DESIGNER 000200 DAVID BROWN D11 4501 1981-03-03 00:00:00.000 DESIGNER 000050 JOHN GEYER E01 6789 1964-08-17 00:00:00.000 MANAGER 000340 JASON GOUNOT E21 5698 1962-05-05 00:00:00.000 FIELDREP 000010 CHRISTINE HAAS A00 3978 1980-01-01 00:00:00.000 PRES 000090 EILEEN# HENDERSON E11 5498 1985-08-15 00:00:00.000 MANAGER 000230 JAMES JEFFERSON D21 2094 1981-11-21 00:00:00.000 CLERK 000260 SYBIL\ JOHNSON D21 8953 1990-09-11 00:00:00.000 CLERK 000210 WILLIAM* JONES D11 0942 1994-04-11 00:00:00.000 DESIGNER 000030 SALLY! KWAN C01 4738 1990-04-05 00:00:00.000 MANAGER 000330 WING LEE E21 2103 1991-02-23 00:00:00.000 FIELDREP 000110 VINCENZO. LUCCHESSI A00 3490 1973-05-16 00:00:00.000 SALESREP 000220 JENNIFER] LUTZ ** ** 1983-08-29 00:00:00.000 DESIGNER 000240 SALVATORE[ MARINO D21 3780 1994-12-05 00:00:00.000 CLERK 000320 RAMLAL MEHTA ** 9990 1980-07-07 00:00:00.000 FIELDREP 000140 HEATHER NICHOLLS C01 1793 1991-12-15 00:00:00.000 ANALYST 000120 SEAN O CONNELL A00 2167 1978-12-05 00:00:00.000 CLERK 000290 JOHN PARKER E11 4502 1995-05-30 00:00:00.000 OPERATOR 000270 MARIA PEREZ D21 9001 1995-09-30 00:00:00.000 CLERK 000160 ELIZABETH PIANKA D11 3782 1992-10-11 00:00:00.000 DESIGNER 000070 EVA PULASKI D21 7831 1995-09-30 00:00:00.000 MANAGER 000130 DOLORES, QUINTANA C01 4578 1986-07-28 00:00:00.000 ANALYST 000280 ETHEL/ SCHNEIDER E11 8997 1982-03-24 00:00:00.000 OPERATOR 000180 MARILYN SCOUTTEN D11 1682 1988-07-07 00:00:00.000 DESIGNER 000310 MAUDE SETRIGHT E11 3332 1979-09-12 00:00:00.000 OPERATOR 000250 DANIEL SMITH D21 0961 1984-10-30 00:00:00.000 CLERK 000300 PHILIP SMITH E11 2095 1987-06-19 00:00:00.000 OPERATOR 000100 THEODORE SPENSER ** ** 1995-06-19 00:00:00.000 MANAGER 000060 IRVING@ STERN D11 6423 1988-09-14 00:00:00.000 MANAGER 000020 MICHAEL THOMPSON ** 3476 1988-10-10 00:00:00.000 MANAGER 000190 JAMES& WALKER D11 2986 1989-07-26 00:00:00.000 DESIGNER 000170 MASATOSHI% YOSHIMURA D11 2890 1993-09-15 00:00:00.000 DESIGNER (32 row(s) affected)
Numbering the rows
SELECT CAST(ROW_NUMBER() OVER (ORDER BY lastname, firstnme) AS VARCHAR(6)) AS 'Row #',
EMPNO AS 'Emp #',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,'**') AS 'Dept',
isnull(PHONENO,'**') AS 'Local',
HIREDATE AS 'Hired',
JOB AS 'Position'
FROM emps
ORDER BY lastname, firstnme;
go
EMPNO AS 'Emp #',
FIRSTNME AS 'First Name',
LASTNAME AS 'Last Name',
isnull(WORKDEPT,'**') AS 'Dept',
isnull(PHONENO,'**') AS 'Local',
HIREDATE AS 'Hired',
JOB AS 'Position'
FROM emps
ORDER BY lastname, firstnme;
go
Row # Emp # First Name Last Name Dept Local Hired Position ------ ------ ------------ --------------- ---- ----- ----------------------- -------- 1 000150 BRUCE ADAMSON ** ** 1987-02-12 00:00:00.000 DESIGNER 2 000200 DAVID BROWN D11 4501 1981-03-03 00:00:00.000 DESIGNER 3 000050 JOHN GEYER E01 6789 1964-08-17 00:00:00.000 MANAGER 4 000340 JASON GOUNOT E21 5698 1962-05-05 00:00:00.000 FIELDREP 5 000010 CHRISTINE HAAS A00 3978 1980-01-01 00:00:00.000 PRES 6 000090 EILEEN# HENDERSON E11 5498 1985-08-15 00:00:00.000 MANAGER 7 000230 JAMES JEFFERSON D21 2094 1981-11-21 00:00:00.000 CLERK 8 000260 SYBIL\ JOHNSON D21 8953 1990-09-11 00:00:00.000 CLERK 9 000210 WILLIAM* JONES D11 0942 1994-04-11 00:00:00.000 DESIGNER 10 000030 SALLY! KWAN C01 4738 1990-04-05 00:00:00.000 MANAGER 11 000330 WING LEE E21 2103 1991-02-23 00:00:00.000 FIELDREP 12 000110 VINCENZO. LUCCHESSI A00 3490 1973-05-16 00:00:00.000 SALESREP 13 000220 JENNIFER] LUTZ ** ** 1983-08-29 00:00:00.000 DESIGNER 14 000240 SALVATORE[ MARINO D21 3780 1994-12-05 00:00:00.000 CLERK 15 000320 RAMLAL MEHTA ** 9990 1980-07-07 00:00:00.000 FIELDREP 16 000140 HEATHER NICHOLLS C01 1793 1991-12-15 00:00:00.000 ANALYST 17 000120 SEAN O CONNELL A00 2167 1978-12-05 00:00:00.000 CLERK 18 000290 JOHN PARKER E11 4502 1995-05-30 00:00:00.000 OPERATOR 19 000270 MARIA PEREZ D21 9001 1995-09-30 00:00:00.000 CLERK 20 000160 ELIZABETH PIANKA D11 3782 1992-10-11 00:00:00.000 DESIGNER 21 000070 EVA PULASKI D21 7831 1995-09-30 00:00:00.000 MANAGER 22 000130 DOLORES, QUINTANA C01 4578 1986-07-28 00:00:00.000 ANALYST 23 000280 ETHEL/ SCHNEIDER E11 8997 1982-03-24 00:00:00.000 OPERATOR 24 000180 MARILYN SCOUTTEN D11 1682 1988-07-07 00:00:00.000 DESIGNER 25 000310 MAUDE SETRIGHT E11 3332 1979-09-12 00:00:00.000 OPERATOR 26 000250 DANIEL SMITH D21 0961 1984-10-30 00:00:00.000 CLERK 27 000300 PHILIP SMITH E11 2095 1987-06-19 00:00:00.000 OPERATOR 28 000100 THEODORE SPENSER ** ** 1995-06-19 00:00:00.000 MANAGER 29 000060 IRVING@ STERN D11 6423 1988-09-14 00:00:00.000 MANAGER 30 000020 MICHAEL THOMPSON ** 3476 1988-10-10 00:00:00.000 MANAGER 31 000190 JAMES& WALKER D11 2986 1989-07-26 00:00:00.000 DESIGNER 32 000170 MASATOSHI% YOSHIMURA D11 2890 1993-09-15 00:00:00.000 DESIGNER (32 row(s) affected)
- The ROW_NUMBER() OVER (ORDER BY lastname, firstnme) does all the work.
- The OVER (ORDER BY lastname, firstnme) should match the sort order of the ORDER BY.
- The OVER (ORDER BY lastname, firstnme) does not have match the sort order of the ORDER BY.
- The OVER (ORDER BY lastname, firstnme) should be covered by an index.
- The ROW_NUMBER() is a few thousand times faster than any cursor equivalent.
- The ROW_NUMBER() is a few times faster than the autojoin equivalent.
- The cast(ROW_NUMBER() OVER (ORDER BY lastname, firstnme) as varchar(6)) is used to set the column width.
- The default of ROW_NUMBER() is 20 chars wide.
- The isnull(WORKDEPT,'**') as 'Dept' is used because normally SQL Server will display NULL, which makes the listings difficult to read.