How to modify a SQL Server table with a script?

T-SQL script examples to modify SQL Server columns using the Alter table and Alter column statements.

How to modify a SQL Server table with a script using thee ALTER statement? 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.

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.

1. Create the SQL Server table to modify using the scripts

Let’s use a table inspired by the sales table used in this first tutorial on how to create a SQL Server table. It have a different structure, the code is available below. Copy and paste it in SSMS to create the sample table used in the next queries.

-- 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

2. Script to change the column length of a SQL Server 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.

3. Script to add or remove columns from a SQL Server table

The Alter Table command also allows to add or drop columns from a given table. This T-SQL script demonstrates how to add and remove columns from the SQL Server SALES table, in 4 steps.

-- 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

The 4 queries executed above performs the following operations:

1. The first query in the script adds a single column named NewColumnA to the SALES table using the ALTER TABLE statement combined with the ADD keyword. The data type of the new column is VARCHAR(10) and it allows NULL values.
2. The second one adds multiple columns to a table also with the ADD keyword. In this case, two columns named NewColumnB and NewColumnC are added to the table.
3. The third section of the T-SQL code removes a single column from a SQL Server table using the DROP COLUMN keyword.
4. And the last query removes multiple columns from the table. In this case, the NewColumnB and NewColumnC columns that were added in the second section are removed.

4. Conclusion on modifying SQL Server tables and columns

In conclusion, modify a SQL Server table with and its columns with a script is a straightforward process that involves the use of the ALTER TABLE statement. By connecting to the server hosting the database and table, developers can easily modify the table’s structure, add or remove columns, or change column constraints using T-SQL statements.

This tutorial covered three main use cases for modifying a SQL Server table with a script, including changing the length of a column, adding or removing a single column or multiple columns from a table. Each use case was illustrated with examples of T-SQL queries that demonstrated how to achieve the desired modifications.

Overall, by following the techniques outlined in this tutorial, developers can efficiently modify SQL Server tables, and adapt their structures to meet the needs of their applications. 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.

Manage SQL Server user defined functions