Generate a Sequence number for each row in a table.


Howdy EveryOne

Generating a sequence a number for each and every row in a table…. Hmm if you are familiar with TSQL already I think you are thinking of creating Identity column like me…. (ofcourse if you are not an advanced user)…..

Now you can create the Identity column in a temporary table followed by the same schema of your own table to generate a sequence number for all the rows in your table, But in order to accomplish this task we are using a temporary table in between. If the data too small we might not see any performance issue but if the data is too large (too many record sets) then you are in serious trouble of performance.

How can we accomplish this task without the temp table in between… Is it possible, Yes it is ?

And again Thanks to Kent W. on MSDN forums who shown the best approach to do this….

DECLARE @a table
(
	a int
)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
insert into @a values (1)
select row_number() over(order by (select 1)) , a from @a
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s