创建一个将IDENTITY列作为主键的表。IDENTITY关键字放在可空选项之后,但在PRIMARY KEY关键字之前:
USE AdventureWorks
GO
CREATE TABLE HumanResources.CompanyAuditHistory
(
CompanyAuditHistory int NOT NULL IDENTITY(1,1) PRIMARY KEY,
CompanyID int NOT NULL,
AuditReasonDESC varchar(50) NOT NULL,
AuditDT datetime NOT NULL DEFAULT GETDATE()
)
INSERT HumanResources.CompanyAuditHistory
(CompanyID,AuditReasonDESC,AuditDT)
VALUES
(1,'Bad 1099 numbers','6/1/2009')
INSERT HumanResources.CompanyAuditHistory
(CompanyID,AuditReasonDESC,AuditDT)
VALUES
(1,'Miss financial statement','7/1/2009')
SELECT CompanyAuditHistory,AuditReasonDESC
FROM HumanResources.CompanyAuditHistory
查询结果
CompanyAuditHistory AuditReasonDESC
1 Bad 1099 numbers
2 Miss financial statement
创建好表后,向表中插入了两行,但没有显式插入CompanyAuditHistory列的值。在查询表后,可以看到这两行都根据IDENTITY属性被自动分配了值,由种子的值1开始,每个新行增加1.