How To Create Table with Identity Column

sql, sql-server, t-sql

Solution

CREATE TABLE [dbo].[History](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [RequestID] [int] NOT NULL,
    [EmployeeID] [varchar](50) NOT NULL,
    [DateStamp] [datetime] NOT NULL,
 CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
) ON [PRIMARY]

Problem

I have an existing table that I am about to blow away because I did not create it with the `ID` column set to be the table's Identity column. Using SQL Server Management Studio, I scripted a "Create To..." of the existing table and got this: ``` CREATE TABLE [dbo].[History]( [ID] [int] NOT NULL, [RequestID] [int] NOT NULL, [EmployeeID] [varchar](50) NOT NULL, [DateStamp] [datetime] NOT NULL, CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ``` My question is, how would I modify this `SQL` so that my resulting table has the `ID` column set as the Identity?

Original source