DBA 101: Altering a column

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.

image

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.

3 thoughts on “DBA 101: Altering a column

  1. Excellent points overall, with great advice to people to use commands for ALTERing columns rather than using the gui.

    But, please note one critical thing when ALTERing columns: always specify NULLability, otherwise SQL could change it without you realizing it.

    Let’s see this in action, using tempdb as a common database everyone would have (if your instance’s tempdb doesn’t have NULLable by default, naturally you’ll need to use a different db).

    USE tempdb;
    CREATE TABLE #test_table ( c1 int NOT NULL);
    EXEC sp_help #test_table; /*verify that column “c1” is indeed NOT NULLable*/
    ALTER TABLE #test_table ALTER COLUMN c1 bigint;
    EXEC sp_help #test_table; /*note that column “c1” is now NULLable!*/

    Like

  2. I agree with you Daniel, using the T-SQL command is always preferable to using its GUI counterpart in the SQL Server.
    In your instance it is ALTER TABLE command. In my experience, using ALTER TABLE to add a default value or a new constraint performs faster than using the GUI especially where the table has a few million rows already. GUI always has some overhead involved.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s