SQL Server ALTER TABLE

How to modify a SQL Server table using the Alter Table command? Simply connect to the server hosting the instance, the database and the table and run an ALTER TABLE SQL Server query. The goal is to update an already existing table to adapt its structure for example. Or the constraints associated to the table.

The example script below modify the Sales table previously created, by changing the length of the Month Name column. Of course, adapt it to the project requirement.

Script to modify a SQL Server table with ALTER TABLE

Moreover, the ALTER keyword in SQL allows you to add a column or remove a column. Use the ALTER twice: the first time to specify the target table and the second time to specify the column to change.

Let’s consider the sales table used in this first article to CREATE a table.

-- Create the sales table to change with ALTER
CREATE TABLE [dbo].[SALES] 
(
	[Year] 		INT NULL, 
	[MonthName] 	NVARCHAR(50) NULL, 
	[MonthCurrent] BIT NULL, 	
	[NumberMonth] 	TINYINT NULL
);
GO

Change the column length with a SQL script (ALTER COLUMN)

In the script two lines are used, the first one is the ALTER TABLE statement. And the other keyword is ALTER COLUMN to explicitly change the length of the month name column.

-- Change the length to 100, initially it was 50
ALTER TABLE [dbo].[SALES]
	ALTER COLUMN [MonthName] NVARCHAR(100);
GO

So in the SQL Server tables scripts, we have seen previously how to create a table. Now to go further, check this article on how to empty a SQL Server table.

Please note that if you want to decrease the size of the column and data is already there, then the database system does a check to verify the size of the data. So, if you reduce the size to 20 characters, it’s OK. But if you insert month names (up to 9 chars for September), then the system through an error.

Indeed, please note that the content needs to be compatible with the new length.

If the length decrease, then the operation needs an additional step that is to back up the content in another table or to shorten the content length.

ALTER TABLE [dbo].[SALES] ALTER COLUMN [MonthName] NVARCHAR(20);

insert into [dbo].[SALES] ([MonthName]) values ('January'); 
insert into [dbo].[SALES] ([MonthName]) values ('February'); 
insert into [dbo].[SALES] ([MonthName]) values ('March'); 
insert into [dbo].[SALES] ([MonthName]) values ('April'); 
insert into [dbo].[SALES] ([MonthName]) values ('May'); 
insert into [dbo].[SALES] ([MonthName]) values ('June'); 
insert into [dbo].[SALES] ([MonthName]) values ('July'); 
insert into [dbo].[SALES] ([MonthName]) values ('August'); 
insert into [dbo].[SALES] ([MonthName]) values ('September'); 
insert into [dbo].[SALES] ([MonthName]) values ('October'); 
insert into [dbo].[SALES] ([MonthName]) values ('November'); 
insert into [dbo].[SALES] ([MonthName]) values ('December');

ALTER TABLE [dbo].[SALES] ALTER COLUMN [MonthName] NVARCHAR(5);

The error thrown is the following:

Msg 2628, Level 16, State 1, Line 29
String or binary data would be truncated in table ‘Expert-Only.dbo.SALES’, column ‘MonthName’. Truncated value: ”.

Indeed, a column size cannot be reduced more than the current content.

Add or remove columns from a SQL Server table (ADD and DROP COLUMN)

The Alter Table command also allows to add or remove columns in a table.

-- Script to add a column to the sales table
ALTER TABLE [dbo].[SALES] 
	ADD 	NewColumnA VARCHAR(10) NULL;
GO

-- Add multiple columns to the sales table
ALTER TABLE [dbo].[SALES] 
ADD 	NewColumnB VARCHAR(10) NULL, 
	NewColumnC INT NULL;
GO

-- Script to remove one column from a SQL Server table 
ALTER TABLE [dbo].[SALES] 
	DROP COLUMN NewColumnA;
GO

-- Script to remove multiple columns from a SQL Server table 
ALTER TABLE [dbo].[SALES]  
	DROP COLUMN NewColumnB, NewColumnC;
GO

To go further, check the SQL Server function section to create reusable code that return a value from tables or built-in SQL functions and procedures.

Be the first to comment

Leave a Reply

Your email address will not be published.


*