Just a day ago, I received interesting question on this blog. Read original question here. This is very good question and after reading this question I quickly wrote small script as answer. Let us see the question and answer together.
Q. How can we generate incremented linear number in sql server as in oracle we generate in via sequence?
1
2
3
4
5
6
7
8
9
A. Run following script which will generate output in sequence, which will generate output as you requested.
DECLARE @idt INT
SET @idt = 0
WHILE (@idt < 100)
BEGIN
SELECT @idt = @idt + 1
PRINT @idt
END
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
I doubt this is the answer your question asker was looking for.
Most likely the asker wanted to know how to do this in a table. in a table, you would use an identity column, not a while loop.
If this question is about creating a “sequence” in MS SQL – I’ve wanted this functionality as well and came up with the following solution.
Here’s what I did…
Create a table with one column, I chose “id” as an “int” type.
Create a stored procedure, mine was “reserve_ordernumber” and was short and sweet.
DECLARE @id int
UPDATE order_sequence SET id = id + 1, @id = id + 1
SELECT @id AS ‘id’
Voila! – You can then call the procedure and have it return a unique integer from a linear number sequence.
If stored procedures aren’t your thing, you could really do this a number of ways by mutating things here and there. (e.g. trigger (instead-of), scalar value function, etc…)
Hope that helps.
Hi
Is it possible to store in a single variable like as below
1,2,3,4 and so on. Output can be shown in this format.
I have tested out: UPDATE order_sequence SET id = id + 1, @id = id + 1 and it is working in generating a sequence.
I also have this problem to generate serial number for rows returned by an sql query. I have done this and it solved my problem.
SELECT (ROW_NUMBER() OVER ( ORDER BY fieldname)) AS ID from tablename
Hi Pinal,
Can you share your input and thoughts on this Database Document generator. Here is the sample document
http://www.sqldocumentor.com/sqlserverhtml/sql_server_index.html
Thanks
Nilesh