Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing an existing column to identity Expand / Collapse
Author
Message
Posted Wednesday, October 5, 2011 8:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,295, Visits: 13,280
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?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1185933
Posted Wednesday, October 5, 2011 10:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 1,618, Visits: 1,552
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
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1186022
Posted Wednesday, October 5, 2011 10:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:01 PM
Points: 10,295, Visits: 13,280
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.





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1186037
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse