A SQL Server function to generate of sequential numbers
I would like to have a SQL Server function dbo.GetNextNumber(), which would generate sequential numbers for each call. As far as I understand this is impossible with a native T-SQL function as SQL Server insists the functions has to be deterministic. But, if you could show me a native T-SQL function that does this would really make my day.
I thought perhaps this could be possible to write using a CLR function. As CLR functions are static, the sequence numbers need to be stored in the calling context of the set operation, as storing it as a static variable would result in several connections using the same sequence, resulting in not-so-sequential numbers. I do not know enough about embedded CLR to see if set operation's (select, update, delete, insert) calling context is reachable from the CLR side.
At the end of the day, the following query
select dbo.GetNextNumber() from sysobjects
must return the result
1
2
3
4
5
It is OK if another function call to reset the context is necessary like
exec dbo.ResetSequenceNumbers()
To prevent some misunderstandings and reduce the chances of wasting your time answering wrong question, please note that I am not looking for an ID generation function for a table and I am aware of some hacks (albeit using a proc not a function) that involves some temp tables with identity columns. The ROW_NUMBER() function is close but it also does not cut.
Thanks a lot for any responses
Kemal
P.S. It is amazing that SQL Server does have a built-in function for that. A function (provided that it cannot be used in joins and where clauses) is really easy to do and extremely useful, but for some reason it is not included.
Answer
As you have implemented the CLR sequence based on my article related to the calculation of Running Totals, you can achieve the same using the ROW_NUBER() function.
The ROW_NUMBER() function requires the ORDER BY in the OVER clause, however there is a nice workaround how to avoid sorting due to the ORDER BY. You cannot put an expression in the order by, but you can put SELECT aConstant there. So you can easily achieve number generating using below statement.
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber,
*
FROM aTable