Intermediate SQL – Post 8 – ALTER TABLE

Well, finally I can get ¬†back to writing some SQL goodies. I have to say I’m pretty excited.

We left off creating some tables – and those tables are all good. Let’s say now that we need to change something in a table – such as how many characters or the data type of a column that we created (we’ll get to datatypes later).

Let’s do this quick so we can see what we are looking at:

USE AdventureWorks2012
GO
 
SELECT DISTINCT City FROM Person.Address
WHERE len(City) = (SELECT max(len(City)) FROM Person.Address)
 
SELECT max(len(ltrim(rtrim(City)))) FROM Person.Address

That first query will give us the name of the longest city in that column, and the second will tell us how many characters it is. For times sake, let’s say it’s a 21 character long string. That’s all fine and dandy, considering City is a varchar (60) field.

But what we need to change a city to something like Taumatawhakatangihangakoauauotamateapokaiwhenuakitanatahu (which, incidentally, is a real place)

If we run this query:

UPDATE Person.Address
	SET City = 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu', ModifiedDate = getdate()
WHERE AddressID = '26158'

We will get an error back that looks like this:

Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.

Oh noes, what do we do? That error usually means your column won’t fit the data that you’re trying to put in to it. In this case we are attempting to stick a 85 letter (character) name in to a field that is only 60 characters long. No bueno.

What we have to do to make this work is alter our table. A simple query can do this. The syntax of ALTER TABLE is right here:

ALTER TABLE tablename
ALTER COLUMN columnname datatype

For example, to make this city fit, we could do this:

ALTER TABLE Person.Address
ALTER Column City varchar (90)

You may get an error when you do this though – something like this:

Msg 5074, Level 16, State 1, Line 1
The index 'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode' is dependent on column 'City'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN City failed because one or more objects access this column.

That means that this table has an Index on it and we can’t do anything to the column that is indexed unless we remove and recreate the index. We’ll get in to indexes later. For now you can see here:

 

What we want to do is “Drop and Create to”. You can either have it on your clip board and paste it in your current query window or have it open a new query window. Usually I do the second one.

The first thing we want to do is run the “Drop Index Portion”.

USE [AdventureWorks2012]
GO
 
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'INDEX',@level2name=N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
 
GO
 
/****** Object:  Index [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]    Script Date: 11/29/2012 12:11:49 PM ******/
DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]
GO

No we can run our “Alter Table” statement:

ALTER TABLE Person.Address
ALTER Column City varchar (90)

And then we want to re-create the index:

/****** Object:  Index [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]    Script Date: 11/29/2012 12:11:49 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]
(
	[AddressLine1] ASC,
	[AddressLine2] ASC,
	[City] ASC,
	[StateProvinceID] ASC,
	[PostalCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nonclustered index.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'INDEX',@level2name=N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
GO

Once all that is done, we can successfully change the city name for our huge city name:

UPDATE Person.Address
	SET City = 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu', ModifiedDate = getdate()
WHERE AddressID = '26158'

And there we go.