Adding columns to tables in SQL Server is a fundamental task for both database developers and administrators. Whether prompted by the pursuit of application features or script enhancements, this process demands precision and careful consideration of potential complications. In this blog post, we’ll walk through considerations and demo how adding a new column in SQL.
SQL DBA Responsibilities
As a SQL Server DBA you may be responsible for adding all columns on business critical tables to ensure it is handled safely. For instance, if the SQL database is configured with Replication, we need to consider the implications outlined in the MS Docs regarding “Considerations for Schema Changes” and “Replicate Schema Changes“. While changes typically propagate seamlessly to subscribers, caution is warranted, especially when incorporating default values, as they can impact Replication Latency.
Add Column to Table in SQL Server
To add a column to a table in SQL Server we use the ALTER TABLE statement. The example below demonstrates adding a new column named ‘Gravity’ with a FLOAT data type on the ‘MoonData’ Table:
USE Jupiter;
GO
-- Create a sample table
CREATE TABLE MoonData (
MoonID INT PRIMARY KEY,
MoonName VARCHAR(100),
MoonType VARCHAR(50)
);
-- Add a column named 'Gravity' with a default value
ALTER TABLE MoonData
ADD Gravity FLOAT DEFAULT 1.0;
-- Display the structure of the updated table
EXEC sp_help 'MoonData';
Considerations & Notes
1. Verify New Column Exists
After adding a column to a table in SQL, it’s important that we verify it exists. This can be done by running the sp_help procedure, as shown in the screenshot above. Alternatively, querying sys.columns provides similar information for all columns in a table. If Replication is enabled for the table, we can ensure the DDL statement has been effectively propagated through the Replication Monitor before confirming the table’s existence on all Subscriber SQL Servers.
2. Adding Default Values
When adding columns to tables with default values, assessing any implications is necessary, such as considering table sizes and transaction log file sizes. Monitoring Replication Latency after the SQL DDL statement to add the new column has been completed is advisable.
Microsoft Documentation offers more detailed guidance on specifying default column values, covering multiple methods, including setting defaults via constraints, defining them within table structures, and using the ALTER TABLE statement for implementation.
3. Drop Column in SQL Server
In scenarios where the objective is to remove a column rather than add it, we can use the ALTER TABLE SQL statement with the DROP COLUMN clause. Below is an example demonstrating the removal of the column we added above in this post:
-- Remove column from table ALTER TABLE MoonData DROP Gravity;
Leave a Reply