Home Dashboard Directory Help
Search

Allow enabling and disabling of a column's IDENTITY property by Adam Machanic


Status: 

Active


74
2
Sign in
to vote
Type: Suggestion
ID: 252226
Opened: 1/16/2007 10:51:11 AM
Access Restriction: Public
Duplicates: 294193
5
Workaround(s)
view

Description

Sometimes IDENTITY columns need to be cleaned up a bit -- their values updated or re-ordered, for instance. This can occur when data replication strategies fail, or when unexpected data was inserted. This is a somewhat rare occurrence, but when it does happen it can be extremely painful due to the fact that IDENTITY columns cannot be updated, and the property cannot be enabled or disabled. As a result, in most cases doing what should be a simple update requires creation of a new table.

Likewise, we sometimes want to be able to turn an existing column into an IDENTITY column. The same problem exists there -- it simply can't be done.
Details
Sign in to post a comment.
Posted by Anton Plotnikov on 6/18/2013 at 3:17 AM
Sequences were introduced in SQL 2012. But we cannot use it because it is an impossible to replace identity to sequence without recreating table. Our database contains a lot of foreign keys, indexes etc. In other words "simple dropping identity property (not column data!)" leads to total DB reconstruction. Proposed suggestion can simplify such migration dramatically.
Posted by prk on 5/2/2013 at 8:10 PM
I just realised how painful having identity columns !! we recently wanted to merge two client databases (their businesses merging) and it became a nightmare!! Because we used identities as PK and we could not update PKs because of this sql restriction. we wanted to assign a range IDs to one client's database and bring them to second database. only if we could update identity column it could have been much simpler!
Posted by Paul White NZ on 2/4/2010 at 5:16 PM
I have added a partial workaround using ALTER TABLE...SWITCH.
I do agree that this would be a useful extension to ALTER TABLE ALTER COLUMN though...
Posted by Daniel Smith on 10/16/2009 at 4:00 AM
@way0utwest - the workaround definitely does work in 2005 - I used it just the other day!
Posted by way0utwest on 2/21/2008 at 8:08 AM
Workaround doesn't work in 2000 or 2005.

I would rate updates more important than turn on/off, but both would be very handy.
Posted by David Avsajanishvili on 7/5/2007 at 12:27 AM
IDENTITY columns CAN be updated! See workaround...
Posted by Microsoft on 1/18/2007 at 10:51 AM
Thank you for the feedback, we'll look into ways to improve on the scenario you describe.

Thanks,

- Christian Kleinerman
Sign in to post a workaround.
Posted by Paul White NZ on 2/4/2010 at 5:14 PM
We can use ALTER TABLE...SWITCH to work around this by only modifying metadata. See Books Online for restrictions on using the SWITCH method presented below. The process is practically instant even for the largest tables.

USE tempdb;
GO
-- A table with an identity column
CREATE TABLE dbo.Source (row_id INTEGER IDENTITY PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);
GO
-- Some sample data
INSERT dbo.Source (data)
VALUES (CONVERT(SQL_VARIANT, 4)),
        (CONVERT(SQL_VARIANT, 'X')),
        (CONVERT(SQL_VARIANT, {d '2009-11-07'})),
        (CONVERT(SQL_VARIANT, N'áéíóú'));
GO
-- Remove the identity property
BEGIN TRY;
    -- All or nothing
    BEGIN TRANSACTION;
    
    -- A table with the same structure as the one with the identity column,
    -- but without the identity property
    CREATE TABLE dbo.Destination (row_id INTEGER PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);
    
    -- Metadata switch
    ALTER TABLE dbo.Source SWITCH TO dbo.Destination;
    
    -- Drop the old object, which now contains no data
    DROP TABLE dbo.Source;
    
    -- Rename the new object to make it look like the old one
    EXECUTE sp_rename N'dbo.Destination', N'Source', 'OBJECT';
    
    -- Success
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Bugger!
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;
GO

-- Test the the identity property has indeed gone
INSERT dbo.Source (row_id, data)
VALUES (5, CONVERT(SQL_VARIANT, N'This works!'))

SELECT row_id,
        data
FROM    dbo.Source;
GO

-- Tidy up
DROP TABLE dbo.Source;

----
Paul
Posted by Marcelo Colla on 2/22/2008 at 4:22 AM
other ideia !

Create database Test

Use test

Create table exemplo ( idx int identity(1,1), idx2 int )

insert into exemplo (idx2) Values (1)
insert into exemplo (idx2) Values (2)
insert into exemplo (idx2) Values (3)


Go
sp_configure 'allow updates', 1
Go
Reconfigure With Override
Go

Update Syscolumns Set typestat = 0 where name = 'idx'
Update Syscolumns Set colstat = 0 where name = 'idx'



sp_configure 'allow updates', 0
Go
Reconfigure With Override
Go
Posted by Marcelo Colla on 2/21/2008 at 10:58 AM
To change a column with identity to not identity,

-- Option 1

If Exists (Select Table_name from information_schema.Tables where table_name = 'Exemplo')
            drop table Exemplo        

Create Table Exemplo ( Idx Int Identity(1,1))

Insert Exemplo Default Values
Insert Exemplo Default Values
Insert Exemplo Default Values

Alter Table Exemplo Add Idx1 Int
Update Exemplo Set Idx1 = Idx

Alter Table Exemplo Drop Column Idx

Sp_rename 'Exemplo.idx1', 'idx', 'Column'

Select * From Exemplo

-- Option 2

If Exists (Select Table_name from information_schema.Tables where table_name = 'Exemplo')
            drop table Exemplo        

Create Table Exemplo ( Idx Int Identity(1,1))

Insert Exemplo Default Values
Insert Exemplo Default Values
Insert Exemplo Default Values

Select Convert(int, Idx) Idx
Into [Temp] From Exemplo

Drop Table Exemplo

EXEC sp_rename '[Temp]', 'Exemplo'

-- this example does not keep table objects as triggers, etc
Posted by Adam Machanic on 12/13/2007 at 2:46 PM
David:

Wishful thinking? Alas, that doesn't work in any current version of SQL Server.

Posted by David Avsajanishvili on 7/5/2007 at 12:30 AM
SET IDENTITY_INSERT ON

UPDATE TABLE MyTable
SET ID = @NewID
WHERE ID = @OldID

SET IDENTITY_INSERT OFF

-- You can also use DBCC CHECKIDENT to reseed identity value
-- (See MSDN for details)