T-SQL script examples to modify SQL Server tables using ALTER TABLE statements.
How to modify a SQL Server table with a simple script, using the ALTER statement? Simply connect to the server hosting the instance using SSMS for example. Then to the database and run an ALTER TABLE SQL Server query. The goal here 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 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 modify.
Before we dive in, note that technically when we use an ALTER TABLE statement, it is about many different types of objects:
- Column Set: Modify or add a column set for sparse columns.
- Column Storage: Change the storage properties of a column (e.g., from ROW to COLUMNSTORE).
- Columns: Add, modify, or remove columns.
- Constraints: Add, modify, or drop constraints. This includes primary keys, foreign keys, unique constraints, and check constraints.
- FileTable Directory Name: Change the directory name for a FileTable.
- Indexes: While you cannot directly create or drop indexes with ALTER TABLE, you can indirectly affect them by adding or dropping columns or constraints.
- Switch Partitions: Switch a table partition with another table’s partition.
- Table Name: Change the name of the table.
- Table Partitioning: Modify the partition structure of a table.
- Text/Image Filegroup: Migrate text or image data to a different filegroup.
In this article we’ll only cover only a few basics.
Table of Contents
1. Create a sample SQL Server table to modify using scripts
Let’s use a table inspired by the sales table used in this first tutorial on how to create a SQL Server table with a primary key. However, it have a different structure, the code is available below. Copy and paste it in SSMS to create the sample table, it will be required 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 a column length in SQL Server
In the script below, 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
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 SQL column size cannot be reduced more than the current content.
Read also : About SQL Server tables scripts basics, check also this second article on how to empty a SQL Server table.
3. SQL Server script to add or remove one column
The Alter Table command also allows to add or drop columns from a table. This T-SQL script below demonstrates how to add and remove one single column from the Sales table.
-- Script to add a column to the sales table ALTER TABLE [dbo].[SALES] ADD NewColumnA VARCHAR(10) NULL; GO -- Script to remove one column from a SQL Server table ALTER TABLE [dbo].[SALES] DROP COLUMN NewColumnA; GO
The 4 queries executed above performs the following operations:
- 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.
- The second section of the T-SQL code removes a single column from a SQL Server table using the DROP COLUMN keyword.
4. SQL Server script to add or remove multiple columns at once
Below are two similar scripts, but this time it allows to manage multiple columns at the same time.
-- Add multiple columns to the sales table ALTER TABLE [dbo].[SALES] ADD NewColumnB VARCHAR(10) NULL, NewColumnC INT NULL; GO -- Script to remove multiple columns from a SQL Server table ALTER TABLE [dbo].[SALES] DROP COLUMN NewColumnB, NewColumnC; GO
- The first 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.
- 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.
Modifying SQL Server tables is straightforward if they are empty
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.
The tricky part with modifying columns is when it comes to change the structure of an existing column with data already loaded. And even more complex is when changing, like reducing the size of a column that is also a foreign key for another table.
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