Modify a SQL Server table

How to modify a SQL Server table ? With the SQL Server Alter Table command, modify an existing table. The ALTER TABLE SQL Server query helps you update an already existing table. The example script below allows you to modify the Sales table previously created, by changing the length of the Month Name column. Of course you need to adapt it to your needs.

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.

How to modify a SQL Server table?

We can see in the script the two lines, one is ALTER TABLE and the other keyword ALTER COLUMN to explicitly change the length of the column.

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

-- we change the length to 100, initially 50

So in the Expert-Only SQL Server tables scripts, we have seen previously how to create a table. And also, how to modify a SQL table. Now to go further, check this article on how to delete 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, it’s OK. But if you insert month names (up to 9 chars for September), then the system through an error.

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 trhown is the folowwing:

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.

To go further, check the function section to create reusable code that return a value from tables.

Be the first to comment

Leave a Reply

Your email address will not be published.


*