How To Create Table with Identity Column

Question

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

User · Answer

Unique key allows max 2 NULL values  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 inserting 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 will get error like      Violation of UNIQUE KEY constraint  UQ  teppp  72E12F1B2E1BDC42   Cannot insert duplicate key in object  dbo teppp     The statement has been terminated

User · Answer

This has already been answered  but I think the simplest syntax is   CREATE TABLE History       ID int primary key IDENTITY 1 1  NOT NULL              The more complicated constraint index is useful when you actually want to change the options   By the way  I prefer to name such a column HistoryId  so it matches the names of the columns in foreign key relationships

User · Answer

id   int  IDENTITY 1 1  NOT NULL    of course since you re creating the table in SQL Server Management Studio you could use the table designer to set the Identity Specification

User · Answer

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

[sql] How To Create Table with Identity Column

Examples related to sql

Examples related to sql-server

Examples related to tsql