Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

Populate a SQL Server column with a sequential number not using an identity


By:   |   Read Comments (32)   |   Related Tips: 1 | 2 | More > Identities

Problem

I have a database table that has a lot of data already in the table and I need to add a new column to this table to include a new sequential number. In addition to adding the column, I also need to populate the existing records with an incremental counter what options are there to do this?

Solution

The first approach that may come to mind is to add an identity column to your table if the table does not already have an identity column. We will take a look at this approach as well as looking at how to do this with a simple UPDATE statement.

Using an Identity Column to Increment the Value by 1

In this example we are going to create a table (to mimic a table that already exists), load 100,000 records and then alter the table to add the identity column with an increment of 1.

CREATE TABLE accounts ( fname VARCHAR(20), lname VARCHAR(20)) 
GO 

INSERT accounts VALUES ('Fred', 'Flintstone') 
GO 100000 

SELECT TOP 10 * FROM accounts 
GO
sql server query results
ALTER TABLE accounts ADD id INT IDENTITY(1,1) 
GO 

SELECT TOP 10 * FROM accounts 
GO
sql server query results

The statistics time and statistics i/o show this did about 23K logical reads and took about 48 seconds to complete.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 17 ms.
Table 'accounts'. Scan count 1, logical reads 23751, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 6281 ms, elapsed time = 48701 ms.

SQL Server Execution Times:
CPU time = 6281 ms, elapsed time = 48474 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

Using Variables To Update and Increment the Value by 1

In this example we create a similar table (to mimic a table that already exists), load it with 100,000 records, alter the table to add an INT column and then do the update.

CREATE TABLE accounts2 ( fname VARCHAR(20), lname VARCHAR(20)) 
GO 

INSERT accounts2 VALUES ('Barney', 'Rubble') 
GO 100000 

SELECT TOP 10 * FROM accounts2 
GO
sql server query results

After the table has been created and the data loaded we add a new INT column to the table that is not an identity column.

ALTER TABLE accounts2 ADD id INT 
GO 

SELECT TOP 10 * FROM accounts2 
GO 
sql server query results

In this step we are doing an UPDATE to the table and for each row that is updated we are updating the variable by 1 as well as the id column in the table. This can be seen here (SET @id = id = @id + 1) where we are making the @id value and the id column equal to the current @id value + 1.

DECLARE @id INT 
SET @id = 0 
UPDATE accounts2 
SET @id = id = @id + 1 
GO 

SELECT * FROM accounts2 
GO 

Below we can see the results where each record gets an incrementing value by 1.

sql server query results

The statistics time and statistics i/o show this did about 26K logical reads and took about 4.8 seconds to complete.

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 247 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'accounts2'. Scan count 1, logical reads 26384, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 4781 ms, elapsed time = 4856 ms.

(100000 row(s) affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

If we compare the statistics time and statistics i/o from the the update with the identity column to this approach the overall number of logical reads is just about the same, but the overall duration is about 10 times faster doing the update versus having to maintain the identity value.

Using Variables To Update and Increment the Value by 10

Let's say we want to increment by 10 instead of by 1. We can do the update as we did above, but use a value of 10 to have the ids in increments of 10 for each record.

For clarity purposes, I am going to first make the id column NULL for all records and then do the update.

UPDATE accounts2 SET id = NULL
GO 

DECLARE @id INT 
SET @id = 0 
UPDATE accounts2 
SET @id = id = @id + 10
GO 

SELECT * FROM accounts2 
GO

Below we can see the id values now increment by 10 instead of 1. You can use any value you want to do the increment (1, 2, 5, 10, etc.).

sql server query results

Warning: Possible Duplicate Values

Two of our readers Tillman Dickson and Steve Ash have noted that they have run into an issue where duplicate values are created if this processs is run in parallel. Tillman noted this issue on a table that had over 11 million rows and Steve mentioned this issue on a very large table as well. I tried to duplicate the issue, but on the systems I tested with I was not able to recreate the issue. This doesn't mean that on your systems you won't possibly face the same issue, so to avoid having duplicate values Tillman and Steve have suggested these approaches.

-- use MAXDOP of 1 - Steve Ash
-- this will run the update using only one processor avoiding the issue of duplicates

DECLARE @id INT 
SET @id = 0 
UPDATE accounts2 
SET @id = id = @id + 1 
OPTION ( MAXDOP 1 )
GO 
-- use SERIALIZABLE isolation level - Tillman Dickson
-- this means that no other transactions can modify data that has been read 
-- by the current transaction until the current transaction completes.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION
  DECLARE @id INT 
  SET @id = 0
  UPDATE accounts2  
  SET @id = id = @id + 1
COMMIT TRANSACTION 

Another approach to update the sequence values

Here is another approach by another reader Ervin Steckl.

-- update rows using a CTE - Ervin Steckl 

;WITH a AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn, id
FROM accounts2
) 
UPDATE a SET id=rn
OPTION (MAXDOP 1)

Summary

Once you have created an identity column there is no easy way to renumber all of your values for each row. With the update approach you could do this over and over again by just rerunning the query and changing the values.

Next Steps
  • If you have the need to add a new sequential value to your tables or have the need to update an existing value in a sequential manner don't forget about this approach
  • Look for other T-SQL tips and tricks here


Last Update:

First Published: 2008-04-01


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 30, 2018 - 3:09:07 PM - Jinnybat Back To Top

Nice, I would like to add one more query comparative to CTE.

UPDATE REF

   SET REF.ID=REF.ROWID

   FROM(

             SELECT ID, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))               AS ROWID

             )REF

Regards


Tuesday, March 13, 2018 - 4:42:44 AM - Mike Grimes Back To Top

 

 Hi Greg,

 

Thanks for the info, I had never seen that syntax before for setting an incremental id    set @id = id = @id+1

Glad that it worked.

Regards, Mike.


Thursday, March 08, 2018 - 4:06:38 AM - herman Back To Top

hi,

External systems are injecting data in our database and the import table required an unique reference id.

so we are using new data and above solutions are not serving our need.

our database is oracle and we use the sequence functionality 

just calling the function increments the sequential number and this allowed up to respect the unique reference and avoid any duplicates or restarting of numbers.

this is not identical to the function of identify as the starting number is irrelevant.

ALTER TABLE accounts ADD id INT IDENTITY(1,1) 
GO 

I suppose that a simular functionality exists and this would be an other soluction.

Also

Is there no functionality that when you create a table, you can automatically allocate an unique sequential number as default value? 

 

Herman 

 

 

 


Saturday, February 27, 2016 - 4:41:21 PM - Oluyemi Back To Top

 

 

 

Thanks, Greg.

The article on Rank Function you referred me to is most helpful.

Remain blessed. 


Friday, February 26, 2016 - 9:37:11 AM - Greg Robidoux Back To Top

Hi Oluyemi,

check out this tip and look at the RANK() function.  This should be able to help you do what you are looking at doing.

https://www.mssqltips.com/sqlservertip/1944/sql-server-2005-and-2008-ranking-functions-rownumber-and-rank/

Thanks
-Greg


Friday, February 26, 2016 - 9:34:38 AM - Greg Robidoux Back To Top

Thanks Ervin for another approach to doing this.

I will update the tip and add your approach as well.

-Greg


Friday, February 26, 2016 - 4:30:14 AM - Oluyemi Back To Top

 

 

 

Thanks for the tutorial.

I tried to implement the input of Tillman Dickson in a scenario in which the serial no should resent on change of group.  I was not successful; rather it numbered the entire tblStudents serially. 

Please help me to indicate what I did wrong to enable me do it right.

My Code is shown below. The students are to be grouped and numbered according to their group.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
DECLARE @id Smallint
set @id = 0
WHILE @@FETCH_STATUS =0
BEGIN
    update tblStudents
    set @id = stno = @id+1
    where bytLGACode = bytLGACode and bytschtype = bytschtype and intSchCode = intSchCode
END
COMMIT TRANSACTION


Thursday, February 25, 2016 - 5:24:10 PM - Ervin Steckl Back To Top

Thanks for the tips. The fact about possible parallelism issue is a good to know one.

Yet another method using a windowing function, which uses less reads (scanning the table only once):

 

;WITH a AS(

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rn, id

FROM accounts2

UPDATE a SET id=rn

OPTION (MAXDOP 1)

 

Statistics:

Table 'accounts2'. Scan count 1, logical reads 13404, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(100000 row(s) affected)

 

Note the "ORDER BY (SELECT NULL)" clause which does the trick actually. Should you use "ORDER BY fname", for example, you would get around 126k reads. 

 


Monday, February 22, 2016 - 9:19:41 AM - Greg Robidoux Back To Top

Hi Steve and Tillman,

Thank you for providing input on this tip.  I will make updates to the tip based on the issues you both encountered.

Thanks again,
Greg


Saturday, February 20, 2016 - 3:46:15 PM - Steve Ash Back To Top

 Just to echo Tillman's comment about parallelism -- i ran into this as well and unfortunately didn't catch it and wasted a day of work :/  You might want to update the post above since this shows up high in the list from Google.  The simplest fix is just to use MAXDOP 1 which just disables parallism:

declare @id bigint = 1
update SIM_OASIS_SUSPIC_HIST_RUN9 
set @id = id = @id + 1
option (MAXDOP 1)

 


Monday, February 15, 2016 - 10:23:04 AM - Greg Robidoux Back To Top

Hi Tillman, thanks for taking the time to peform all of these tests.  I didn't do a test on such a large table, so your results are very interesting.

Thanks again for showing a solution that worked for a very large table.

-Greg


Sunday, February 14, 2016 - 6:16:47 PM - Tillman Dickson Back To Top

 This is a sweet trick, but one with a nasty gotcha, SQL Server is multithreaded and when it can it threads queries aggressively. @ID isn't interlocked

 

My use, gotcha and fix will quickly illustrate.

I received an unindexed table with 11 000 000 rows of which several columns were NVARCHAR(MAX) of mixed English and Arabic.

1. I performed a SELECT INTO with a SELECT 0 as ID, field field.

2. I used your trick to generate unique values in the ID column (40 seconds for 11 million rows!!!!!!).

3. I inserted these rows into an empty table of the same schema, but with ID actually having a clustered index.

4. Insert failed due to key violations. For every unique ID value there were 8 rows which shared. Welcome to reentrancy. 

5. Reset ID column value to 0, wrapped your trick in a transaction, applied it. No improvement. 

6. Reset ID columns to 0.

6. Made the transaction serializable et voila!. I now had unique values in the ID column and it run time was 2 seconds longer, nominal.

Here's my example (the first line is key)

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

  DECLARE @val INT 

  SET @val = 1

  UPDATE [ActivationsToExportStage] 

SET id = @val + 1, @VAL= @val + 1

COMMIT TRANSACTION 

 

 

 


Tuesday, February 17, 2015 - 8:05:35 AM - Greg Robidoux Back To Top

Hi Prakash,

this can be done with a CASE statement:

update [dbo].[student]
set name = case when stid = 2 then 'ragu' when stid = 4 then 'manju' end
where stid in (2,4)


Tuesday, February 17, 2015 - 12:53:50 AM - prakash Back To Top

hi

how to update two id number at a time.but same table name

example:update student set stid=2,stid=4 where name='ragu' or 'manju'

how to excute this one 


Friday, July 11, 2014 - 5:52:57 PM - Gene Wirchenko Back To Top

Yes, it is now correct.

 

 


Friday, July 11, 2014 - 3:31:20 PM - Greg Robidoux Back To Top

Hi Gene,

I updated the tip to make this more clear.  Let me know if this makes more sense.

Thanks
Greg


Friday, July 11, 2014 - 1:39:54 PM - Gene Wirchenko Back To Top

In the last example, the scenario stated is to INCREMENT the iids by 10, that is what the code does, but the cursor shown is the result of MULTIPLYING the ids by 10.


Thursday, January 16, 2014 - 11:48:26 PM - S Rajesh Back To Top

I need output for the below sample. the trip number has to be generated for each carrier, truck, trip eligibility combination. but if the trip eligibility has 'N' then the previous row trip number should be considered. i.e. the elibility flag 'N' indicates that that trip is single trip of previous record. Anyone can help for this??? Thanks.

 

Carrier

Truckno

Trip Elg

TripNo

SlNo

A

aa

Y

1

1

A

aa

N

1

2

A

aa

N

1

3

A

aa

Y

2

4

A

aa

Y

3

5

A

aa

N

3

6

A

aa

Y

4

7

A

bb

Y

1

8

A

bb

N

1

9

A

bb

Y

2

10

A

bb

Y

3

11

A

bb

Y

4

12

B

ac

Y

1

13

B

ac

N

1

14

B

ac

N

1

15

B

ac

Y

2

16

B

ac

N

2

17

B

ad

Y

1

18

 

 

 


Tuesday, January 14, 2014 - 5:30:54 AM - Pranit Back To Top

Hey Greg,

Really very nice post, thanks man.


Monday, January 13, 2014 - 5:54:37 AM - Satish Back To Top

Greg !

Really very nice one.

Thank You So much :)


Satish


Friday, July 26, 2013 - 5:05:37 AM - Michiel van den Boogaard Back To Top

Same result

with emptyAccession

as

(

                select accession, 'CONV' + cast(ROW_NUMBER() over (order by study_id) + 150454 as varchar(max)) as rownumber

                from MyTable           where cast(Remarks as varchar(max))= 'Conversion 26-07-2013'

)

update emptyAccession

set Accession = rownumber


Friday, July 26, 2013 - 5:04:27 AM - Michiel van den Boogaard Back To Top

I had to populate a column with a string, followed by a number. So I used this:

DECLARE @id INT
SET @id = 200000
UPDATE MyTable SET Accession = 'Conv'+cast(@id as varchar(6)), @id = @id + 1
where cast(Remarks as varchar(max))= 'Conversion 26-07-2013'

The syntax SET @id id @id 

10 could not be used

 

 

 


Monday, June 24, 2013 - 11:20:53 AM - Ramon Back To Top

Hi Gred.

I have one question, how you find out if SQL is using Queries Hint that the developers cteated.

Thanks.


Saturday, June 08, 2013 - 9:59:02 PM - Greg Robidoux Back To Top

@Jeff - here is the exampe for what you want to do.  The "order by id" sorts the data by "id" for the numbering.

create table #temp (Id int ,Drwng_Nmbr int, M_D_Y date,   Night_Day char(1))

insert into #temp
select 1,345,'12/18/66','D'
union
select 3,384,'12/18/68','D'
union
select 4,984,'12/18/69','N'

select *, row_number() over (order by id) as NewRow_Count from #temp

 

 


Saturday, June 08, 2013 - 12:26:48 PM - Jeff Back To Top

My database is named

 

dbo.Matrix it has the following tables  Id ,Drwng_Nmbr, M_D_Y , Night_Day

I would like to add a view that will add a new row called "RowCount" say for instance?

Because my id doesnt follow consecutive count no longer, cause of deleted records.

 

when i run the new view i would like to see this

Id ,Drwng_Nmbr, M_D_Y ,   Night_Day ,NewRow_Count

1       345         12/18/66      D              1

3       384         12/18/68      D              2  where the row count stays consecutive for each record

4       984         12/18/69      N              3   where the row count stays consecutive for each record

Do you have an example of the sql statement i would use to accomplish this

I am using sql 2008 express and sql management studio

I am sorry but i am new to dbs lol

jeff

 

 


Saturday, June 08, 2013 - 11:51:54 AM - Greg Robidoux Back To Top

@Jeff - take a look at the ROWNUMBER function

http://www.mssqltips.com/sqlservertip/1944/sql-server-2005-and-2008-ranking-functions-rownumber-and-rank/

 


Saturday, June 08, 2013 - 10:21:57 AM - Jeff Back To Top

Yes I need to create a view where it will add a new column NewId

and will number every row consecutively from 1 to the last index

thanks in advance

jeff


Friday, March 08, 2013 - 5:35:50 AM - Aakaash Back To Top

Thank you so much ! The incremental code is really helpful.


Friday, February 22, 2013 - 4:54:30 AM - Julian Back To Top

Greg, you are a genius! This tip has saved me hours and hours of brain-ache, thankyou so much. I had a table with an ID column (but not an identity column) where only some of the rows had a value. I wanted to make sure every row was assigned an ID, and with the simple addition of a WHERE clause to the end I was able to do this without recourse to the dreaded cursor...


Thursday, December 20, 2012 - 2:33:08 PM - Rob Back To Top

Greg, this is insanely useful!  I needed to update a column in-place with sequential values and was struggling to remember the names of the T-SQL keywords  ("ROW_NUMBER() OVER") so my Google search landed me here instead.  After having a look at your trick and trying it myself, I MUCH prefer this to the ROW_NUMBER approach!  Thanks for sharing


Friday, April 18, 2008 - 2:46:25 PM - grobido Back To Top

From the tests that were run it was quicker to do a mass popluation of a sequential number not using an identity value.

If there is a need to maintain the value for each insert, such as for a transaction based system, this would be different since you are not doing a mass quantitiy of records all at once.  So if this is the case you probably would not see much of a difference with either method.

 

 


Wednesday, April 16, 2008 - 1:53:44 AM - chrisfeas Back To Top

Looking at the two methods used in this tip it appears that it's quicker to add sequential ID numbers after creating a table than to use an identity column, is this correct or have I misunderstood?

The reason I ask is that I have a stored procedure that populates data with a sequential ID as the data is being inserted into a table but if it would be quicker to add the ID numbers after I'm going to alter the procedure to do it that way.


Learn more about SQL Server tools