Skip to content

Latest commit

 

History

History
77 lines (54 loc) · 3.62 KB

add-columns-to-a-table-database-engine.md

File metadata and controls

77 lines (54 loc) · 3.62 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Add Columns to a Table (Database Engine)
Learn how to add columns to an existing table in SQL Server and Azure SQL platforms by using SQL Server Management Studio or Transact-SQL.
WilliamDAssafMSFT
wiassaf
07/05/2024
sql
table-view-index
how-to
inserting columns
columns [SQL Server], adding
adding columns
>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current||=fabric

Add Columns to a Table (Database Engine)

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-pdw-fabricdw-fabricsqldb]

This article describes how to add new columns to a table in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

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 [!INCLUDE ssManStudioFull]. 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

Important

Always use the latest version of SQL Server Management Studio (SSMS).

SQL Server Management Studio (SSMS) doesn't support all data definition language (DDL) options in Azure Synapse. Use T-SQL scripts instead.

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 list. Data type is a required value, and is 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 ;

Related content