How To Create Table with Identity Column

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 ?

---

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]

---

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.

---

[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.

链接地址: http://www.djcxy.com/p/94660.html

上一篇: SQL表更新加入

下一篇: 如何使用标识列创建表