DB-SQLServer：开发反模式----伪键洁癖

一、目标：整理数据                                                                                                 

　　有的人有强迫症，他们会为一系列数据的断档而抓狂。

　　

　　一方面，Id为3这一行确实发生过一些事情，为什么这个查询不返回Id为3的这一行？这条记录数据丢失了吗？那个Column到底是什么？我要为这条数据的丢失负责吗?

二、反模式：填充角落                                                                                               

　　大多数人对于断档的第一反应就是想要填补其中的空缺。对此，可能有两种做法:

　　1、不按照顺序分配编号

　　你可能想要在插入新行时，通过遍历表，将找到的第一个未分配的主键编号分配给新行，来代替原来自动分配的伪主键机制。随着你不断地插入新行，断档就被填补起来了。

　　

　　然而，为了找到第一个未被使用的值，你不得不执行一次自联合查询：

SELECT TOP 1 c1.ColumnId + 1
FROM [Columns] AS c1
LEFT OUTER JOIN [Columns] AS c2 ON (c1.ColumnId + 1 = c2.ColumnId)
WHERE c2.ColumnId IS NULL
ORDER BY c1.ColumnId

　　如果用SELECT MAX(ColumnId) + 1 这种查询语句，则会出现并发访问的问题，如果有两个程序同时想要找最小的未使用值时，也会出现同样的问题，结果就是一个成功

，另一个失败。况且，这个方法既低效，也容易导致问题。

UPDATE [Columns] SET ColumnId = 3 WHERE ColumnId = 4

　　2、为现有行重新编号

　　在某些情况下，你可能急着想要让所有的主键变得连续，而等着新行来填补空缺不够快。你可能会考虑更新现有行的主键值，让其变得连续，从而消除断档。通常这样的做法是找到主键最大的行，然后用最小的未被使用的值来更新它。比如，你可以将4更新为3:

　　

　　要完成这一步，你需要使用和前面一种方法插入新行类似的方法。先找到一个未被使用的值，随后执行UPDATE语句来重新分配主键值。这两步都会引起并发访问的问题。而且，你需要重复执行很多次这样的操作，才能将较大的断档填满。

　　你必须同时更新所有引用了你重新分配了主键的行的子记录。如果你在定义外键时，使用了ON UPDATE CASCADE选项，这一步会变得很方便，但如果你没这么做，就必须先禁用约束，手动更新所有的子记录，然后恢复约束。这是一个费时费力且容易出错的操作，并且可能会影响到数据库的服务，正常人都会避免这么做。

　　即使你完成了清理操作，“整洁”也是个“短命鬼”，如果如果Id是自动生成的，那么对不起，你懂的，Id自增还是按照原来的最大数值增加，比如最大是4，你将4改成了3，但是下一个自动生成的主键依然是5。

　　3、制造数据差异

　　重用主键并不是一个好主意，因为断档往往是由于一些合理的删除或者回滚数据引起的。假设一个AccountId为789的用户由于发送带有政治问题的邮件而被封号，产品策略要求删除了这个账号，如果你重用了主键，就会在某一时间点将789分配给另一个用户。而收件人可能在删除后的某一个时间点才打开这些邮件，他们还会投诉789这个Id账号的用户。尽管这个用户本身没有做错任何事情，但是它被分配了一个需要为此负责的编号。

三、解决方案：克服心理障碍                                                                                       

　　1、定义行号

　　大多数伪键返回的数字看起来就和行号一样，因为他们就是一次递增的，但这只是由于伪键实现机制所造成的巧合而已。按照这样的方式生成的主键值能比较方便地确保唯一性。

　　别把主键值和行号混为一谈。主键是用来标识表中记录的，而行号是用来标识查询结果集中记录的。查询结果集的行号和主键值没有一丁点关系，尤其是当你使用了JOIN、GROUP BY或者ORDER BY这些操作符的时候。

　　有很多使用行号的好理由，比如返回一个查询结果集的子集。通常我们称之为分页，就像在网络搜索时的一页。要选择一个子集，你需要使用到实际的连续增长的行号，但和查询的形式无关。

　　SQL Server定义了包括ROW_NUMBER()在内的一些窗口函数，返回一个查询结果集中一段连续的行。通常使用行号的作用是将查询结果限制在一个特定的范围内。

　　

　　2、使用GUID

　　当然我们还可以生成随机伪键值，只要你不会重复使用任何数字。有些数据库提供全局唯一标识符(GUID)来达到这个目的。

　　GUID是一个128位的伪随机数(通常用32个)，由于GUID的定义及其所要实现的目的，它被设计成具有唯一，因此你可以用其作为伪键。

　　下面给出用SQL Server使用GUID作为主键的例子：

CREATE TABLE Person(
    Id UNIQUEIDENTIFIER DEFAULT NEWID(),
    Name nvarchar(50)
)

　　效果如下：

　　

　　使用GUID相对于整型自增主键来说有如下两个优势:

• 　　可以在多个数据库服务器上并发地生成伪键，而不用担心生成同样的值。
• 　　没有人会在抱怨有断档-他们会忙于抱怨输入32个十六进制字符做主键。

　　下面是使用GUID带来的不便：

• 　　GUID的值太长，不便于输入。
• 　　GUID的值是随机的，因此找不到任何规则或者依靠最大值来判断哪一行是最新插入的。
• 　　GUID的存储需要16字节，这比传统的4字节整型伪键占用更多的空间，并且查询的速度更慢。

　　SQL:UNIQUEIDENTIFIER <=> .Net GUID