笔记246 在SQLServer中,支持多种自动产生序号的机制 master黄2013-4-18

笔记246 在SQLServer中,支持多种自动产生序号的机制  黄2013-4-18

  1 --在SQLServer中,支持多种自动产生序号的机制  master黄2013-4-18
--http://blog.csdn.net/dba_huangzj/article/details/7685162
2 3 --(1)第一种是根据数据的插入自动生成序号用于识别每个数据行。称为【Identity】。作用在同一个表层面 4 5 -- 6 7 --(2)第二种是作用在数据库层面,叫做timestamp数据类型,称为rowversion。通过这个,可以让相同数据库中不同数据列产生唯一识别码。 8 9 -- 10 11 --(3)第三种是似乎用NEWID()或NEWSEQUENTIALID()产生Uniqueidentifier的数据类型。这个类型是全球级别的唯一识别码。号称3000年内不会重复。 12 13 -- 14 15 -- 16 17 --合理使用上面三钟方式,能减轻应用程序的负担。 18 19 ----------------------------------------------------------------- 20 21 --1、数据表级别识别——Identity: 22 23 --这种识别方式只适合在表级别。使用时只需要在insert语句中搭配即可,不用指定该列的名称。另外,它会自动增加,比如在DELETE语句中删除某行,后续的数据仍然会从最近的一行序号中自加。而不会从原始的定义起始开始重新自增。以下给出一个例子: 24 25 26 27 use tempdb 28 go 29 --创建测试用的数据表 30 create table Employee 31 ( 32 en int not null IDENTITY PRIMARY KEY, --自增ID 主键PRIMARY KEY 33 ename varchar(50), --员工名称 34 keyDT datetime --创建日期 35 ) 36 37 --DROP TABLE [dbo].[Employee] 38 39 40 --插入数据,不指定列名 41 insert into Employee 42 values('Lewis','2012/6/23'); 43 44 --插入数据,指定列名,但不指定自增列 45 insert into Employee(ename,keyDT) 46 values('Ada','2012/6/24') 47 go 48 select * from Employee 49 50 ----------------------------------------------------- 51 --针对Identity,还有一些使用技巧: 52 53 --1、Identity(n,m):n为自增起始值,m为自增数量,可以实现(n,n+m,n+2m,n+3m..)这样的数据。 54 55 --2、@@identity系统函数:该用处在执行阶段,用于捕获最近一次插入数据所产生的自增号。在应用程序中非常有用,比如新增一个新数据,然后获取该id,接着用于查询显示。 56 57 --3、IDENT_CURRENT('数据表名'):可以找出指定表的目前最大自增号,可以取代SELECT MAX语句,加快查询。特别是在大并发的时候,如果用SELECT MAX可能会出现获取不正确的序号,而且当表非常大的时候SELECT MAX也是需要很长时间的。 58 59 --4、SCOPE_IDENTITY()函数:在存储过程、触发器执行过程中的自增加号数。但和@@identity有些不同,@@identity返回的是整个事务中的目前值,而本函数仅返回该存储过程、触发器程序中的新增数据表的号码。@@identity在一个事务有延伸或调用另外一个表的INDENTITY属性时,会产生差异,而本函数主要用于处理这种问题。 60 61 --------------------------------------------------------------- 62 63 --2、数据库级别标识——timestamp : 64 65 --这个功能主要使用数据库的计数器产生的时间戳,产生每个数据的识别。这种数据的属性是timestamp,也称为rowversion。为指定数据库的任何数据表产生唯一的戳值。戳值就是一种二进制数据类型,长度等于varbinary(8)。另外,这种类型还会根据后续针对这行数据的修改,改变原有timestamp值。由于它的动态性,在选作索引值时要评估。 66 67 --该值可以使用@@DBTS系统函数来获取。 68 69 --以下是示例代码: 70 71 use tempdb 72 go 73 --创建南方员工的数据表 74 CREATE TABLE Employee_S 75 ( 76 en timestamp not null,--自增二进制ID 77 ename varchar(50),--员工名 78 keyDT datetime --创建时间 79 ) 80 81 --创建中部员工的数据表 82 CREATE TABLE Employee_C 83 ( 84 en timestamp not null,--自增二进制ID 85 ename varchar(50),--员工名 86 keyDT datetime --创建时间 87 ) 88 89 --创建北方员工的数据表 90 CREATE TABLE Employee_N 91 ( 92 en timestamp not null,--自增二进制ID 93 ename varchar(50),--员工名 94 keyDT datetime --创建时间 95 ) 96 97 --插入数据: 98 insert into Employee_S(ename,keyDT) values('Sname',GETDATE()) 99 insert into Employee_C(ename,keyDT) values('Cname',GETDATE()) 100 insert into Employee_N(ename,keyDT) values('Yname',GETDATE()) 101 --显示数据 102 select '南方',* from Employee_S 103 union all 104 select '中部',* from Employee_C 105 union all 106 select '北方',* from Employee_N 107 108 --执行脚本后看到数据的日期是一样的,但是en列不一样,而这种效果是identity做不到的 109 --timestamp在多个表之间保持唯一 110 111 ------------------------------------------------------------------- 112 --3、使用NEWID()搭配UniqueIdentifier数据产生全球唯一标识码: 113 114 --该值通过随机搭配多种配置信息,产生全球性的唯一识别码。以下是一个示例代码 115 116 use tempdb 117 go 118 --创建南方员工的数据表 119 CREATE TABLE Employee_GUID 120 ( 121 en uniqueidentifier not null,--自增二进制ID 122 ename varchar(50)--员工名 123 124 ) 125 126 --插入数据: 127 insert into Employee_GUID(en,ename) values(newid(),'Sname') 128 insert into Employee_GUID(en,ename) values(newid(),'Cname') 129 insert into Employee_GUID(en,ename) values(newid(),'Nname') 130 131 132 --显示数据,为了证明不唯一,可以使用GROUP BY来检验: 133 --源数据 134 select * 135 from Employee_GUID 136 --检验数据 137 select count(1) 'Total',en 138 from Employee_GUID 139 group by en 140 having count(1)>1 141 142 143 --另外,在前面提到过,可以使用NEWID()和NEWSEQUENTIALID()产生, 考虑NEWID()和NEWSEQUENTIALID()两者在使用上的区别: 144 use tempdb 145 go 146 --产生NEWID()和NEWSEQUENTIALID(): 147 SET NOCOUNT ON 148 DECLARE @T TABLE (newSN uniqueidentifier,seqSN uniqueidentifier default (NEWSEQUENTIALID())) 149 DECLARE @I INT 150 SET @I=1 151 WHILE @I<=10 152 BEGIN 153 INSERT INTO @T VALUES(NEWID(),DEFAULT) 154 SET @I=@I+1 155 END 156 157 SELECT * FROM @T 158 SET NOCOUNT OFF 159 160 --执行后可以看到下图:注意每台机器值会不一样 161 --从图上可以看出,NEWSEQUENTIALID()会产生一个有次序的GUID值(观察值的第一部分),这样可以在做比较时起作用。而NEWID()则为没有次序的值。 162 163 164 --注意事项: 165 166 --1、使用Identity作为行的标识时,无法结合事务的使用保留下一个使用的号码。即当事务发生Rollback时,依然会出去一个号码,而不会释放,会造成跳号现象。 167 168 --2、使用Truncate会重置IDENTITY最后识别的值。而DELETE全部删除数据,但下一行数据依旧会从原有的上一笔开始,不会重新开始。 169 170 --3、使用Timestamp类型时,仅适合那些不会UPDATE操作的数据。因为会更新timestamp值。 171 172 173 174 175 176 --博客园里的开辟数据同步软件 177 178 --http://www.cnblogs.com/iyangyuan/archive/2013/03/05/2943660.html 179 180 --程序使用注意事项 181 182 --1.必须确保您的数据库没有修改主键的操作。由于修改主键会引起很多问题,如果一定要修改主键,建议把修改拆分成删除→添加。 183 184 -- 185 186 --2.必须确保您的数据库没有使用自增长的字段,因为自增长字段本程序无法干涉。 187 188 -- 189 190 --3.支持所有SQL数据类型(除SQL2008新增的geography、geometry、hierarchyid)。 191 192 -- 193 194 --4.数据库连接字符串一旦成功设置,不能更改!如需更改,需要重新安装程序。

 

posted @ 2013-08-04 20:05 桦仔 阅读(...) 评论(...)  编辑 收藏