（3.9）常用知识-标识值（identity）的不连续与强行插入、计算列

参考：深入了解identity

参考：深入了解计算列

概念：标识值 identity(begin,add_number) 是一种特殊的值，依赖于列，由sql server自动维护，是自增的，而且一般是不会重复的。但是sql server并不维护标识（identity）值的唯一（要保证其唯一需在使用列上添加主键或唯一约束），也不维护标识值的连续。

0、查看表是否有标识列

 select * from db_tank.sys.columns 
 where object_id=object_id('sys_users_detail') 
 AND is_identity=1

1、标识值不连续

（1）当事务回滚的时候

--构建测试表
create table test_1(id int identity(1,1)  ,col int)
GO
--构建测试数据
insert into test_1(col) values(101),(102),(103)
--事务回滚
begin tran a
    insert into test_1(col) values(104),(105),(106)
rollback tran a
--再次插入
insert into test_1(col) values(107),(108),(109)
--验证
select * from test_1


结果如下：

　　

（2）删除记录

　　　　无论被删除的标识值是最新添加的还是以前添加的，标识值均不会再回收利用，这里就不演示了。

（3）使用dbcc checkident重置标识值

USE tempdb;
go
--构建测试表
IF OBJECT_ID('test101')  IS NOT NULL
DROP TABLE test101

CREATE TABLE test101 (
id INT IDENTITY(1000,1), --起始从1000开始，补偿为1
num int 
)
INSERT INTO test101 VALUES(100)

--重置当前 identity增长值为 20000
dbcc checkident(test101,reseed,20000)
INSERT INTO test101 VALUES(1)

--重置后，新插入的变成20001了
SELECT * FROM test101

　　

2、identity强行插入

　　由于sql server不维护标识值的唯一，因此在没有主键、唯一约束等情况下，标识值可能会重复。

（1）使用 set identity_insert tableNmae on 强行插入标识值 （注意，用这种办法插入时，一定要写列名 而不是*）

　　　　

（2）使用dbcc checkident重置标识~~1的（3）中已经描述过操作，这里不再赘述

3、在select into 中构造identity列

select 1 as id,2 as num,identity(int,1,1) rn into #temp1 

　　

4、删除列的identity属性

--如何用sql语句去掉列的自增长(identity) 

--**无法通过alter把现有自增字段改为非自增 
--比如alter   table   a   alter   id   int,自增属性不会去掉 
--通过修改系统表可以做到(此法可能有不可预知的结果，慎之...) 

sp_configure   'allow   updates ',   1 
GO 
reconfigure   with   override 
GO 
update   syscolumns   set   colstat   =   colstat   &   0x0000   
where     id=object_id( '表名 ')   and   name= '字段名 ' 
GO 
sp_configure   'allow   updates ',   0 

--------------------------------------------- 
--折中的办法 
alter   table   a   add   xxx   int 
update   a   set   xxx=id 
alter   table   a   drop   column   id 
exec   sp_rename   'xxx ',   'id ',   'column ' 

 

 

5、计算列

　　

CREATE TABLE #t
( 
x INT ,
y INT ,
z AS x+y
)

INSERT INTO #T VALUES(1,1);
INSERT INTO #T VALUES(2,1);
INSERT INTO #T VALUES(1,3);
INSERT INTO #T VALUES(4,4);
INSERT INTO #T VALUES(12,11);

SELECT * FROM #T

DROP TABLE #t

 

来一个比较实际的需求：弄一个日期加两位数流水号的自动编号

CREATE TABLE #t
(
id INT IDENTITY(1,1) ,
aa DATETIME DEFAULT GETDATE(),
new_id AS CONVERT(VARCHAR(20),GETDATE(),112)+'_'+RIGHT('0000000'+CAST(id AS VARCHAR(200)),3),
x INT
)

INSERT INTO #t (X) VALUES(1);
INSERT INTO #t (X) VALUES(1);
INSERT INTO #t (X) VALUES(1);
INSERT INTO #t (X) VALUES(1);

SELECT * FROM #T

DROP TABLE #T