SQL Server: How to Use the ADD Keyword for Schema Changes

 

https://www.nilebits.com/blog/2024/07/sql-server-add-keyword-for-schema-changes/

When working with SQL Server, managing and modifying database schemas is a fundamental task. One of the key operations you might frequently perform is adding new columns, constraints, or indexes to your existing tables. This is where the ADD keyword becomes incredibly useful. This blog post will delve into how to effectively use the ADD keyword in SQL Server to perform schema changes, complete with code examples to illustrate each scenario.

Adding Columns to an Existing Table in SQL Server

One of the most common uses of the ADD keyword is to add new columns to an existing table. This operation is essential when you need to store additional data that wasn't initially considered during table creation.

Example 1: Adding a Simple Column

Suppose you have a table named Employees and you want to add a new column to store the employee's date of birth.

ALTER TABLE Employees
ADD DateOfBirth DATE;

In this example:

• ALTER TABLE Employees specifies that you are modifying the Employees table.
• ADD DateOfBirth DATE adds a new column named DateOfBirth with the DATE data type.

Example 2: Adding Multiple Columns

You can also add multiple columns in a single ALTER TABLE statement.

ALTER TABLE Employees
ADD 
    PhoneNumber VARCHAR(15),
    HireDate DATE;

Here, two new columns, PhoneNumber and HireDate, are added to the Employees table.

Adding Constraints to a Table in SQL Server

Constraints are rules that enforce data integrity. You can use the ADD keyword to apply constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK to your table.

Example 3: Adding a Primary Key Constraint

If you want to add a PRIMARY KEY constraint to an existing column, you would use the following SQL statement.

ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

In this example:

• ADD CONSTRAINT PK_Employees names the new primary key constraint PK_Employees.
• PRIMARY KEY (EmployeeID) designates EmployeeID as the primary key column.

Example 4: Adding a Foreign Key Constraint

To ensure referential integrity, you might add a foreign key constraint.

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Departments
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

Here:

• ADD CONSTRAINT FK_Employees_Departments creates a foreign key constraint named FK_Employees_Departments.
• FOREIGN KEY (DepartmentID) specifies the column that will be the foreign key.
• REFERENCES Departments(DepartmentID) establishes a link to the DepartmentID column in the Departments table.

Adding Indexes to Improve Performance in SQL Server

Indexes are critical for improving query performance. You can add indexes to existing tables to speed up data retrieval.

Example 5: Adding an Index

To add an index on a column, use the following syntax:

CREATE INDEX IX_Employees_LastName
ON Employees (LastName);

In this example:

• CREATE INDEX IX_Employees_LastName creates an index named IX_Employees_LastName.
• ON Employees (LastName) specifies that the index is on the LastName column of the Employees table.

Adding Default Values to Columns in SQL Server

When you add a column to a table, you can also set a default value that will be used if no value is provided.

Example 6: Adding a Column with a Default Value

To add a new column with a default value:

ALTER TABLE Employees
ADD Status VARCHAR(20) DEFAULT 'Active';

In this case:

• ADD Status VARCHAR(20) DEFAULT 'Active' adds the Status column with a default value of 'Active'.

Adding Constraints to New Columns in SQL Server

When adding a column, you might want to impose constraints directly on it.

Example 7: Adding a Column with a Not Null Constraint

To ensure a new column cannot have NULL values:

ALTER TABLE Employees
ADD EmailAddress VARCHAR(100) NOT NULL;

Here:

• NOT NULL ensures that every row must include a value for the EmailAddress column.

Conclusion

Using the ADD keyword in SQL Server is a powerful way to modify your database schema efficiently. Whether you're adding new columns, constraints, indexes, or default values, understanding how to use ALTER TABLE with ADD commands helps ensure your database evolves with your application's needs. Always remember to test schema changes in a development environment before applying them to production to avoid unintended disruptions.

Feel free to experiment with these examples and adjust them according to your specific database design requirements.

https://www.nilebits.com/blog/2024/07/sql-server-add-keyword-for-schema-changes/