Microsoft SQL server does not support a method of identifying the row numbers for records stored on disk, although there are a number of different techniques to associate a sequential number with a row. Today, we'll cover how you can number records by having an identity column, using a temporary table, altering tables, using Self Join, usiung a cursor, and numbering groups of records. See the full tutorial here.

Sequentially Numbering Records by Having an Identity Column


SET NOCOUNT ON
CREATE TABLE SEQ_NUMBER_EXAMPLE (
   RECORD_NUMBER INT IDENTITY (1,1),
   DESCRIPTION VARCHAR(40))
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FIRST RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('SECOND RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('THIRD RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FOURTH RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FIFTH RECORD')
SELECT * FROM SEQ_NUMBER_EXAMPLE
DROP TABLE SEQ_NUMBER_EXAMPLE



Sequentially Numbering Records by Using a Temporary Table


create table #HireDate (rank int identity, 
                    HireDate datetime,
                    LastName nvarchar(20),
                    FirstName nvarchar(20)
                    )
insert into #HireDate (HireDate, LastName, FirstName)
  select Hiredate, LastName, Firstname
    from northwind.dbo.employees
    where Title = 'Sales Representative'
    order by HireDate
Select cast(rank as char(4)) as Rank, 
       cast(hiredate as varchar(23)) as HireDate,
       LastName, 
       FirstName from #HireDate
Drop table #HireDate



Sequentially Numbering Records by Altering Table


set nocount on
alter table pubs.dbo.titles
 add rownum int identity(1,1)
go
select rownum, title from pubs.dbo.titles
   where rownum < 6
   order by rownum
go
alter table pubs.dbo.titles
drop column rownum



Sequentially Numbering Records by Using a Self Join


SELECT count(*) RecNum,
       a.LastName
     FROM Northwind.dbo.Employees a join
          Northwind.dbo.Employees b
          on a.LastName >= b.LastName
     group by a.LastName
     order by a.LastName



Sequentially Number Records by Using a Cursor


declare @i int
declare @name varchar(200)
declare authors_cursor cursor
   for select rtrim(au_lname) + ', ' + rtrim(au_fname) from pubs.dbo.authors
         where au_lname < 'G' 
       order by au_lname, au_fname
open authors_cursor
fetch next from authors_cursor into @name
set @i = 0
print 'recnum name'
print '------ -------------------------------'
while @@fetch_status = 0
begin
  set @i = @i + 1
  print cast(@i as char(7)) + rtrim(@name)
  fetch next from authors_cursor into @name
end
close authors_cursor
deallocate authors_cursor



Sequentially Numbering Groups of Records


select OD.OrderID, LineNumber, OD.ProductID, UnitPrice, Quantity, Discount 
  from  Northwind.dbo.[Order Details] OD
       join 
        (select count(*) LineNumber, 
                a.OrderID, a.ProductID
                from Northwind.dbo.[Order Details] A join
                     Northwind.dbo.[Order Details] B 
                     on  A.ProductID >= B.ProductID
                         and A.OrderID = B.OrderID
                  group by A.OrderID, A.ProductID) N
          on OD.OrderID= N.OrderID and 
             OD.ProductID = N.ProductID
    where OD.OrderID < 10251
    order by OD.OrderID, OD.ProductID



DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

More Database Code Articles
More By Gregory A. Larsen

 

developerWorks - FREE Tools!

NEW! Driving Business Success with Rational Process Library Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance. FREE! Go There Now!

NEW! Achieving True Agility -- How process can change the behavior of your tools Achieving true agility is a never-ending effort. We will showcase how you can become agile incrementally, a few practices at the time.Which practices should any agile team strive to adopt? What additional practices should you consider based on your needs to scale? Adopting practices are however made much easier with the right tool support. What about if your tools adapt to your practices? We will take a look at how the Jazz technology can be leveraged to make your process change the behavior of your tools. FREE! Go There Now!

NEW! Addressing software-as-a-service challenges using Tivoli security and WebSphere solutions Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base. FREE! Go There Now!

NEW! Build Web services with transport-level security using Rational Application Developer V7, Part 1: Build Web services and Web services clients Build secure Web services with transport-level security using IBM Rational Application Developer V7 and IBM WebSphere Application Server V6.1. Follow this three-part series for step-by-step instructions about how to develop Web services and clients, configure HTTP basic authentication, and configure HTTP over SSL (HTTPS). This first part of the series walks you through building a Web service for a simple calculator application. You generate and test two different types of Web services clients: a Java Platform, Enterprise Edition (Java EE) client and a stand-alone Java client. You also handle user-defined exceptions in Web services. FREE! Go There Now!

NEW! Download DB2 9.5 for Linux, Unix, and Windows Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML. FREE! Go There Now!

NEW! Download the free Web Application Security eKit Discover how IBM Rational AppScan Standard Edition can help you detext vulnerabilities in your web applications in the Web Application Security eKit. IBM Rational AppScan is a leading suite of automated web application security solutions that scan and test for common Web application vulnerabilities. The new Web Application Security eKit provides you with valuable resources, including white papers, demos, and additional information on the benefits of testing your Web applications. FREE! Go There Now!

NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code. FREE! Go There Now!

NEW! Improve your build process with IBM Rational Build Forge, Part 2: Automate builds for a real-world Tomcat project Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available. FREE! Go There Now!

NEW! Rational Talks to You: Manage RUP-based CMMI initiatives Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered! FREE! Go There Now!

NEW! Try the IBM SOA Sandbox for People Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity. FREE! Go There Now!

All FREE IBM® developerWorks Tools!