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

1 --在SQLServer中，支持多种自动产生序号的机制  master黄2013-4-18
--
  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 --
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.数据库连接字符串一旦成功设置，不能更改！如需更改，需要重新安装程序。