1.ms_sql查询下一个标识

use test002

go

--1.查找并删除已经存在的数据

 if object_id('table_6_2','U') is not null drop table table_6_2

go

--2.创建数据表并插入数据

 create table table_6_2(id_num int identity(1,1),company_name sysname)

insert table_6_2(company_name)values('test_1')

 insert table_6_2(company_name)values('test_2')

insert table_6_2(company_name)values('test_3')

insert table_6_2(company_name)values('test_4')

go --3.查看数据表的数据 select *from table_6_2

go

--4.删除一行

delete from table_6_2 where id_num=2

--5.从新插卡看数据

select *from table_6_2

go

--6.查找最小标号

 set identity_insert  table_6_2 on

 declare @minidentval smallint

 declare @nextidentval smallint

select @minidentval=min($identity) from table_6_2

 if @minidentval=ident_seed('table_6_2')  

select @nextidentval=  

min($identity)+ident_incr('table_6_2')from table_6_2 t1  

 where ($identity between ident_seed('table_6_2') and 32766)  

 and(not exists(select * from table_6_2 t2 where t2.$identity=t1.$identity+ident_incr('table_6_2')))

else select @nextidentval=ident_seed('table_6_2')

 select @nextidentval

set identity_insert table_6_2 off