Edit

Add Columns to a Table (Database Engine)

  • Article

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This article describes how to add new columns to a table in SQL Server by using SQL Server Management Studio or Transact-SQL.

Remarks

Using the ALTER TABLE statement to add columns to a table automatically adds those columns to the end of the table.

If you want the columns in a specific order in the table, you must use SQL Server Management Studio. Though it isn't recommended, for more information on reordering tables, see Change Column Order in a Table.

To query existing columns, use the sys.columns object catalog view.

Permissions

Requires ALTER permission on the table.

Use SQL Server Management Studio

Insert columns into a table with Table Designer

  1. In Object Explorer, right-click the table to which you want to add columns and choose Design.

  2. Select the first blank cell in the Column Name column.

  3. Type the column name in the cell. The column name is a required value.

  4. Press the TAB key to go to the Data Type cell and select a data type from the dropdown.

    This is a required value, and will be assigned the default value if you don't choose one.

    Note

    You can change the default value in the Options dialog box under Database Tools.

  5. Continue to define any other column properties in the Column Properties tab.

    Note

    The default values for your column properties are added when you create a new column, but you can change them in the Column Properties tab.

  6. When you're finished adding columns, from the File menu, choose Save table name.

Use Transact-SQL

Add columns to a table

The following example adds two columns to the table dbo.doc_exa.

ALTER TABLE dbo.doc_exa 
ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

See also

Next steps