（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重置标识值

　　　　

--现有数据
select * from test_1
--重置标识值
dbcc checkident(test_1,reseed,1)
--插入测试数据
insert into test_1(col) values(110),(111),(112)
--验证标识值
select * from test_1

结果如下：

现有数据~　　　　　　　　　　　　　　验证标识值数据

　　　　　　　　　

 

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.计算列

　　

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

 

　　

 

 

转载于:https://www.cnblogs.com/gered/p/9146616.html