Adding or removing the IDENTITY property

The obvious methods that you use to add or remove the IDENTITY property involve ‘size of data’ operations. For example, if you use the SQL Server Management Studio table designer to change this property you will find that a brand new table is created and data moved from the existing table to a new table. If your table contains a huge number of rows this can be a very expensive operation. You can avoid these ‘size of data’ operations by making use of the ALTER TABLE… SWITCH command. Here’s a script that illustrates its use:


CREATE TABLE dbo.mytable
(
id INT NOT NULL CONSTRAINT pk_mytable PRIMARY KEY,
smallstr VARCHAR(200) NOT NULL,
largestr VARCHAR(MAX) NOT NULL
)
GO

DECLARE @i INT
SET @i = 1

SET NOCOUNT ON

BEGIN TRAN
WHILE @i <= 20000
BEGIN
INSERT INTO dbo.mytable
SELECT @i, 'Small string - no identity', 'Large string - no identity'
SET @i = @i + 1
END
COMMIT TRAN
GO

-- Rename the existing table and its constraints
EXEC sp_rename 'pk_mytable','pk_mytable_old','OBJECT'
EXEC sp_rename 'dbo.mytable', 'mytable_old'
GO

-- Create the new table making the id column an identity column
CREATE TABLE dbo.mytable
(
id INT NOT NULL IDENTITY CONSTRAINT pk_mytable PRIMARY KEY,
smallstr VARCHAR(200) NOT NULL,
largestr VARCHAR(MAX) NOT NULL
)
GO

-- Now switch to the new table - metadata operation that adds the IDENTITY property
ALTER TABLE dbo.mytable_old SWITCH TO dbo.mytable
GO

-- Run CHECKIDENT to ensure newly added rows have the correct values for their id values
DBCC CHECKIDENT('dbo.mytable')
GO

-- Now let's add the new rows
INSERT INTO dbo.mytable(smallstr,largestr)
SELECT 'Small string - with identity', 'Large string - with identity'
GO 3

-- Check the results
SELECT TOP 10 * FROM dbo.mytable ORDER BY id DESC
GO

-- Switch back to a table with no identity
DROP TABLE dbo.mytable_old
GO

EXEC sp_rename 'pk_mytable','pk_mytable_old','OBJECT'
EXEC sp_rename 'dbo.mytable', 'mytable_old'
GO

CREATE TABLE dbo.mytable
(
id INT NOT NULL CONSTRAINT pk_mytable PRIMARY KEY,
smallstr VARCHAR(200) NOT NULL,
largestr VARCHAR(MAX) NOT NULL
)
GO

ALTER TABLE dbo.mytable_old SWITCH TO dbo.mytable
GO

INSERT INTO dbo.mytable(id,smallstr,largestr)
SELECT 20004,'Small string - no identity', 'Large string - no identity'
UNION
SELECT 20005,'Small string - no identity', 'Large string - no identity'
UNION
SELECT 20006,'Small string - no identity', 'Large string - no identity'

-- Check the results
SELECT TOP 10 * FROM dbo.mytable ORDER BY id DESC
GO

The final results are as follows:

addremoveidentity

 

Advertisements
  1. Leave a comment

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

%d bloggers like this: