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.
Using AdventureWorks, make a copy of the Person.Address table with the following query:
SELECT * INTO demoAddress FROM PERSON.ADDRESS
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.*/
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
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]
ALTER TABLE dbo.Tmp_demoAddress SET (LOCK_ESCALATION = TABLE)
SET IDENTITY_INSERT dbo.Tmp_demoAddress ON
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)‘)
SET IDENTITY_INSERT dbo.Tmp_demoAddress OFF
DROP TABLE dbo.demoAddress
EXECUTE sp_rename N’dbo.Tmp_demoAddress’, N’demoAddress’, ‘OBJECT’
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”
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).
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!*/
Excellent point Scott. Thanks for the reply.
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.