我建了1个新表,其中有一列是这么定义的:ID int identity(1,1) 这样的话 id列就能自动递增了。 但是当我删除一行,例如第7行,然后紧接着插入一行时,id列显示的并不是7,而是8, 这是为什么啊? 有什么办法可以让新添列的ID不间断??代码如下:
create table t2(ID int identity(1,1),MtlID varchar(10),StkID varchar(10),TimeID datetime,Weight float)insert into t2(MtlID,StkID,TimeID,Weight)
select MtlID,StkID,TimeID,Weight --插入内容
from t1
delete from t2 where Weight is nullinsert into t2(MtlID,StkID,TimeID,Weight)
select MtlID,StkID,TimeID,Weight -- 重新插入删除行,这时就显示为8 而不是7了
from t1
where id=7
create table t2(ID int identity(1,1),MtlID varchar(10),StkID varchar(10),TimeID datetime,Weight float)insert into t2(MtlID,StkID,TimeID,Weight)
select MtlID,StkID,TimeID,Weight --插入内容
from t1
delete from t2 where Weight is nullinsert into t2(MtlID,StkID,TimeID,Weight)
select MtlID,StkID,TimeID,Weight -- 重新插入删除行,这时就显示为8 而不是7了
from t1
where id=7
create view vtb_2000 as select new_id=(select count(1) from tb where id<=t.id), * from tb as t
go--2005
create view vtb_2005 as select new_id=row_number()over(order by id), * from tb as t
go
1.打开identity的开关,语句set identity_insert t2 on
这时可以在ID列插入你要的值
2.在删除某列之前取出ID的最大值,然后删该列,再用dbcc checkident('t2',reseed,取出的ID值),这时下次插入记录时,ID是连续的
dbcc checkident('表名', RESEED, 100)
alter table t2 add ID int identity(1,1)[/code]
alter table t2 add ID int identity(1,1)
place_id smallint identity(1,1),
place_name varchar(50),
primary key(place_id))create trigger trig_place
on place
instead of insert
as
declare
@place_id smallint,
@place_name varchar(50),
@min smallint
select @min=min(identitycol)
from place p1
where identitycol between ident_seed('place') and 32766
and not exists(select *
from place p2
where p2.identitycol=p1.identitycol+ident_incr('place'))
select @place_id=max(place_id) from place
select @place_name=place_name from inserted
if @place_id<>@min
begin
set @place_id=@min
end
if @place_id is null
begin
set @place_id=0;
end
set identity_insert place on
insert into place(place_id,place_name) values(@place_id+1,@place_name)
set identity_insert place off
go
Identity列如果不连续也没有必要,添加比较麻烦
同意当然如果你非要连续的话 可以写个小语句先导出数据重置递增列后再导回来INSERT t2bak SELECT * from t2 order by ID
TRUNCATE TABLE T_code
INSERT t2 SELECT * from t2bak order by ID
TRUNCATE TABLE t2 --SORRY 表名搞错鸟
INSERT t2 SELECT * from t2bak order by ID