Change scripts generated by SQL Enterprise Manager

I had an occasion today where I needed to create a Primary key constraint on an existing table. The database this table resides in is a copy of a third-party database that is maintained and developed by the third-party. For this table the primary key consists of three fields. For some reason our copy had one of these fields allowing nulls.

As a shortcut, I usually make table changes in EM, grab the change script it generates and run it in Query Analyzer [guess I just don't like typing ;)]. I was a bit surprised to see the script EM created for this change. To change the nullability of the column it took the costly route of creating a new table, copying the data across, dropping the original table, renaming the new one and finally creating the primary key constraint.

Now this was far from ideal as this table holds in excess of 2Mil records and is constantly being read from a 24x7 website. When I ran the script in our testing environment, the website was not functional and I gave up waiting for the script to complete after 5 minutes.

Here is the rather lengthy script generated by EM, only the names have been changed to protect those who shouldn't be innocent.

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
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Tablename]
 DROP CONSTRAINT DF_Tablename_CreatedOn
GO
ALTER TABLE dbo.[Tablename]
 DROP CONSTRAINT DF_Tablename_UpdatedOn
GO
CREATE TABLE dbo.[Tmp_Tablename] (
 field1 varchar(20) NOT NULL,
 field2 varchar(20) NOT NULL,
 field3 varchar(24) NOT NULL,
 field4 decimal(25, 10) NULL,
 field5 decimal(25, 10) NULL,
 field6 decimal(25, 10) NULL,
 field7 decimal(25, 10) NULL,
 field8 decimal(25, 10) NULL,
 field9 decimal(25, 10) NULL,
 field10 decimal(25, 10) NULL,
 field11 decimal(25, 10) NULL,
 field12 decimal(25, 10) NULL,
 field13 decimal(25, 10) NULL,
 field14 decimal(25, 10) NULL,
 field15 bit NULL,
 field16 bit NULL,
 CreatedOn datetime NOT NULL,
 UpdatedOn datetime NOT NULL,
 zChar1 varchar(40) NULL,
 zChar2 varchar(40) NULL,
 zDeci1 decimal(25, 10) NULL,
 zDeci2 decimal(25, 10) NULL
)  ON [PRIMARY]
GO

ALTER TABLE dbo.[Tmp_Tablename] ADD CONSTRAINT
 DF_Tablename_CreatedOn DEFAULT (getdate()) FOR CreatedOn
GO

ALTER TABLE dbo.[Tmp_Tablename] ADD CONSTRAINT
 DF_Tablename_UpdatedOn DEFAULT (getdate()) FOR UpdatedOn
GO

IF EXISTS(SELECT * FROM dbo.[Tablename])
  EXEC('INSERT INTO dbo.[Tmp_Tablename] (field1, field2, field3, field4, field5
, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15
, field16, CreatedOn, UpdatedOn, zChar1, zChar2, zDeci1, zDeci2)
  SELECT field1, field2, field3, field4, field5, field6, field7, field8, field9
, field10, field11, field12, field13, field14, field15, field16, CreatedOn, UpdatedOn
, zChar1, zChar2, zDeci1, zDeci2 FROM dbo.[Tablename] (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.[Tablename]
GO

EXECUTE sp_rename N'dbo.[Tmp_Tablename]', N'Tablename', 'OBJECT'
GO

CREATE CLUSTERED INDEX IX_Tablename_field1 ON dbo.[Tablename] (
 field1
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_Tablename_field3 ON dbo.[Tablename] (
 field3
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE dbo.[Tablename] ADD CONSTRAINT
 [PK_Tablename] PRIMARY KEY NONCLUSTERED (
 field1,
 field2,
 field3
) ON [PRIMARY]

GO
COMMIT 

And here is a much more simplified script to achieve the same result with any blocking resolved before the connection timed out.

ALTER TABLE dbo.[Tablename]
 ALTER COLUMN field2 varchar(20) NOT NULL
GO

ALTER TABLE dbo.[Tablename] ADD CONSTRAINT
 [PK_Tablename] PRIMARY KEY NONCLUSTERED
 (
 field1,
 field2,
 field3
 ) ON [PRIMARY]
GO 

Sort of makes you wonder what the thinking is behind generating the change scripts. I know which one I'd rather run :)