Modify a SQL Server column with a script

How to modify a column in a SQL Server table with a script?

Tutorial to modify a SQL Server column with a T-SQL ALTER TABLE and ALTER COLUMN command. This SQL code allows you to modify the structure of a table. The table is created in the first part with a length of 20 characters. This already existing column is changed to a length of 100 characters.

The example used is the Sales table created in this CREATE TABLE SQL script example. Create the table first before changing the column. Connect to the target instance and database with SSMS and execute the code below in a new window.

-- Create the example table to store the Sales data
CREATE TABLE [dbo].[Sales] (
  [MonthName] NVARCHAR(20),
  [Amount] NUMERIC(5)
);

T-SQL script to change the length of a column

In effect, use this example of SQL Server code to update the sales table and change the months column. The goal is to give it a length of 100 characters instead of 20 characters.

-- Script to increase the length of the NVARCHAR field from 20 to 100 characters
ALTER TABLE [dbo].[Sales]
  ALTER COLUMN [MonthName] NVARCHAR(100);

Change the length of an SQL column without losing data

Modifying a table allows you to change the data stored in the table without deleting it. To modify a SQL Server table, the data already stored in the table must be compatible with the new data type. If the new data type is not compatible, then perform these actions in this order.

Store the data in a temporary backup table
Empty the source table with a truncate command
Modify the table with the ALTER script above
Insert the data back into the table with an INSERT INTO command
Check the data in the column and in the table, e.g. count the number of rows
Finally delete the backup table with a DROP TABLE request

This method allows you to change a SQL column and keep the data intact. Finally, here is an article that explains how to delete a SQL Server table from a database.