SQL Server makes it very easy to change the data type of a column. Maybe you need to make a column larger, or maybe you want to make your table slightly more efficient.
Through the ALTER COLUMN statement, we can very easily change a column’s data type. In this very brief tutorial, we’ll discuss examples of changing a column’s data type and walk you through the very important rules you need to understand when changing a column’s data type.
How to change a column type in SQL Server:
We use the ALTER COLUMN statement within an ALTER TABLE statement to change a column’s data type.
Let’s look at an example of a column we might want to change. Here’s a table called Books:
CREATE TABLE Books ( BookID INT IDENTITY(10,5) NOT NULL, Title VARCHAR(20) NOT NULL, Author VARCHAR(10) NULL, Pages INT NULL, )
We’ll add some data to it:
INSERT INTO Books (Title, Author, Pages)
VALUES
('As a man thinketh','Allen',45),
('Eat that frog','Tracy',108),
('The war of art','Pressfield',165),
('Deep work','Newport',263)
The data in the table should look like this:
Notice in the definition of our table, the ‘Title‘ column is given a data type of VARCHAR(20). Let’s say we want to store the book The Pragmatic Programmer written by David Thomas and Andrew Hunt. If we use the handy LEN system function, we can see that this title is 24 characters long:
So, we shouldn’t be surprised to see that if we try to insert a new row with that title, the statement will fail:
So, how do we change the column’s data type to allow a title longer than 20 characters?
The ALTER COLUMN statement
The ALTER COLUMN statement is used within an ALTER TABLE statement. For example, here is the statement we would run to change the column from 20 characters to, say, 30:
ALTER TABLE Books ALTER COLUMN Title VARCHAR(30)
Notice we basically just redefine the column to give it the new data type we want.
That’s it! Now if we run that statement, then try to do our INSERT again, we see it executes successfully:
Nice!
Important rules you need to know:
There are some very important rules you need to know about changing a column’s data type.
Rule # 1: Since the ALTER COLUMN statement redefines the column completely, you need to make sure you redefine any column settings to preserve them.
I need to point out a problem with my ALTER COLUMN statement I ran earlier. First, notice the nullability of the Title column in our CREATE TABLE statement:
If we remember the ALTER COLUMN statement we ran to change this from VARCHAR(20) to VARCHAR(30), notice we did not specify the nullability of the redefined column:
ALTER TABLE Books ALTER COLUMN Title VARCHAR(30) --No nullability specified. Defaults to allow NULL
By default, if you don’t specify the nullability of a column, SQL Server will set the column to nullable (aka allow NULL values).
Here is what the table looks like in the object explorer after my ALTER COLUMN statement:
In other words, I accidentally changed this column from disallowing NULL to allowing NULL.
Again, the ALTER COLUMN statement redefines the column, so any column settings need to be outlined again. Luckily, I can run another simple ALTER COLUMN statement to make the column non-nullable again:
ALTER TABLE Books ALTER COLUMN Title VARCHAR(30) NOT NULL
Rule # 2: Things get tricky if the column is part of an index
Let’s add the Title column to a simple nonclustered index:
CREATE NONCLUSTERED INDEX idx_Title ON Books(Title)
Now let’s run another quick ALTER COLUMN statement to change the data type from VARCHAR(30) to VARCHAR(35):
So far so good. But now let’s try to change the type again to VARCHAR(34), which is only one less than what it is now:
We get an error message. I noticed that when it comes to altering a column, if that column is part of an index, you can’t change the data type to make the column smaller than what it is presently.
If I want to make the column smaller, I need to drop the index, then make the change, then recreate the index:
If we look in the object explorer, we see the column is VARCHAR(34):
Rule # 3: You can’t decrease a column length if there is already data in the column longer than that length
Here’s the data in our table presently:
We know the Title column is data type VARCHAR(34) now.
We also know that the title “The Pragmatic Programmer” is 24 characters long.
If I wanted to change the data type to VARCHAR(20), SQL Server won’t allow it. If there is already data in the column that exceeds the size of my target data type, SQL Server throws an error message:
Makes sense, right? SQL Server won’t just truncate a value to make it fit!
Rule # 4:Â The target data type needs to be compatible with the data in the column
This is another rule that should go without saying. The target data type needs to be compatible with the data presently in the column. For example, I can’t change the Author column from VARCHAR(10) to INT:
For example, how is SQL Server supposed to change the name “Allen” to a number? Folks, that doesn’t make sense.
Rule # 5: References to your column won’t be changed automatically
Let’s create a very silly stored procedure that simply tells us if we have any books in our inventory for a specified author:
CREATE PROCEDURE IsAuthorInInventory @authorName VARCHAR(10) AS IF EXISTS (SELECT 1 FROM Books WHERE Author = @authorName) SELECT 'Author has books in our bookstore' as Status ELSE SELECT 'Author DOES NOT have books in our bookstore' as Status
Here are two executions of the procedure, showing examples of what is returned if we do have books from an author in our inventory and also if we don’t:
Cool, so let’s increase the Author column to a size of 15, then insert the book ‘The Education of a Bodybuilder‘ into the table:
Ok, now let’s see what happens when we run our stored procedure again:
Excuse me? I don’t think that’s accurate. We can very easily run a quick SELECT statement to see that the author ‘Schwarzenegger‘ exists in our table:
So, what gives?
Even though we changed the column to be VARCHAR(15), the stored procedure was not changed to accommodate. If we do a quick SP_HELPTEXT against this stored procedure, we see the data type of our input parameter is still VARCHAR(10):
Because this input parameter is VARCHAR(10), I think SQL Server is truncating the value we pass to it. The word ‘Schwarzenegger‘ is 14 characters long, but the input parameter only accepts 10. I think SQL Server is truncating the word ‘Schwarzenegger‘ to just ‘Schwarzene‘, then running the internal query using that value. Of course, there are no authors with exactly the name ‘Schwarzene‘, so the procedure returns the message about the author NOT being in our inventory.
SQL Server isn’t smart enough to know all the references to a column, so it is your responsibility to find all those references and change them. For example, we need to change our procedure to use an input parameter of VARCHAR(15):
Then, we see that it returns accurate information:
The task of finding references to a column is a bit of an art. I have a great query you can run to locate all objects that reference a specific word. That word can, of course, be the name of a column whose data type you want to change:
SELECT ss.name as 'Schema', so.name as 'Object', so.type as 'Object Type' FROM sys.objects as so INNER JOIN sys.schemas as ss ON so.schema_id = ss.schema_id WHERE so.name in (select object_name(id) from syscomments where text like '%WordToLookFor%')
Next Steps:
Leave a comment if you found this tutorial helpful!
I hope you found this tutorial helpful! If you are just starting out with SQL Server, you might also be interested to know how to change a column’s name: Here is the full tutorial on the topic:
How to change a column name in SQL Server
Also, it’s possible to change the name of an entire table in SQL Server. Learn how:
How to change a table name in SQL Server
Finally, I mentioned stored procedures in this tutorial. If you are new to SQL Server, understand that stored procedures are a HUGE PART of any database. It’s very important to know what they are and how to write them. Check out the full beginner friendly tutorial here:
SQL Server Stored Procedure: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
I hope you found this tutorial helpful. If you have any questions, leave a comment. Or better yet, send me an email!
