In this step by step SQL Tutorial I will show you how to use Alter Table Alter Column. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
Who is it for?
- People who would like to learn basics of using Alter Table Alter Column
- Those who are preparing for Microsoft certifications and need to cover this topic.
In the Object Explorer I open required database in this case it is AdventureWorksDW2012 then I expand Tables folder and find table which I want to remove. In this example it is dbo.Employee table.
In this example I will show you how to change NULL in BirthDate to NOT NULL using Alter Table Alter Column statement.
Below you can see my script Alter Table Alter Column and you can notice that in the BirthDate I changed null to not null. I run my query and in the Messages box I get information that my code was run successfully.
I refresh Object Explorer and go to table dbo.DimCustomer, BirthDate field and I can see that null was changed to not null.
In a dbo.DimCustomer table I go to NumberCarsOwned and check change the data type from tinyint to int.
Below you can see that in my Alter Table Alter Column code I placed int data type. I run my query and I can see in the Messages window that my query was run successfully.
I refresh Object Explorer and I can see that NumberCarsOwned data type was changed.
I hope that help