T-SQL开发——ID处置篇

T-SQL开发——ID处理篇
数据库自增ID功能中Identity、Timestamp、Uniqueidentifier的区别：问题现象：

一般序号的产生，对于一般程序员而言，都是使用T-SQL命令来实现。先读取表中的最大需要，然后累加一，再插回数据库，这样做是相当危险的。因为如果事务机制没有处理好，就会出现同时间内取得同一序号。结果可想而知。为了避免这种情况，SQLServer在内部已经提供了一定的机制来协助处理。

说明：在SQLServer中，支持多种自动产生序号的机制。第一种是根据数据的插入自动生成序号用于识别每个数据行。称为【Identity】。作用在同一个表层面。第二种是作用在数据库层面，叫做timestamp数据类型，称为rowversion。通过这个，可以让相同数据库中不同数据列产生唯一识别码。第三种是似乎用NEWID()或NEWSEQUENTIALID()产生Uniqueidentifier的数据类型。这个类型是全球级别的唯一识别码。号称3000年内不会重复。合理使用上面三钟方式，能减轻应用程序的负担。
下面对每种情况做详细讲解：1、数据表级别识别——Identity：这种识别方式只适合在表级别。使用时只需要在insert语句中搭配即可，不用指定该列的名称。另外，它会自动增加，比如在DELETE语句中删除某行，后续的数据仍然会从最近的一行序号中自加。而不会从原始的定义起始开始重新自增。以下给出一个例子：

<br><br>针对Identity，还有一些使用技巧：1、Identity(n,m)：n为自增起始&#20540;，m为自增数量，可以实现(n,n&#43;m,n&#43;2m,n&#43;3m..)这样的数据。2、@@identity系统函数：该用处在执行阶段，用于捕获最近一次插入数据所产生的自增号。在应用程序中非常游泳，比如新增一个新数据，然后获取该id，接着用于查询显示。3、IDENT_CURRENT('数据表名')：可以找出指定表的目前最大自增号，可以取代SELECT MAX语句，加快查询。特别是在大并发的时候，如果用SELECT MAX可能会出现获取不正确的序号，而且当表非常大的时候SELECT MAX也是需要很长时间的。4、SCOPE_IDENTITY()函数：在存储过程、触发器执行过程中的自增加号数。但和@@identity有些不同，@@identity返回的是整个事务中的目前&#20540;，而本函数仅返回该存储过程、触发器程序中的新增数据表的号码。@@identity在一个事务有延伸或调用另外一个表的INDENTITY属性是，会产生差异，而本函数主要用于处理这种问题。下面展示@@identity和SCOPE_IDENTITY()的差异：<textarea readonly name="code" alt="T-SQL开发——ID处置篇"><br>执行脚本后看到数据的日期是一样的，但是en列不一样，而这种效果是identity做不到的。<br>3、使用NEWID()搭配UniqueIdentifier数据产生全球唯一标识码：该&#20540;通过随机搭配多种配置信息，产生全球性的唯一识别码。以下是一个示例代码：<textarea readonly name="code" alt="T-SQL开发——ID处置篇"><br><br>从图上可以看出，NEWSEQUENTIALID()会产生一个有次序的GUID&#20540;（观察&#20540;的第一部分），这样可以在做比较时起作用。而NEWID()则为没有次序的&#20540;。注意事项：1、使用Identity作为行的标识时，无法结合事务的使用保留下一个使用的号码。即当事务发生Rollback时，依然会出去一个号码，而不会释放，会造成跳号现象。2、使用Truncate可以重置IDENTITY最后识别的&#20540;。而DELETE计算全部删除数据，下一行数据依旧会从原有的上一笔开始，不会重新开始。3、使用Timestamp类型时，仅适合那些不会UPDATE操作的数据。因为会更新timestamp&#20540;。<br>通过存储过程实现定制化产生序号方式：问题现象：在很多情况下，由于使用需要，往往不能仅靠上面提到的3中方式产生序号。而要组合成一些有意义的号码。但是这种情况就难以保证数据在插入数据库的时候不重复。<br>说明：这种情况在多人调用程序时就容易出现。可以从前端应用程序着手，也可以从数据库开发一些功能来统一产生序号。无论哪种方式，都要做到以下3点才算解决了问题：1、给号的过程中，据对不能发生重复。2、给号速度越短越好。3、有些应用程序要求，全部给出去的序号。不能有跳号的情况。在这种情况下，建议混合使用前后端程序来保证，当使用存储过程年时，建议采用OUTPUT参数进行序号的释放。避免使用数据集的方式回传，因为使用OUTPUT参数输出，可以减少资源使用，加快运行的速度。另外搭配数据库的SET XACT_ABORT ON 选项，及BEGIN TRANSACTION /COMMIT TRANSACTION表达式，保证每次产生的序号过程不会发生事务过程中的Lost Updae。下面是一些示例代码：<textarea readonly name="code" alt="T-SQL开发——ID处置篇"><br>没有重复和跳号的数据。<br>通过INSTEAD OF 触发器，实现定制化序号：问题现象：在需要同时支援大批量数据插入时，也具备有产生独立专用序号等功能。<br>说明：如果要同时具备有自动产生序号或类&#20284;存储过程中定制复杂序号的功能，可以使用新增情况下的INSTEAD OF触发器，因为它能取代新增动作，由自己的特殊定义来改变INSERT的操作方式。但是如果INSTEAD OF之后没有出现INSERT /UPDATE/DELETE这样的语句，则触发器就会无效。<br><br>解决方法：以下代码使用INSTEAD OF触发器，实现批量新增，并根据每一天的订单总数，从000001开始编号。&#26684;式为YYYYMMDD.NNNNNN。<br><textarea readonly name="code" alt="T-SQL开发——ID处置篇"><br><br>注意事项:1、INSTEAD OF 触发器执行时机,会在条件约束Primary key之前.2、执行过程，可以用INSERTED记录新增的数据后者修改后的数据，使得DELTE记录删除的数据或修改前的数据。3、在定义过程中避免使用Cursor，可以直接使用INSERTED或者DELETED来获取数据。实现循环效果。<br><br>在前端应用程序输出时自动加上序号：问题：在前端应用程序展现数据时，希望能自动加上序号。解决方法：可以使用ROW_NUMBER()函数，使用方式：ROW_NUMBER() OVER([分割子句]&lt;排序子句&gt;)使用ROW_NUMBER()解决自动产生序号的时候，需要指定哪个数据航排序。<textarea readonly name="code" alt="T-SQL开发——ID处置篇"><br><br>注意事项:ROW_NUMBER()函数的ORDER BY 和SELECT 的ORDER BY 不一致时会影响输入结果<dl class="comment_item comment_topic"><dt class="comment_head">3楼TravyLee昨天 21:45<dd class="comment_body">要说这个是自增UniqueIdentifier有点不妥</dl><dl class="comment_item comment_topic"><dt class="comment_head">2楼xqf309昨天 19:26<dd class="comment_body">求教一个问题：每次对数据表进行删除操作后，ID就间断了，我想对其进行操作后让ID从新排序好，请问有什么良策吗？<dl class="comment_item comment_topic"><dt class="comment_head">Re: DBA_Huangzj昨天 21:37<dd class="comment_body">回复xqf309n重新排序是指从1开始吗？如果序号不是非常重要的话，可以使用SET IDENTITY_INSERT 的配置，先取消掉表的自增，然后重建自增序号，如果ID非常重要，那建议不要更改。</dl></dl><dl class="comment_item comment_topic"><dt class="comment_head">1楼DBA_Huangzj昨天 19:15<dd class="comment_body">因为也不知道把UniqueIdentifier放到那个地方比较合适，而且用法都相似，所以就放到一起算了</dl> </p></div><div style="margin:0 auto;width:300px;height:250px;position:relative;">
<script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
<!-- 移动端全站正文 -->
<ins class="adsbygoogle"
style="display:block"
data-ad-client="ca-pub-6147101717324856"
data-ad-slot="1958144088"
data-ad-format="auto"></ins>
<script>
(adsbygoogle = window.adsbygoogle || []).push({});
</script></div>
<div class="subn-nav"><a href="//m.reader8.net">读书人网</a> ><a href="//m.reader8.net/jiaocheng/SQL/">SQL Server</a></div><div class="sp-jctu"><h3><span> 热点推荐 </span></h3><ul><li><p><a href="//m.reader8.net/jiaocheng/20140611/2380889.html">批量删除每个数据库中 某表的某列,该怎</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140611/2380888.html">求sql 算法,该如何解决</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140610/2376998.html">求一条查询语句！该如何解决</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140610/2376997.html">请问SQL批量修改数据库语句</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140609/2373360.html">联合查询更新求指教,该怎么解决</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140609/2373359.html">sql服务无法启动,该怎么处理</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140608/2370004.html">下井次数计算sql解决方法</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140608/2370003.html">SQL SEVER 2008安装后出现的2个范例的</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140607/2366398.html">【题外话】与数据库有些相关关于Had</a></p></li>
<li><p><a href="//m.reader8.net/jiaocheng/20140607/2366397.html">解一下子型的度</a></p></li>
</ul></div><div style="display:none;">
<script>
var _hmt = _hmt || [];
(function() {
  var hm = document.createElement("script");
  hm.src = "//hm.baidu.com/hm.js?d6151f323a57d16696165e47eee86d53";
  var s = document.getElementsByTagName("script")[0]; 
  s.parentNode.insertBefore(hm, s);
})();
</script></div>
<div class="footer"><p>触屏版 | <a href="//www.reader8.net/jiaocheng/20120624/1837993.html#fromapp">电脑版</a></p><p>读书人网 m.reader8.net</p></div>
<script language="javascript">
function clickme(sad){
var divobjdiv = document.getElementById("divobjdiv" + sad);
if(divobjdiv.style.display =="none"){
divobjdiv.style.display ="block"; 
}else{
divobjdiv.style.display ="none";
}
var wenzi = document.getElementById('divobjimg' + sad);
if(wenzi.innerText == "收起"){
wenzi.innerText = "查看更多";
}else{
    wenzi.innerText = "收起";
  }
}
</script>
<div id="back-top"><a href="#top"><span></span></a></div>
</body>
</html>

T-SQL开发ID处置篇