Alter Table Add Column

Adding a column to a table in SQL Server is done using the ALTER TABLE tablename ADD command. When adding columns you can specify all the same settings available when creating a table.

In the example below, we will create a small sample table, then add columns using the ALTER TABLE command.

Multiple columns can be specificied by using the syntax below. First issue an alter table command then add the column name, data type, nullable, and default value.

[cc lang=”sql”]

IF OBJECT_ID(‘tempdb..#employees’) IS NOT NULL
BEGIN
DROP TABLE #employees
END

CREATE TABLE #employees
(
EmployeeID int IDENTITY (1,1) CONSTRAINT PK_employee NOT NULL PRIMARY KEY CLUSTERED
,FirstName VARCHAR(50)
)
GO

INSERT INTO #employees (FirstName) VALUES (‘John’);

ALTER TABLE #employees
ADD
LastName varchar(50) NULL
,SSN varchar(9) NULL CONSTRAINT ssn_unique UNIQUE
,IsTerminated bit NOT NULL DEFAULT 0
,DateAdded datetime NOT NULL DEFAULT GETDATE()
,DateModified DATETIME NULL DEFAULT GETDATE()
,Comments varchar(255) SPARSE NULL — SQL Server 2008 sparse column

SELECT * FROM #employees
[/cc]

When adding a column to an existing table the ordinal position will always be appended to the end

Also note, when you add columns with a default value, the value will only get populated when you specify the column as NOT NULL. Otherwise, NULL values will populate the column initially. However once the column is created, subsequent insert statements will populate the default value. In the above example, DateAdded is initially populated whereas DateModified is only populated after a row is inserted.

Please help improve this content! Please write a comment below about what’s missing!

5 comments
ВалокСочи 13 Feb 2017 at 4:02 pm

RE:Alter Table Add Column Валок john deere Пучеж

Mareese Kennell 24 Oct 2016 at 5:38 am

My business partners were wanting WA TD-420-065 earlier today and were informed of an excellent service with 6 million forms . If you need to fill out WA TD-420-065 too , here’s https://goo.gl/mzfaks

Dave 05 Nov 2014 at 9:43 pm

Great info. Presented well. One caveat, what if I wanted to add a set of columns to every relative table in the database. Would you suggest a For @each in ‘systables.*’ and add the columns for the @each table point? What is your take on it. The reason I ask is I am adding audit information to each table for who added/updated it. Your example of the dates is great along with the explanation. Also didn’t know about the sparse tag. Thanks.

Sheki 29 Aug 2015 at 7:18 am

Hey, I have bought an upgadre a week ago and things are not ok, for indeed even seeming it is alright, products in home are not appearing, and also any search I try to do, it shows errors like the ones below.:[Microsoft][ODBC Microsoft Access Driver] ORDER BY clause (customerTypesPrices.price) conflicts with DISTINCT./Comersus/includes/databaseFunctions.asp, line 57=========================another search gives (seems any search gives error)(Search criteria: Category .. ordered by: no ordering specified ) Microsoft VBScript runtime error ‘800a0006 Overflow: cint’/comersus/includes/itemFunctions.asp, line 273===========================and also the backoffice is not working.are there bugs in this items functions.asp or is something wrong with my db? By the way, I have a list of almost 70000 items (as it was in the former version and never had any problem).Paulo[] Reply:December 6th, 2012 at 1:38 amYou need to upgadre your database structure to 9.10 before using 9.10 Power Pack. Upgrade DB utility is provided in Free Downloads Page.[]

najib 25 Jun 2014 at 11:09 am

ok i found this info here usefull and simple to understand thank you

Featured Articles

 Site Author

  • Thanks for visiting!
css.php