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.
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.
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.
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.
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.
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.
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.
Finally, we can sample five records to look at the 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.
The last T-SQL statement shows the modification to the table schema. The column name has been successfully changed.
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.
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.
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].
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:
