Men In Black are not the only ones that can remove an identity
Identity columns are great. they help us to understand our data better, and can even help our queries to perform better.
But what if you made a mistake when creating the table. What if someone else made a mistake? Whatever the reason, you ultimately want that aspect of the column to go away.
There are a couple of ways to do this:
Using the GUI for identity removal:
First you have to login to SSMS, open your database and find the table with the column you want to remove the identity attribute from.
Right click on the table and select Design. Scroll until you find Identity Specification, expand and change the (is Identity) value to No.
You can also right click on the column name itself and select modify. Then you just find the Identity Specification as above and change the value.[divider]
Using T-SQL code for identity removal
/* Script to remove the ‘identity’ aspect of a column in a table --------------------
5/6/15 - WaterOx Consulting, Inc
First we have to allow updates directly to the system tables to change the identity column information */
sp_configure 'allow update', 1
go
RECONFIGURE WITH OVERRIDE
go
UPDATE syscolumns
SET colstat = colstat - 1 /*turn off bit 1 which indicates it's an identity column */
<img class="wp-more-tag mce-wp-more" title="Read more..." src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" alt="" data-wp-more="more" data-wp-more-text="" data-mce-resize="false" data-mce-placeholder="1" data-mce-src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7">
WHERE id = OBJECT_ID('dbo.AccountsIDed') /* Enter the tablename */
AND name = 'accountid'
/* Enter the column name set as identity */
go
/* lock your system tables back down */
EXEC sp_configure 'allow update', 0
go
RECONFIGURE WITH OVERRIDE
-------------------------------------------------------------------------------------------------------------
What about Primary Keys?
Does the above work for a Primary Key set as an identity value?
You bet!
There may be issues that arise due to any foreign key restraints, but the “key” to this is that the identity attribute basically tells SQL Server that the value is to auto-increment and by what increment. The uniqueness is only a factor if you place a restraint on the column to be unique. This can be done by making it a primary key, or adding a unique constraint like this:
USE AdventureWorks2012; GO ALTER TABLE dbo.accountsIDed ADD CONSTRAINT UID_accountid UNIQUE (accountid); GO
In general, if you are just looking to get rid of the identity attribute, it is possible.
[divider_top][icon_box icon=”users” title=”Who is Chris Bell?”]
Chris Bell, SQL Server MVP, MCSE & MCITP, is a 20 year veteran of using Microsoft products & SQL Server to create solutions for businesses, organizations and individuals. Chris speaks, blogs, writes articles and makes media of all kinds regarding SQL Server at WaterOxConsulting.com. Chris is also the founder of WaterOx Consulting, Inc. which features SQL Server consulting services along with the destination location week-long training series: SQL Summer Camp.
Click here to contact Chris or to follow him on twitter. [/icon_box]