DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
 
 
SITE SEARCH SQLUSA.com VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
How to create a sequence column for the result set?

Starting with SQL Server 2005, the ROW_NUMBER function is available for sequence generation. Pre-2005, you can use the identity function to create a sequence. Following are example T-SQL scripts for results sequence numbering and sequence number generation.


-- SQL Server 2005 / SQL Server 2008 ROW_NUMBER()
SELECT ID=ROW_NUMBER() OVER (ORDER BY Name DESC),
       Department=Name
FROM AdventureWorks2008.HumanResources.Department
ORDER BY ID
/*
ID    Department
1     Tool Design
2     Shipping and Receiving
3     Sales
4     Research and Development
5     Quality Assurance
6     Purchasing
7     Production Control
8     Production
9     Marketing
10    Information Services
11    Human Resources
12    Finance
13    Facilities and Maintenance
14    Executive
15    Engineering
16    Document Control

*/
------------

-- SQL SELECT INTO temp table IDENTITY row numbering
SELECT

      IDENTITY(smallint, 1, 1) AS JobSeq,

      fname AS First,

      minit AS Middle,

      lname AS Last,

      job_lvl AS JobLevel,

      hire_date as HireDate

INTO #employee

FROM employee

------------

 

-- SQL sequence number generator table-valued function

CREATE FUNCTION fnGenerateSequentialNumbers

               (@Limit INT)

RETURNS @Sequence TABLE(SequentialNumber INT)

AS

  BEGIN

    DECLARE  @RunningValue INT

    SET @RunningValue = 1

    WHILE @RunningValue <= @Limit

      BEGIN

        INSERT @Sequence

        VALUES(@RunningValue)

        SET @RunningValue = @RunningValue + 1

      END

    RETURN

  END

GO

 

SELECT *  FROM   dbo.fnGenerateSequentialNumbers(10)

GO

/*

SequentialNumber

1

2

3

4

5

6

7

8

9

10

*/

------------


Exam Prep 70-461
SQL 2016 DESIGN & PROGRAMMING
 
 
 
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE