Changing an existing column to identity

  • My current project for performance improvement of a database includes changing from a sequence generated by inserting into a table with an identity column, retrieving the identity value created, deleting the row from the table and then using the generated value in the insert. This takes place in an SP like this:

    CREATE PROCEDURE dbo.get_order_id (@order_id INT OUTPUT)

    AS

    BEGIN;

    INSERT INTO dbo.generate_order_id

    (dummy)

    VALUES

    (0) ;

    SELECT

    @order_id = SCOPE_IDENTITY() ;

    DELETE FROM

    dbo.generate_order_id

    WHERE

    id = @order_id ;

    RETURN ;

    END ;

    This is used like this:

    CREATE PROCEDURE dbo.insert_order (@customer_id INT, @order_date SMALLDATETIME)

    AS

    BEGIN;

    DECLARE @order_id INT;

    EXEC dbo.generate_order_id @order_id OUTPUT;

    INSERT INTO dbo.orders

    (

    order_id,

    customer_id,

    order_date

    )

    VALUES

    (

    @order_id,

    @customer_id,

    @order_date

    )

    RETURN ;

    END ;

    As you can imagine this causes deadlocks. The fix is to change the order_id column in the orders table to be an identity column, since that's all the code is doing anyway.

    You can't just change a column to identity, you have to re-create the table. When using SSMS to generate the script for this you get something like this:

    /*

    Wednesday, October 05, 201110:31:34 AM

    User:

    Server: .

    Database: Play

    Application:

    */

    /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

    /* Start process with Set options Transaction 1 */

    BEGIN TRANSACTION -- SET options Transaction 1

    SET QUOTED_IDENTIFIER ON

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    COMMIT -- SET options Transaction 1

    /* Drop FK to Customers Transaction 2 */

    BEGIN TRANSACTION --

    GO

    ALTER TABLE dbo.orders

    DROP CONSTRAINT FK_orders_customers

    GO

    ALTER TABLE dbo.customers SET (LOCK_ESCALATION = TABLE)

    GO

    COMMIT -- Drop FK to Customers Transaction 2

    /* create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3 */

    BEGIN TRANSACTION

    GO

    CREATE TABLE dbo.Tmp_orders

    (

    order_id int NOT NULL IDENTITY (1, 1),

    customer_id int NOT NULL,

    order_date smalldatetime NOT NULL

    )

    GO

    ALTER TABLE dbo.Tmp_orders SET (LOCK_ESCALATION = TABLE)

    GO

    SET IDENTITY_INSERT dbo.Tmp_orders ON

    GO

    IF EXISTS(SELECT * FROM dbo.orders)

    EXEC('INSERT INTO dbo.Tmp_orders (order_id, customer_id, order_date)

    SELECT order_id, customer_id, order_date FROM dbo.orders WITH (HOLDLOCK TABLOCKX)')

    GO

    SET IDENTITY_INSERT dbo.Tmp_orders OFF

    GO

    ALTER TABLE dbo.order_items

    DROP CONSTRAINT FK_order_items_order_items

    GO

    DROP TABLE dbo.orders

    GO

    EXECUTE sp_rename N'dbo.Tmp_orders', N'orders', 'OBJECT'

    GO

    ALTER TABLE dbo.orders ADD CONSTRAINT

    PK_orders PRIMARY KEY CLUSTERED

    (

    order_id

    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    GO

    ALTER TABLE dbo.orders ADD CONSTRAINT

    FK_orders_customers FOREIGN KEY

    (

    customer_id

    ) REFERENCES dbo.customers

    (

    customer_id

    ) ON UPDATE NO ACTION

    ON DELETE NO ACTION

    GO

    COMMIT -- create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3

    /* add FK to order_items Transaction 4 */

    BEGIN TRANSACTION --

    GO

    ALTER TABLE dbo.order_items ADD CONSTRAINT

    FK_order_items_order_items FOREIGN KEY

    (

    order_id

    ) REFERENCES dbo.orders

    (

    order_id

    ) ON UPDATE NO ACTION

    ON DELETE NO ACTION

    GO

    ALTER TABLE dbo.order_items SET (LOCK_ESCALATION = TABLE)

    GO

    COMMIT -- add FK to order_items Transaction 4

    AS you can see this sets up 4 separate transactions. I'm wondering if I should modify it to be one transaction so that if there is an error somewhere, the whole thing gets rolled back. This is what I'm thinking:

    BEGIN TRY

    SET XACT_ABORT ON;

    BEGIN TRANSACTION

    SET QUOTED_IDENTIFIER ON

    SET ARITHABORT ON

    SET NUMERIC_ROUNDABORT OFF

    SET CONCAT_NULL_YIELDS_NULL ON

    SET ANSI_NULLS ON

    SET ANSI_PADDING ON

    SET ANSI_WARNINGS ON

    ALTER TABLE dbo.orders

    DROP CONSTRAINT FK_orders_customers

    ALTER TABLE dbo.customers SET (LOCK_ESCALATION = TABLE)

    /* create new table, insert date, drop FK to order items, drop existing table, rename new table, add FK's Transaction 3 */

    CREATE TABLE dbo.Tmp_orders

    (

    order_id int NOT NULL IDENTITY (1, 1),

    customer_id int NOT NULL,

    order_date smalldatetime NOT NULL

    )

    ALTER TABLE dbo.Tmp_orders SET (LOCK_ESCALATION = TABLE)

    SET IDENTITY_INSERT dbo.Tmp_orders ON

    IF EXISTS(SELECT * FROM dbo.orders)

    EXEC('INSERT INTO dbo.Tmp_orders (order_id, customer_id, order_date)

    SELECT order_id, customer_id, order_date FROM dbo.orders WITH (HOLDLOCK TABLOCKX)')

    SET IDENTITY_INSERT dbo.Tmp_orders OFF

    ALTER TABLE dbo.order_items

    DROP CONSTRAINT FK_order_items_order_items

    DROP TABLE dbo.orders

    EXECUTE sp_rename N'dbo.Tmp_orders', N'orders', 'OBJECT'

    ALTER TABLE dbo.orders ADD CONSTRAINT

    PK_orders PRIMARY KEY CLUSTERED

    (

    order_id

    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

    ALTER TABLE dbo.orders ADD CONSTRAINT

    FK_orders_customers FOREIGN KEY

    (

    customer_id

    ) REFERENCES dbo.customers

    (

    customer_id

    ) ON UPDATE NO ACTION

    ON DELETE NO ACTION

    /* add FK to order_items */

    ALTER TABLE dbo.order_items ADD CONSTRAINT

    FK_order_items_order_items FOREIGN KEY

    (

    order_id

    ) REFERENCES dbo.orders

    (

    order_id

    ) ON UPDATE NO ACTION

    ON DELETE NO ACTION

    ALTER TABLE dbo.order_items SET (LOCK_ESCALATION = TABLE)

    IF XACT_STATE() = 0

    BEGIN;

    COMMIT TRANSACTION;

    END;

    ELSE

    BEGIN;

    ROLLBACK TRANSACTION;

    END;

    END TRY

    BEGIN CATCH

    SELECT

    ERROR_NUMBER(),

    ERROR_LINE(),

    ERROR_MESSAGE(),

    ERROR_SEVERITY(),

    ERROR_STATE()

    IF XACT_STATE <> 0

    ROLLBACK TRANSACTION;

    END CATCH

    There will also be SP changes to be made to eliminate the call to the SP that generates the id and to remove the insertion of order_id into the orders table since it is now an identity column.

    What do you think?

  • First thing, a single transaction is better.

    Secondly, I recommend renaming the old table instead of dropping immediately just in case.

    Third, are there indexes, triggers, etc. that also need to be created on the new table?


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Robert Davis (10/5/2011)


    First thing, a single transaction is better.

    That's what I think as well. The only issue is length of transaction, but consistency overrides concurrency in my opinion.

    Secondly, I recommend renaming the old table instead of dropping immediately just in case.

    That's what I think as well. I just posted what is created by SSMS when you script the change made in the GUI.

    Third, are there indexes, triggers, etc. that also need to be created on the new table?

    I didn't add any indexes to the table in my example just to keep it simpler and shorter. If there are indexes, script changes does include the indexes. I haven't tried with a trigger yet. I'll have to check on that. I don't think there are any triggers on any tables I have to make the changes on.

Viewing 3 posts - 1 through 2 (of 2 total)

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