Change SQL Server Table Structures with SQL ALTER TABLE

By:   |   Updated: 2022-09-19   |   Comments   |   Related: > Database Design


Problem

Just like humans, the schema for a SQL Server database has a life cycle. The database is first deployed (birth), then it is widely used (living), and finally, it is decommissioned (death). A database administrator during this life cycle might be asked to add, change, and drop columns within an existing table. How can we execute these tasks without using the graphical user interface within SQL Server Management Studio (SSMS)?

Solution

Microsoft supports the SQL ALTER TABLE syntax to help the database administrator make changes to a table. Today, we will explore the three main tasks: add a column, change a column, and delete a column in this SQL Tutorial.

Business Problem

The Azure virtual machine named vm4sql19 has a copy of the AdventureWorks database already installed. We will use this environment for our Transact SQL (T-SQL) exploration.

Database Files

Each database has at least two files. The data file contains both data pages and index pages. The transaction log file is used to maintain the ACID properties of a database. Check out Microsoft's documentation to learn more about database files and file groups. Execute the T-SQL script below to obtain more information about the database.

--
-- 1 - Files used by database
--
SELECT
  F.file_id,
  F.type,
  F.type_desc,
  F.name,
  F.physical_name,
  F.size * 8/1024 'Size (MB)',
  F.max_size
FROM
  sys.database_files as F
GO

The image below shows the name of the database: AdventureWorksLT2019. However, the logical names of the files show that it is actually based on the 2012 schema. It is always important to ensure the log file is smaller than the data file. It should only capture transactions until they can be written to the data file. If this is not the case, log backups are not being executed, or a large transaction is being performed without checkpoints.

alter table columns (TSQL) - list database files.

It is very important to understand that tables are related to files. The dropping of columns from a table creates an opportunity to reclaim space. We will get more into the details later in the tip.

Database Tables

There are two types of objects stored in a database: objects that use data pages and objects that don't. For instance, stored procedures and functions are just code. These objects are stored as metadata inside the system catalog and can be investigated using system catalog views. Tables and indexes use space within the data file. On the other hand, most of the time, a view is just a pre-compiled T-SQL statement. However, if the view is materialized, the index will take up space inside the data file.

--
-- 2 - Tables inside database
--
SELECT
  S.name AS SCHEMA_NM1,
  S.schema_id AS SCHEMA_ID1,
  T.name AS TABLE_NM1,
  T.object_id AS TABLE_ID1,
  T.type AS TYPE_CD1,
  T.type_desc AS TYPE_NM1,
  OBJECTPROPERTYEX(t.object_id, 'Cardinality') as ROW_CNT1,
  T.modify_date AS MODIFY_DTE1
FROM
  sys.tables AS T
JOIN
  sys.schemas AS S
ON
  T.schema_id = S.schema_id
GO

The above query shows details of each table in the Adventure Works database. We can see from the image below that 12 of the 14 tables reside in the SalesLT schema. The OBJECTPROPERTYEX metadata function is a really quick way to get table counts. Lastly, I created two big tables using Adam Machanic's "Thinking Big (Adventure)" script.

alter table columns (TSQL) - list database objects.

In a nutshell, it is important to know the volume of data stored in the tables. This will help prioritize which tables to investigate first when optimizing performance.

Table Definition

Each table is composed of an ordered list of columns that have a defined data type. Please see MSDN documentation for more details. Columns can either allow or not allow null values. The code below shows three different ways to explore a given table.

--
-- 3 - Show table details (describe, contents, space)
--
 
-- Get table definition
sp_help 'SalesLT.Product'
 
-- Show products
SELECT * FROM SalesLT.Product
GO
 
-- Space of table
sp_spaceused 'SalesLT.Product'
GO

There are a bunch of system stored procedures that allow the user to retrieve information from the database and/or make changes to the database. Please see MSDN documentation for more details. The sp_help stored procedure returns database information about a given object. The image below shows the details of the SalesLt.Product table.

alter table columns (TSQL) - show details of user defined table.

The SELECT statement just gives us a sample of the data in the table. We can use the TOP command to limit the number of rows returned. What is more interesting is the sp_spaceused stored procedure. It tells us useful information such as reserved space, data space, and index space in kilobytes.

alter table columns (TSQL) - show space of table before removing columns.

To recap, there are many system stored procedures that can help the database administrator do their daily job.

Remove Column with the SQL ALTER TABLE STATEMENT

We use the ALTER TABLE command to remove, change, and create columns. In this example, we determined that the photo stored in the database and the image's file name are no longer needed.

--
-- 4 - How to drop a column
--
 
-- Remove columns
ALTER TABLE SalesLT.Product
DROP COLUMN ThumbNailPhoto, ThumbnailPhotoFileName;
GO
 
-- Fails showing missing columns
SELECT ThumbNailPhoto, ThumbnailPhotoFileName FROM SalesLT.Product
GO
 
-- Rebuild the table
ALTER TABLE SalesLT.Product REBUILD
GO
 
-- Space after removing columns
sp_spaceused 'SalesLT.Product'
GO
 
-- Show products
SELECT TOP 5 * FROM SalesLT.Product
GO

The execution of the first T-SQL statement drops the column. The command either returns a success or failure. The image below shows a successful execution.

alter table columns (TSQL) - drop column has not real output.

If we try to query the missing columns after column removal, the query analyzer tells us the column names are invalid. The image below shows the output from executing the second T-SQL statement.

alter table columns (TSQL) - query analyzer can not find columns in updated schema.

If we execute the third and fourth T-SQL statements, the table will be rebuilt to reclaim space, and the data/unused space has reduced. This is because images tend to take up a lot of file space.

alter table columns (TSQL) - space used by table after dropping columns and rebuilding.

Finally, we can sample five records to look at the new table schema.

alter table columns (TSQL) - select top 5 records from new table schema.

Use the DROP COLUMN clause of the ALTER TABLE command to remove any unwanted columns.

Rename Column with sp_rename

The ALTER TABLE command does not support the renaming of columns. However, a system stored procedure called sp_rename can be used to change this metadata. Note: Any database objects dependent upon the name must be dropped, recreated, and re-validated.

--
-- 5 - How to rename a column
--
 
-- Drop the constraint
ALTER TABLE SalesLT.Product DROP CONSTRAINT [CK_Product_Weight]
GO
 
-- Rename column
EXEC sp_rename 'SalesLT.Product.Weight', 'WeightGrams', 'COLUMN';
GO
 
-- Create new constraint
ALTER TABLE [SalesLT].[Product] WITH NOCHECK ADD CONSTRAINT [CK_Product_Weight] CHECK (([WeightGrams]>(0.00)))
GO
 
-- Enable constraint
ALTER TABLE [SalesLT].[Product] CHECK CONSTRAINT [CK_Product_Weight]
GO
 
-- Show products
SELECT TOP 5 * FROM SalesLT.Product
GO

The above example shows that the constraint CK_Product_Weight is dependent upon the column weight. If the business line asks the IT team to change the column name to WeightGrams, we must perform the following steps:

  • Step 1 - Drop existing constraint
  • Step 2 - Rename existing column
  • Step 3 - Create new constraint
  • Step 4 - Validate new constraint
  • Step 5 - Check out new schema

Executing the first four T-SQL statements will return a success or failure message.

alter table columns (TSQL) - most alter statements have no output.

The last T-SQL statement shows the modification to the table schema. The column name has been successfully changed.

alter table columns (TSQL) - image shows table with renamed column.

Use the sp_rename system stored procedure to rename a column. Just watch out for any objects that depend on the column name.

Add Column with the SQL ALTER TABLE STATEMENT

The ALTER TABLE command supports adding columns to an existing table. Unless you specify a default value, the column should be defined as nullable. The T-SQL code below creates a new column named WeightPounds and uses an update statement to populate the column with values.

--
-- 6 - How to add a column
--
 
-- Add new field
ALTER TABLE [SalesLT].[Product] ADD [WeightPounds] [decimal](8, 2) NULL;
GO
 
-- Set value
UPDATE [SalesLT].[Product] SET [WeightPounds] = [WeightGrams] / 453.59237;
GO
 
-- Show the table
SELECT ProductId, WeightGrams, WeightPounds FROM [SalesLT].[Product] WHERE WeightGrams IS NOT NULL
GO

Please note that the column was defined as eight digits with a precision of two decimal places. This definition is reflected in the output below.

alter table columns (TSQL) - add a new column and update the values.

Use the ADD clause of the ALTER TABLE command to create new columns. Interestingly, the COLUMN keyword was dropped from the statement.

Modify Column with the SQL ALTER TABLE STATEMENT

The ALTER TABLE command does support the alteration of existing columns within an existing table. In our example, the product owner on the business team has requested more precision for the weight columns. The script below makes changes to two columns in the products table.

--
-- 7 - How to alter a column
--
 
-- Grams - Change from (8,2) -> (10,4)
ALTER TABLE SalesLT.Product ALTER COLUMN [WeightGrams] decimal (10,4);
GO
 
-- Pounds - Change from (8,2) -> (10,4)
ALTER TABLE SalesLT.Product ALTER COLUMN [WeightPounds] decimal (10,4);
GO
 
-- Show the table
SELECT ProductId, WeightGrams, WeightPounds FROM [SalesLT].[Product] WHERE WeightGrams IS NOT NULL
GO

The output of the SELECT statement shows that the precision has changed in the resulting columns.

alter table columns (TSQL) - change the weight columns to have 4 digit of precision.

Use the ALTER COLUMN clause of the ALTER TABLE command to modify the definition of existing columns.

Calculated Columns

There are times when storing the same data twice is a waste of space. In fact, there might be a situation in which we have plenty of processing power but not necessarily enough disk space. Since the weight in pounds can be calculated from weight in grams, we can create a calculated (virtual) column. The value of the column is created at execution when the data is queried.

--
-- 8 - Calculated column
--
 
-- Add new field
ALTER TABLE [SalesLT].[Product] DROP COLUMN [WeightPounds];
GO
 
-- Add new field
ALTER TABLE [SalesLT].[Product] ADD [WeightPounds] AS CAST(([WeightGrams] / 453.59237) AS DECIMAL (10,4));
GO
 
-- Show the table
SELECT ProductId, WeightGrams, WeightPounds FROM [SalesLT].[Product] WHERE WeightGrams IS NOT NULL
GO

The above T-SQL drops the existing column and creates a new calculated column. Please see the MSDN documentation for more details. In fact, you can turn the virtual column into a physical one by using the keyword PERSISTED.

Virtual Ordering Columns

When a database administrator adds a new column to a table, it is appended to the end of the table. How can we order the columns so the end user has them listed in the order they want? One solution is to use a user-defined view.

--
-- 9 - Use view to enforce order
--
 
CREATE VIEW vProductsWeights
AS
SELECT
    [ProductID]
   ,[Name]
   ,[ProductNumber]
   ,[Color]
   ,[StandardCost]
   ,[ListPrice]
   ,[Size]
   ,[WeightGrams]
   ,[WeightPounds]
   ,[ProductCategoryID]
   ,[ProductModelID]
   ,[SellStartDate]
   ,[SellEndDate]
   ,[DiscontinuedDate]
   ,[rowguid]
   ,[ModifiedDate]
FROM
    [AdventureWorksLT2012].[SalesLT].[Product]
GO

The above T-SQL statement creates a view that orders the columns virtually. Some end users might want to reorder the columns physically. In the next section, I will show you a technique that tools like Visual Studio Database Projects use to guarantee end state conditions such as column ordering.

Physically Ordering Columns

The code behind this process is relatively large since we must recreate the table with all the constraints, including check, foreign key, and primary key. The image below shows the three steps required to reorder the columns in a table physically. It is dependent upon the existence of a temporary schema named [tmp] and a backup schema named [bac].

alter table columns (TSQL) - the process to physically re-order table columns.

The most interesting code is the T-SQL required to move a table from one schema to another. The code below assumes that both schemas exist and a new table with a new column order exists in the [tmp] schema. This table has already been populated with the data from the existing table in the [SalesLT] schema.

--
-- 10 – Move tables around
--
 
-- Transfer old to bac
ALTER SCHEMA [bac] TRANSFER [SalesLT].[Product];
GO
 
-- Transfer new to saleslt
ALTER SCHEMA [SalesLT] TRANSFER [tmp].[Product];
GO

To summarize, physically ordering the columns in a table takes much work. Enclosed is the full script for the product table in the AdventureWorks database.

Next Steps
  • The database administrator can use the ALTER TABLE syntax to drop, add, or alter columns. This statement does not support the renaming of a column. However, a system stored procedures can help with that task. When creating a new column based on existing data, the column can either be calculated at query time or stored on disk. Look at the calculated column feature of a table to save disk space.
  • When removing columns from a table, there is an opportunity to reclaim disk space. Save the space information about the table before the change. Delete the column from the table and REBUILD the table. Last but not least, compared the new space information to the old.
  • When adding columns to a table, the new columns are always added to the end of the table. If the business line wants to see the columns displayed in a particular order, then use a user-defined view to provide this functionality. Otherwise, a three-step process using a temporary table and two additional schemas is required to make the change successfully.
  • In short, databases and tables have a life cycle. During your time at a company as a database administrator, you will be making changes to them. Get used to the techniques shown in this article and sample code since they will come in handy.
  • Check out these additional tips:


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author John Miner John Miner is a Data Architect at Insight Digital Innovation helping corporations solve their business needs with various data platform solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-09-19

Comments For This Article

















get free sql tips
agree to terms