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为自增起始值，m为自增数量，可以实现(n,n+m,n+2m,n+3m..)这样的数据。2、@@identity系统函数：该用处在执行阶段，用于捕获最近一次插入数据所产生的自增号。在应用程序中非常游泳，比如新增一个新数据，然后获取该id，接着用于查询显示。3、IDENT_CURRENT('数据表名')：可以找出指定表的目前最大自增号，可以取代SELECT MAX语句，加快查询。特别是在大并发的时候，如果用SELECT MAX可能会出现获取不正确的序号，而且当表非常大的时候SELECT MAX也是需要很长时间的。4、SCOPE_IDENTITY()函数：在存储过程、触发器执行过程中的自增加号数。但和@@identity有些不同，@@identity返回的是整个事务中的目前值，而本函数仅返回该存储过程、触发器程序中的新增数据表的号码。@@identity在一个事务有延伸或调用另外一个表的INDENTITY属性是，会产生差异，而本函数主要用于处理这种问题。下面展示@@identity和SCOPE_IDENTITY()的差异：<textarea readonly name="code" alt="T-SQL开发——ID处置篇"><br>执行脚本后看到数据的日期是一样的，但是en列不一样，而这种效果是identity做不到的。<br>3、使用NEWID()搭配UniqueIdentifier数据产生全球唯一标识码：该值通过随机搭配多种配置信息，产生全球性的唯一识别码。以下是一个示例代码：<textarea readonly name="code" alt="T-SQL开发——ID处置篇"><br><br>从图上可以看出，NEWSEQUENTIALID()会产生一个有次序的GUID值（观察值的第一部分），这样可以在做比较时起作用。而NEWID()则为没有次序的值。注意事项：1、使用Identity作为行的标识时，无法结合事务的使用保留下一个使用的号码。即当事务发生Rollback时，依然会出去一个号码，而不会释放，会造成跳号现象。2、使用Truncate可以重置IDENTITY最后识别的值。而DELETE计算全部删除数据，下一行数据依旧会从原有的上一笔开始，不会重新开始。3、使用Timestamp类型时，仅适合那些不会UPDATE操作的数据。因为会更新timestamp值。<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>说明：如果要同时具备有自动产生序号或类似存储过程中定制复杂序号的功能，可以使用新增情况下的INSTEAD OF触发器，因为它能取代新增动作，由自己的特殊定义来改变INSERT的操作方式。但是如果INSTEAD OF之后没有出现INSERT /UPDATE/DELETE这样的语句，则触发器就会无效。<br><br>解决方法：以下代码使用INSTEAD OF触发器，实现批量新增，并根据每一天的订单总数，从000001开始编号。格式为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([分割子句]<排序子句>)使用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 class="aged2"><script type="text/javascript">a("article_content_down");</script></div> </div> <div id="pagebox" > <a href="/jiaocheng/SQL/" target="_blank">查看更多</a> <A href="/jiaocheng/20131213/1837992.html" title="一个标题涉及到的50个Sql语句">下一篇</A></div> <div id="MainLeftll"> <div id="MainLeftllToolbar">本文网址：<a href="https://www.reader8.com/jiaocheng/20120624/1837993.html">https://www.reader8.com/jiaocheng/20120624/1837993.html</a></div> <div class="related"><span class="relatedIco1">读书人精选</span> <ul class="relatedList"> </ul> </div> <div class="aged3"><script type="text/javascript">a("article_content_down2");</script></div> <div class="aged3a"><script type="text/javascript">a("article_content_down3");</script></div> </div> </div> <!-- 文章正文左边结束 --> <!-- 文章正文右边开始 --> <div class="leftbox_r"> <div class="aged4"><script type="text/javascript">a("article_right_up");</script></div> <div class="Rtitle"> <span class="Rtitle_text">热点排行</span></div> <div class="RContl"> <ul> <li><a href="/jiaocheng/20140126/1981985.html" target="_blank">索引超出了数组界限,该如何处理</a></li> <li><a href="/jiaocheng/20120810/1822364.html" target="_blank">sql查询拼凑</a></li> <li><a href="/jiaocheng/20120829/1825715.html" target="_blank">pl sql developer应用</a></li> <li><a href="/jiaocheng/20121013/1825293.html" target="_blank">SQL按分隔字符串把字符串分解成列表方式</a></li> <li><a href="/jiaocheng/20120304/1892156.html" target="_blank">安装2005之后如何设置本地数据库</a></li> <li><a href="/jiaocheng/20120919/1826070.html" target="_blank">SQLite 的使用实例</a></li> <li><a href="/jiaocheng/20120907/1827442.html" target="_blank">国家组织机构两位字母缩写跟汉字对照表 </a></li> <li><a href="/jiaocheng/20120115/1998171.html" target="_blank">使用 sqlserver的BACKUP语句备份 和使用</a></li> <li><a href="/jiaocheng/20120720/1830960.html" target="_blank">《Pro Oracle SQL》 读书笔记-Chapter 6</a></li> <li><a href="/jiaocheng/20121008/1833678.html" target="_blank">hibernate实施原始sql</a></li> </ul> </div> <div class="aged7"><script type="text/javascript">a("article_right_up2");</script></div> <!-- 文章正文右边结束 --> </div> <!--主体结束--> <script type="text/javascript">a("article_down_up");</script> <script type="text/javascript">a("foot");</script> <script type="text/javascript">a("article_down_up2");</script> <!--主体结束--> <div style="display:none"> <!--百度统计代码--> <script> var _hmt = _hmt || []; (function() { var hm = document.createElement("script"); hm.src = "https://hm.baidu.com/hm.js?d6151f323a57d16696165e47eee86d53"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); </script> </div> <script type="text/javascript">a("allbt");</script> </body> </html>