How to create a table with an identity column

Question

I have an existing table that I am going to blow off, because I did not create it with the ID column set as the Identity column of the table.

Using SQL Server Management Studio, I wrote the Create script for ... an existing table and got the following:

 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 do I change this SQL so that the ID column is set to Identity in my resulting table?

Answer 1

 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] 

Answer 2

This has already been answered, but I think the simplest syntax is:

 CREATE TABLE History ( ID int primary key IDENTITY(1,1) NOT NULL, . . . 

A more complex constraint index is useful when you really want to change parameters.

By the way, I prefer to call such a column HistoryId, so it matches the column names in foreign key relationships.

Answer 3

 [id] [int] IDENTITY(1,1) NOT NULL, 

Of course, since you are creating a table in SQL Server Management Studio, you can use the table designer to set the Identity Specification.

Answer 4

The unique key allows a maximum value of 2 NULL. Explaination:

 create table teppp ( id int identity(1,1) primary key, name varchar(10 )unique, addresss varchar(10) ) insert into teppp ( name,addresss) values ('','address1') insert into teppp ( name,addresss) values ('NULL','address2') insert into teppp ( addresss) values ('address3') select * from teppp null string , address1 NULL,address2 NULL,address3 

If you try to insert the same values ​​as below:

 insert into teppp ( name,addresss) values ('','address4') insert into teppp ( name,addresss) values ('NULL','address5') insert into teppp ( addresss) values ('address6') 

Every time you get an error, for example:

Violation of the UNIQUE KEY 'UQ__teppp__72E12F1B2E1BDC42' restriction. Cannot insert duplicate key into object "dbo.teppp".
Application completed.