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
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?
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.
