Function to generate incrementing numbers in SQL Server

Question

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

Answer 1

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

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

Answer 2

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

Answer 3

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

Answer 4

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;