Elegant way to change field to "Identity" type?

Question

Elegant way to change field to "Identity" type?

Barkingdog

SSCoach

Points: 18670
More actions
May 26, 2010 at 9:57 am

#232880

We have a table with millions of records. The first column looks like 1,2,3,4,5,6....
but it is not an identity field. We want to change it to an identify field.
The only way I know to do this is "in-elegant" (e.g. create a temp table with that field as an Identity, copy the data to the new table, delete the original table, and rename the temp table back to the original name)
Is there a more "elegant" way (e.g. some "magic" DDL I'm not familiar with?) to do this task?
TIA,
Barkingdog

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Is the current column defined as NOT NULL? If so, there is a neat trick that makes it almost instant regardless of the number of rows.

Table definition please (including indexes).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Barkingdog SSCoach Points: 18670 More actions · Answer 2

Paul,

The column is defined as NOT NULL and there are no indexes or constraints defined on the table.

Barkingdog

WayneS SSC Guru Points: 95460 More actions · Answer 3

Paul White NZ (5/26/2010)
Is the current column defined as NOT NULL? If so, there is a neat trick that makes it almost instant regardless of the number of rows.
Table definition please (including indexes).

I'm ready to see this myself... [learn]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

WayneS (5/26/2010)
Paul White NZ (5/26/2010)
Is the current column defined as NOT NULL? If so, there is a neat trick that makes it almost instant regardless of the number of rows.
Table definition please (including indexes).
I'm ready to see this myself... [learn]

Me too waiting for the answer from Paul!

Paul White SSC Guru Points: 150467 More actions · Answer 5

Demonstration script (any edition of 2005+ required)

USE tempdb;

GO

-- Source table without IDENTITY

-- (PRIMARY KEY just for demonstration purposes)

CREATE TABLE dbo.Example

(

row_id BIGINT NOT NULL

CONSTRAINT [PK dbo.Example row_id]

PRIMARY KEY CLUSTERED,

data INTEGER NOT NULL

);

GO

-- Add 987,000 rows (takes about 15-20 seconds)

INSERT dbo.Example WITH (TABLOCK)

SELECT TOP 987000

ROW_NUMBER() OVER (ORDER BY (SELECT 0)),

CHECKSUM(NEWID())

FROM master.sys.all_columns A1,

master.sys.all_columns A2,

master.sys.all_columns A3;

GO

-- New shadow empty table with IDENTITY

CREATE TABLE dbo.ExampleIdentity

(

row_id BIGINT NOT NULL

IDENTITY(1,1)

CONSTRAINT [PK dbo.ExampleIdentity row_id]

PRIMARY KEY CLUSTERED,

data INTEGER NOT NULL

);

GO

-- Promote almost all errors to automatic transaction aborts

SET XACT_ABORT ON;

-- Transaction for safety

BEGIN TRANSACTION;

-- Move the rows (instant)

ALTER TABLE dbo.Example

SWITCH TO dbo.ExampleIdentity;

-- Drop the empty original table (also instant)

DROP TABLE dbo.Example;

-- Rename the new table as the old table

EXECUTE sp_rename @objname = N'dbo.ExampleIdentity', @newname = N'Example', @objtype = 'OBJECT';

-- Rename the PRIMARY KEY

EXECUTE sp_rename @objname = N'dbo.Example.[PK dbo.ExampleIdentity row_id]', @newname = N'PK dbo.Example row_id', @objtype = 'INDEX';

COMMIT TRANSACTION;

-- Show some rows

SELECT TOP (10) *

FROM dbo.Example;

-- Show IDENTITY properties (notice last_value is NULL)

SELECT OBJECT_NAME([object_id]),

TYPE_NAME(system_type_id),

seed_value,

increment_value,

last_value

FROM sys.identity_columns

WHERE [object_id] = OBJECT_ID(N'dbo.Example', N'U');

-- Fix up the IDENTITY metadata

DBCC CHECKIDENT(N'dbo.Example', 'RESEED');

-- Show IDENTITY properties (last_value is now 987,000)

SELECT OBJECT_NAME([object_id]),

TYPE_NAME(system_type_id),

seed_value,

increment_value,

last_value

FROM sys.identity_columns

WHERE [object_id] = OBJECT_ID(N'dbo.Example', N'U');

-- Tidy up

DROP TABLE dbo.Example;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 6

Liking the SWITCH option... Used it many times.

Nice code!

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 7

sql_lock (5/27/2010)
Liking the SWITCH option... Used it many times.
Nice code!

Curious... Other than the obvious purpose for partitioned tables documented in BOL or the one that Paul laid out above, have you used it for anything else?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_lock SSCrazy Eights Points: 9373 More actions · Answer 8

Jeff Moden (5/27/2010)
sql_lock (5/27/2010)
Liking the SWITCH option... Used it many times.
Nice code!
Curious... Other than the obvious purpose for partitioned tables documented in BOL or the one that Paul laid out above, have you used it for anything else?

Jeff

It is in DW for staged data loading and archiving base on our financial year. Not the ideal, but works we for us.

Cheers

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)