CODEWITHSUNDEEP
sql-servert-sql
Brendan Long
Brendan Long

Reputation: 54312

Function to generate incrementing numbers in SQL Server

Is there any way to select from a function and have it return incrementing numbers?

For example, do this:

SELECT SomeColumn, IncrementingNumbersFunction() FROM SomeTable

And have it return:

SomeColumn | IncrementingNumbers
--------------------------------
some text  | 0
something  | 1
foo        | 2

Upvotes: 2

Views: 16466

Answers (5)

John Tadlock
John Tadlock

Reputation: 21

Starting with SQL 2012 versions and later, there is a built-in sequence feature.

Example:

-- sql to create the Sequence object
CREATE SEQUENCE dbo.MySequence
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO

-- use the sequence
SELECT NEXT VALUE FOR dbo.MySequence;
SELECT NEXT VALUE FOR dbo.MySequence;
SELECT NEXT VALUE FOR dbo.MySequence;

Upvotes: 2

Ivo
Ivo

Reputation: 3436

You could an auto increment identity column, or do I miss understand the question?

http://msdn.microsoft.com/en-us/library/Aa933196

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135171

On sql server 2005 and up you can use ROW_NUMBER()

SELECT SomeColumn, 
     ROW_NUMBER() OVER(Order by SomeColumn) as IncrementingNumbers
 FROM SomeTable

0n SQL Server 2000, you can use identity but if you have deletes you will have gaps

SQL 2000 code in case you have gaps in your regular table with an identity column, do an insert into a temp with identity and then select out of it

SELECT SomeColumn, 
     IDENTITY( INT,1,1) AS IncrementingNumbers
     INTO #temp
 FROM SomeTable
 ORDER BY SomeColumn


 SELECT * FROM #temp
 ORDER BY IncrementingNumbers

Upvotes: 11

DOK
DOK

Reputation: 32851

I think you're looking for ROW_NUMBER added in SQL Server 2005. it "returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition."

From MSDN (where there's plenty more) the following example returns the ROW_NUMBER for the salespeople in AdventureWorks2008R2 based on the year-to-date sales.

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Upvotes: 2

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181460

No, there is no sequence generation functions in SQLServer. You might find identity field types handy to resolve your current issue.

Upvotes: 1

Related Questions