Wednesday, October 1, 2008

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

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?

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.

Approach 1 - Identity Column
In this example we are going to create a simple table, load 100,000 records and then alter the table and add the identity column.

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

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

SELECT TOP 10 * FROM accounts
GO

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

SELECT TOP 10 * FROM accounts
GO

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.

Approach 2 - Simple Update
In this example we create a similar table, 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

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

In this step we are doing an UPDATE to the table and for each row that is updated we are passing the existing value and adding one to the value. In addition, we are also updating the parameter value as well. This can be seen where we are making the @id value equal to the id and the id equal to @id + 1.

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

SELECT * FROM accounts2
GO

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.

Another Example
To take this a step further, let's say we want to increment the values by 10 we can do the following just as easy.

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

SELECT * FROM accounts2
GO

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

1 comment:

Anonymous said...

Great, Thanks!