SQL Server中identity(自增)的用法详解

create table student1
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student1(sname,ssex) values ('张三','男');
insert into student1 values ('李四','女');--可以省略列名
insert into student1 values ('王五','女');

create table student2
(
 sid int primary key identity(20,5),
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student2(sname,ssex) values ('张三','男');
insert into student2 values ('李四','女');--可以省略列名
insert into student2 values ('王五','女');

create table student3
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student3(sname,ssex) values ('张三','男');
insert into student3 values ('李四','女');
delete from student3 where sid=2;--把sid为2的记录删除
insert into student3 values ('王五','女');

create table student4
(
 sid int primary key identity,
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student4(sname,ssex) values ('张三','男');
insert into student4 values ('李四','女');
delete from student4 where sid=2;--把sid为2的记录删除
dbcc checkident('student4',reseed,1);--把student4表中identity字段的初始值重新设置为1
insert into student4 values ('王五','女');

create table student5
(
 sid int primary key identity(20,5),
 sname nchar(8) not null,
 ssex nchar(1)
)
insert into student5(sname,ssex) values ('张三','男');
insert into student5 values ('李四','女');
insert into student5 values ('王五','女');
set identity_insert student5 on;
/*
insert into student5 values ('黑六','男');--error
insert into student5 values (21,'黑六','男');--error
*/
insert into student5(sid,sname,ssex) values (21,'黑六','男');
set identity_insert student5 off;
/*
insert into student5 values (22,'赵七','女');--error
insert into student5(sid,sname,ssex) values (22,'赵七','女');--error
*/
insert into student5 values ('赵七','女');

create table 表名 (  字段名1 int not null, ...,
  [constraint 约束名] primary key (字段名1, …)
)

SELECT NAME from SYS.OBJECTS WHERE TYPE_DESC ='PRIMARY_KEY_CONSTRAINT' AND
PARENT_OBJECT_ID = (SELECT OBJECT_ID
FROM SYS.OBJECTS WITH(NOLOCK) WHERE NAME = '表名')