Function to generate incrementing numbers in SQL Server

Is there any way to select from a function and have it return i开发者_Python百科ncrementing numbers?

For example, do this:

SELECT SomeColumn, IncrementingNumbersFunction() FROM SomeTable

And have it return:

SomeColumn | IncrementingNumbers
--------------------------------
some text  | 0
something  | 1
foo        | 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

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;

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

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

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;

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