• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server ALTER TABLE ADD Column

SQL Server ALTER TABLE ADD Column

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use SQL Server ALTER TABLE ADD Column statement to add one or more columns in an existing table in the SQL Server database.

Introduction to SQL Server ALTER TABLE ADD Column

In real-world scenarios, requirements may come like to add one or more columns in an existing table in SQL Server. SQL Server provides the feature to fulfill this requirement by providing the ALTER TABLE ADD Column statement by which you can easily add any new column(s) in the table.

SQL Server ALTER TABLE ADD Column Syntax

Using the below syntax, you can add a new column in an existing table in SQL Server.

ALTER TABLE table_name
  ADD 
column_name data_type column_constrint;

In this syntax,

  • table_name – The name of the table in which you want to add a new column.
  • column_name – The name of the column which you want to add to the existing table.
  • data_type – Specify the data type of the new column.
  • column_constraint – optional. You can specify the column constraint if any.

In case, you want to add more than one column at once in the tables using a single ALTER TABLE statement, you can use the following syntax.

ALTER TABLE table_name
ADD column_name_1 data_type_1 column_constrint_1,
ADD column_name_1 data_type_1 column_constrint_1,
….,
ADD column_name_n data_type_n column_constrint_n;

Please note that SQL Server does not support adding a new column after an existing specific column, unlike MySQL.

SQL Server ALTER TABLE ADD Column Examples

Now lets take few examples of SQL Server ALTER TABLE ADD Column to get more clarity.

Let’s create a table named university.students using the following statement.

CREATE TABLE university.students
(
roll_no INT IDENTITY PRIMARY KEY,
first_name nvarchar(50) NOT NULL,
last_name nvarchar(50) NOT NULL
);

To add new column name dob to the university.students table, you have to use the following statement:

ALTER TABLE university.students
ADD
dob DATE NOT NULL;

The following statement will add two columns named address and dept to the university.students table:

ALTER TABLE university.students
ADD
address varchar(250) NOT NULL,
dept varchar(50) NOT NULL;

Summary

In this tutorial, you have learned how to use SQL Server ALTER TABLE ADD Column statement to add one or more columns to a table.

Was this tutorial helpful?
YesNo
« Previous: SQL Server Index on Computed Columns
SQL Server ALTER TABLE ALTER Column :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.