|
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
*/
------------
|
