People always ask me what tools I use. Mostly this question comes from the topic of monitoring. I can honestly say that I don’t love any tools and you shouldn’t either.

My time at Microsoft placed me at nearly 300 different clients. As a Microsoft employee I never liked saying, “Go grab this tool so we can look at your problem because SSMS isn’t good enough.”

Well in the scope of Altering objects, SSMS is often not good enough if at all.

Today’s post we’re going to look at how SSMS alters a column and how you can avoid it’s shenanigans.

Demo Config

Using AdventureWorks, make a copy of the Person.Address table with the following query:

SELECT * INTO demoAddress FROM PERSON.ADDRESS

Demo

Next we’ll use SSMS to generate a script and change the table. Let’s alter the City column changing it from a length of 30 to 60.

Here any normal person would think that the operation would do a simple alter since all the data from 30 can fit into 60; but, that’s not what happens.

Here’s what SSMS generates:

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

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

GO
CREATE TABLE dbo.Tmp_demoAddress

(

AddressID int NOT NULL IDENTITY (1, 1),

AddressLine1 nvarchar(60) NOT NULL,

AddressLine2 nvarchar(60) NULL,

City nvarchar(60) NOT NULL,

StateProvinceID int NOT NULL,

PostalCode nvarchar(15) NOT NULL,

SpatialLocation geography NULL,

rowguid uniqueidentifier NOT NULL,

ModifiedDate datetime NOT NULL

)  ON [PRIMARY]

TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE dbo.Tmp_demoAddress SET (LOCK_ESCALATION = TABLE)

GO
SET IDENTITY_INSERT dbo.Tmp_demoAddress ON

GO

IF EXISTS(SELECT * FROM dbo.demoAddress)

EXEC(‘INSERT INTO dbo.Tmp_demoAddress (AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, SpatialLocation, rowguid, ModifiedDate)
SELECT AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, SpatialLocation, rowguid, ModifiedDate FROM dbo.demoAddress WITH (HOLDLOCK TABLOCKX)‘)

GO
SET IDENTITY_INSERT dbo.Tmp_demoAddress OFF

GO
DROP TABLE dbo.demoAddress

GO
EXECUTE sp_rename N’dbo.Tmp_demoAddress’, N’demoAddress’, ‘OBJECT’

GO

COMMIT

That’s right. We create a new table, select all the data from the existing one into the new, drop the existing, and finally rename the new to match the previous. Ouch!

How you can accomplish this much more easily:

ALTER TABLE demoAddress ALTER COLUMN City nvarchar(60)

And…. we’re done. One line, one statement. Simple and easy. No need to drop objects or mess with constraints. You’re not recreating indexes or causing a huge hassle.

For the most part, I’m not a fan of tools. I’d much rather learn the DDL, DMVs, and other mechanics of the database. This way I don’t get frustrated when the tool changes or is no longer available.

I hope that helps!

Be sure to follow me on Twitter for fun tech giveaways! I try to do these every quarter; so, stay tuned.