The basic syntax for adding a column in SQL Server is just two lines. But there are enough edge cases to really throw you off guard if you’re not careful. Your actual code will depend on things like, how you define the column, whether the table already has data, what constraints you need, etc. In the real world, there’s quite a bit more to know than just two lines.
This guide walks through all the common scenarios so you have a solid reference regardless of what you’re trying to do.
The Basic Syntax
The simplest way to add a column uses the ALTER TABLE statement.
The basic syntax goes like this:
ALTER TABLE table_name
ADD column_name data_type;For example, to add an email column to a Users table:
ALTER TABLE Users
ADD Email VARCHAR(255);That’s it. The column gets added to the end of your table structure.
If there’s data in the table, the new column will contain NULL values until data is added.
Adding a Column with Constraints
Most of the time, you’ll want more control over your new column. You’ll often want to add constraints in order to prevent bad data from entering the database. Let’s look at how to add common constraints.
Adding a Column that Can’t be NULL
If the column cannot contain NULL values, you’ll need to use a NOT NULL constraint:
ALTER TABLE Users
ADD IsActive BIT NOT NULL;But wait! There’s a catch. If your table already has data, this will fail. You can’t add a NOT NULL column to a table with existing rows unless you provide a default value.
The above statement will result in the following error if there’s already data in the table:
Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'IsActive' cannot be added to non-empty table 'Users' because it does not satisfy these conditions.
One way to overcome this is by specifying a default value.
Adding a Column with a Default Value
You can include a DEFAULT constraint to specify a default value to populate your new column with. Doing this means that if you insert a new row without providing data for this column, the default value will be used. But if you do provide a value, then that value will be used instead of the default value. If the table already contains data at the time of adding the column (and the DEFAULT constraint), the default value will be inserted in all existing rows.
Here’s an example:
ALTER TABLE Users
ADD IsActive BIT NOT NULL DEFAULT 1;Now all existing rows get the value 1, and any new rows will also get 1 unless you specify otherwise.
Another way to deal with this is to use a three step process, where you add a nullable column, add default data, then add the constraint once all NULL values have been changed (see “Handling Existing Data” later in this article).
Adding a Column with a CHECK Constraint
A CHECK constraint is a type of constraint that checks the data before it inserts it into the database. You specify the rules of the constraint.
Example:
ALTER TABLE Products
ADD Rating INT CHECK (Rating >= 1 AND Rating <= 5);In this example, we’re adding a Rating column, and we’re specifying that all values must be between 1 and 5. If someone tries to insert data that’s outside of that range, they’ll get an error.
Adding a Unique Column
You can include a UNIQUE constraint to make the column unique. That is, the column will only accept unique values. This means you can guarantee that the column contains unique values across all rows. For example, if you assign an EmployeeCode of E101 to an employee, no other employee will be able to be assigned that code.
Example:
ALTER TABLE Employees
ADD EmployeeCode VARCHAR(50) UNIQUE;This works fine on an empty table, but if your table already has data, SQL Server will throw an error because adding a nullable unique column causes every existing row to get NULL. And SQL Server doesn’t allow multiple NULL values in a UNIQUE column. Let’s quickly look at both scenarios.
The following works fine:
CREATE TABLE EmployeesEmpty (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
EmployeeName VARCHAR(100) NOT NULL
);
ALTER TABLE EmployeesEmpty
ADD EmployeeCode VARCHAR(50) UNIQUE;
GOWe could add the constraint because the table was empty.
But on a table with data, we get an error:
CREATE TABLE EmployeesWithData (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
EmployeeName VARCHAR(100) NOT NULL
);
INSERT INTO EmployeesWithData (EmployeeName) VALUES
('Zara Martinez'),
('Henrik Olsen'),
('Priya Sharma');
ALTER TABLE EmployeesWithData
ADD EmployeeCode VARCHAR(50) UNIQUE;
GOOutput:
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.EmployeesWithData' and the index name 'UQ__Employee__1F6425485D0C330B'. The duplicate key value is (<NULL>).
Msg 1750, Level 16, State 1, Line 1
Could not create constraint or index. See previous errors.
The statement has been terminated.
To be more precise, this only happens when the table contains two or more rows. If it contained just one row, we’d be fine, due to NULL being present in only one row. It’s only a problem if the table contains more than one row (which is the most likely scenario anyway).
A simple workaround is to add the column first, populate it, then add the unique constraint:
-- Step 1: Add the column without the unique constraint
ALTER TABLE EmployeesWithData
ADD EmployeeCode VARCHAR(50) NULL;
GO
-- Step 2: Populate it with unique values
UPDATE EmployeesWithData SET EmployeeCode = 'ZM-2401' WHERE EmployeeID = 1;
UPDATE EmployeesWithData SET EmployeeCode = 'HO-2402' WHERE EmployeeID = 2;
UPDATE EmployeesWithData SET EmployeeCode = 'PS-2403' WHERE EmployeeID = 3;
GO
-- Step 3: Now add the unique constraint
ALTER TABLE EmployeesWithData
ADD CONSTRAINT UQ_EmployeesWithData_EmployeeCode UNIQUE (EmployeeCode);
GOThat should work without error. You can run the following to verify that it worked:
SELECT * FROM EmployeesWithData;You can also check that the UNIQUE constraint is in place by running a little test:
UPDATE EmployeesWithData SET EmployeeCode = 'ZM-2401' WHERE EmployeeID = 2;That should result in the following error:
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_EmployeesWithData_EmployeeCode'. Cannot insert duplicate key in object 'dbo.EmployeesWithData'. The duplicate key value is (ZM-2401).
The statement has been terminated.
That confirms that the UNIQUE constraint is working as expected.
This same concept applies whenever you’re adding any kind of constraint to a table that already has data. Add the column first, clean up the data so it satisfies the constraint, then apply the constraint.
If you run into this in more complex scenarios, other approaches worth knowing about include adding the unique constraint as a filtered index (my article on fixing error 1505 uses this approach). This lets you exclude NULLs entirely. Another approach would be to handle it through application logic before applying the constraint. But for most cases, the three-step approach above is all you need.
Adding Multiple Columns at Once
You can add several columns in a single statement by separating them with commas:
ALTER TABLE Customers
ADD
DateOfBirth DATE,
LoyaltyPoints INT DEFAULT 0,
PreferredContact VARCHAR(50);This approach is cleaner and more efficient than running multiple ALTER TABLE statements.
Adding an Identity Column
Identity columns auto-generate sequential numbers. Here’s an example:
ALTER TABLE Orders
ADD OrderID INT IDENTITY(1,1) PRIMARY KEY;Using IDENTITY(1,1) means start at 1 and increment by 1 for each new row. Using this syntax means you can start at any number and increment by any number. For example IDENTITY(100, 10) starts at 100 and increments by 10.
In our example we’re also setting the IDENTITY column as the primary key. This makes sense, given both primary keys and IDENTITY columns hold unique values that can identify each row.
I should point out that you can only have one identity column per table.
Adding a Column at a Specific Position
Here’s something that often surprises people. SQL Server doesn’t let you specify where a column appears in the table structure using T-SQL. When you add a column, it goes to the end.
If you absolutely need a column in a specific position, you have two options:
- Use SQL Server Management Studio (SSMS), which has a visual designer that can reorder columns. Behind the scenes, it actually drops and recreates the table (which could be risky with large tables).
- Manually create a new table with the desired structure, copy data, drop the old table, and rename the new one
For most purposes, column order doesn’t matter since you should be selecting columns by name, not position.
Adding Computed Columns
Computed columns derive their values from other columns. Here’s an example of adding one to an existing table:
ALTER TABLE Products
ADD TotalPrice AS (UnitPrice * Quantity);Here, the TotalPrice column’s value is computed by multiplying the UnitPrice by Quantity. The computed column will be updated automatically whenever data in any of the other two columns changes.
You can also persist computed columns so they’re stored physically:
ALTER TABLE Products
ADD TotalPrice AS (UnitPrice * Quantity) PERSISTED;Persisted columns perform better in queries since the value doesn’t need to be calculated each time, but they do take up space.
Adding Columns with Foreign Keys
To add a column that references another table, we use a foreign key:
ALTER TABLE Orders
ADD CustomerID INT,
CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);This creates what’s known as a relationship. In this case we created a relationship between the Orders and Customers tables.
You can also add the column first, then add the foreign key separately:
ALTER TABLE Orders
ADD CustomerID INT;
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);The second approach is useful when you need to populate the column with data before enforcing the foreign key relationship.
Handling Existing Data
As we saw earlier, the quickest solution when adding a NOT NULL column to a table with existing data is a DEFAULT constraint. This handles both existing and new rows in one step:
ALTER TABLE Users
ADD Department VARCHAR(100) NOT NULL DEFAULT 'Unassigned';This prevents us from getting an error due to the NULL values that will populate the column once created.
But adding a DEFAULT constraint might not always be what you want. Sometimes you might want to backfill existing rows with a placeholder value, while forcing the application to explicitly provide the value for all new rows going forward. In that case you’d intentionally leave off the DEFAULT constraint, which means you need to handle the existing data manually before you can enforce NOT NULL:
-- Step 1: Add the column as nullable (no default, so new rows must supply a value)
ALTER TABLE Users
ADD Department VARCHAR(100) NULL;
GO
-- Step 2: Backfill existing rows with a placeholder
UPDATE Users
SET Department = 'Unassigned'
WHERE Department IS NULL;
GO
-- Step 3: Now that no NULLs exist, enforce NOT NULL
ALTER TABLE Users
ALTER COLUMN Department VARCHAR(100) NOT NULL;
GOThe end result is a NOT NULL column with no DEFAULT constraint attached. So if any application code tries to INSERT a new row without supplying Department, SQL Server will throw an error. That’s the intended behavior here. You’re using the constraint to enforce that new data is always complete, while still giving old rows a reasonable placeholder to keep things running.
The same concept applies with other constraint types. Add the column, fix up the existing (NULL) data so it satisfies the constraint, then apply the constraint.
Checking If a Column Exists Before Adding
To avoid errors when running scripts multiple times, you can check if the column already exists:
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Users'
AND COLUMN_NAME = 'Email'
)
BEGIN
ALTER TABLE Users ADD Email VARCHAR(255);
ENDOr use the more modern COL_LENGTH function:
IF COL_LENGTH('Users', 'Email') IS NULL
BEGIN
ALTER TABLE Users ADD Email VARCHAR(255);
ENDBest Practices and Considerations
Here are some things to consider when adding columns in your database.
Name your Constraints Explicitly
SQL Server will auto-generate names like DF__Users__IsActive__6DCC4D03, which are ugly and hard to work with. Fortunately, you have the option of naming them yourself. For example:
ALTER TABLE Users
ADD IsActive BIT NOT NULL
CONSTRAINT DF_Users_IsActive DEFAULT 1;It’s a good idea to do this whenever possible. It will make your constraints much easier to find and work with later on.
Test on a Development Database First
Before you go live with any schema changes, do yourself a favor and run them on a development or staging database first. Adding a new column to a massive production table might seem like a quick fix, but it can be a deceptively risky operation. Depending on your database setup, it can trigger a metadata lock or a full table rewrite, essentially freezing your app while the database works through the backlog.
Testing it out beforehand lets you spot any issues in a safe space, so you don’t end up having to explain an accidental outage to your users (or your boss).
Consider Impact on Applications
Adding a column might seem like a safe, additive change. After all, you’re not removing or modifying anything, just adding something new. And in many cases it is safe. But depending on how the column is defined, it can have ripple effects that aren’t immediately obvious. It’s worth thinking through the full picture before running the ALTER TABLE statement in production.
For example, when you add a NOT NULL column without a default, any application code that inserts into that table without explicitly including the new column will start throwing errors. This scenario comes up when the table was empty at the time the column was added, or more commonly, when you’ve followed the three-step approach covered earlier (adding the column as nullable, backfilling existing rows, then making it NOT NULL) and intentionally left off a DEFAULT constraint. In either case the column is NOT NULL with no default, and so the database will reject any INSERT that doesn’t explicitly supply a value for it.
If you’re not expecting this, it can cause sudden breakage across multiple parts of an application, including stored procedures, ORMs, and any raw SQL in your codebase. Before making the change, it’s worth searching your codebase for every INSERT that touches the affected table and updating them accordingly.
It’s also worth thinking beyond just INSERT statements. Adding a column can affect SELECT * queries, application-level model classes that map directly to table columns, and any reporting or export logic that expects a fixed schema. If your application maps database rows to objects automatically (like with Entity Framework or Hibernate), you may need to update those models before or alongside the schema change, otherwise you risk runtime errors or silent data mismatches. Schema changes and application deployments ideally go hand in hand. Changing the database independently of the application, or vice versa, is a common source of hard-to-diagnose production issues.
Document your Changes
Treating schema changes as proper code means keeping your ALTER TABLE scripts in version control alongside your application code. This gives you a full history of how your database has evolved over time, makes it easy to reproduce the schema in a new environment, and means you can trace back exactly when and why a column was added. Without this, database schemas have a tendency to quietly drift between environments. The production database ends up with columns that staging doesn’t have, or vice versa, and nobody is quite sure what the source of truth is.
Beyond just storing the scripts, it’s worth adopting a structured migration approach using a tool like Flyway or Liquibase, or the built-in migration systems in frameworks like Django or Rails. These tools apply schema changes in a controlled, sequential order and track which changes have already been applied to a given environment. That way you’re not manually running scripts and trying to remember what’s already been executed where. Even if a dedicated migration tool feels like overkill for your project, at minimum each change should be a clearly named, timestamped SQL file committed alongside the feature or fix that required it.
Use Transactions for Multiple Changes
When you’re making several schema changes at once, wrapping them in a transaction means either all of them succeed or none of them do. Without a transaction, if something goes wrong halfway through, you can end up in an inconsistent state (some columns added, others not) which can be messy to untangle, especially in production. Transactions give you a clean rollback point if anything goes wrong, and they’re a good habit any time you’re making changes that belong together as a unit.
Example:
BEGIN TRANSACTION;
ALTER TABLE Users ADD Department VARCHAR(100);
ALTER TABLE Users ADD HireDate DATE;
-- Check if everything looks good
-- ROLLBACK; -- If there's a problem
COMMIT; -- If everything's fineInterestingly, DDL statements like ALTER TABLE are transactional in SQL Server, which is not the case in all databases. This means SQL Server gives you a genuine safety net here. If you ROLLBACK, the schema changes are fully undone. That said, transactions still hold locks while they’re open, so keep them as short as possible and avoid doing large data operations inside the same transaction as your schema changes.
Performance Considerations
Adding columns to small tables is nearly instantaneous. For large tables, here’s what to keep in mind:
- Adding a nullable column is fast (just metadata change in most cases)
- Adding a
NOT NULLcolumn with a default can be slow on large tables because SQL Server may need to update every row - SQL Server 2012 and later can optimize
NOT NULLcolumns with constant defaults as metadata-only changes under certain conditions - Adding computed columns (non-persisted) is fast since no data is stored
- Adding persisted computed columns requires calculating and storing values for all rows
For very large tables, consider maintenance windows and possibly using database snapshots or backups before major schema changes.
Wrapping Up
Expanding your SQL Server schema is simple in theory, but the secret to a smooth deployment lies in the details. While the ALTER TABLE statement gets the job done, your primary focus should be on how those changes interact with your existing environment. This means being intentional with constraints and having a clear plan for NOT NULL columns, since adding those to a populated table will require a default value to prevent the operation from failing. By prioritizing these safeguards, you can evolve your database structure efficiently without risking the integrity of your production data.