SQL Server 不按预期工作的 10 件事

10. 唯一NULL

当在列上声明唯一约束后,SQL Server会把所有的NULL值当视为唯一的。这就意味着在一个唯一约束的列你不能有超过一个NULL.

这种性质违背了标准并且仅仅是SQL Server特有的。

为了解决这一问题,要通过声明一个排除NULL值的唯一索引来执行该约束。

请注意,这将阻止使用索引来 搜索NULL值。

幸运的是,要搜索一个NULL值(即使是在一个变量中)你也不得不使用一个谓词,IS NULL,由于NULL=NULL不等于TRUE,所以不满足WHERE条件。

因此,你可以创建一个额外的(非唯一的)索引:

这将用于IS NULL搜索。

 

9. NULLS FIRST

当对包含NULL值的一列使用ORDER BY时,这些NULL值会最先出现。这种行为没有被标准规定,在SQL Server和MySQL中可以看到。

Oracle和PostgreSQL默认把NULL值排在最后。同时,这两个数据库管理系统支持一个扩展来生成ORDER BY语句: 每个表达式接收可选修饰符:NULL FIRST和NULL LAST,指导引擎怎样排序NULL值然后通过表达式返回。这些修饰符可以在索引定义时使用,因此结果集的次序不仅可以在查询中定义,而且可以在索引中定义。

为了解决NULL值问题,可以在查询时使用额外的排序表达式:

不幸的是,SQL Server不像Oracle和PostgreSQL,不允许使用这些语句来创建索引。所以为了能够在索引中使用它,你不得不增加一已计算好的列到表中:

为了这个索引能够在查询中被使用,你应该总是在ORDER BY使用mycolumn_nulls_last,如:

8.谓词 IN 中的多列

你无法在IN谓词中使用多个列。下面这个查询是无效的:

这种性质违反了标准并且是SQL Server特有的。

为了解决这个问题,用EXISTS来代替IN:

请注意这仅仅适用于IN,而不适用于NOT IN.

在处理NULL值方面,NOT IN与NOT EXISTS有一点不同。

为了模仿下面这个NOT IN查询:

我们会用下面这个语句:

第二个谓词确保othertable在col1或者col2列都没有NULL值。任何此类值都会使原始查询不会返回任何记录,尽管其他值会。

 

7. No USING clause(没有USING语句)

SQL Server在连接中不支持USING语句。
这种性质有悖于标准,只针对于SQL Server·[@Lesus 注:像MySQL和PostgreSQL都支持USING语句].

USING语句允许你像这样写连接语句:

代替

注意这两个查询并不是完全相同的:第一个会一次性返回id。

这通常并不是个问题关键,然而有种情况就是FULL JOIN时就有用了。

想象一个像这样的PostgreSQL查询:

这个相当明了:它会选取3个表中全连接有序记录的前10条,如果可能的话,带有NULL值的其它两个表会潜在出现丢失记录。

让我们在SQL Server中实现同样的:

如你所看到的那样,我需要使用丑陋的COALESCE来连接和排序,因为使用全连接任何一个表中都会可能在id列上产生NULL值。这个会阻止优化器使用合并连接,它是用来对有索引的id列的最快的方法。

为了享受MERGE JOIN的好处,我需要像这样重写这个查询:

SQL Server能够使用MERGE UNION而不是MERGE JOIN,它使我们的目的十分有益。

 

6. String concatenation(字符串连接)

连接字符串使用的和数字的加法一样的操作符+。这可能会在数字和字符串混合时导致冲突。这种行为还没在标准中定义,只是针对SQL Server。

当试图连接数字和字符串时,请记住是字符串转换为数字,然后在加,而不是数字转换为字符串,然后连接。

操作符从左到右执行。

这里是一些查询[示例]:

一个字符串加上一个数字等价于一个数字和一个转换为数字的字符串。

一个数字加上一个字符串等价于一个数字和一个转换为数字的字符串。

一个字符串加上字符串等价于一个字符串和其它字符串的连接。

一个字符串加上字符串再加上数字为一个数字。首先,两个字符串连接(产生为’12’),然后,这个结果字符串转换为数字再和3相加(产生15).

首先,这个两个字符串相连接,产生’1a’。然后,试图将结果字符串转换为一个数字,这会失败,就会产生如上的错误。

如果你想连接一个数字和一个字符串,显式转换前者为后者:

5. MAX(BIT)

SQL Server 支持一种特别的数据类型来存储布尔值(booleans),称之为位(BIT)。然而,你不能在这些领域上聚合AND 或者OR 等(通常可以使用MAX和MIN完成)。

这种行为还没有被标准定义,只针对SQL Server。

位类型是为存储布尔值而设计的,一条记录中1字节存储8位,9到16位存储到两个字节中,以此类推,是这种类型存储十分高效。

然而如果我们想聚合这些值时,会遇到错误:

一个简单的方法就是把它们转换为整数:

但是这会阻止使用索引。

更高效的方法就是使用TOP 1 / ORDER BY来代替MAX和MIN。与MAX和MIN不一样排序,它可以在位上工作:

4. TRIM

SQL Server 没有实现可以去除字符串两端的空白字符的TRIM函数。

这种行为违反了标准,只针对SQL Server。

联合使用LTRIM和RTRIM来实现:

3. LEAST and GREATEST(最小值和最大值)

SQL Server不支持LEAST和GREATEST:这两个函数对应于从参数列表中返回最小值或最大值。

这没有在标准中规定,在SQL Server中很特殊。

你可以使用嵌套的CASE语句来模拟这些函数:

但是这个并不具备可读性和即使只是3列就容易出错(就不用说更多了(to say nothing of more))

更好的方式是可以在子查询中使用MAX或者MIN,使用组合联接(UNION)查询来返回合适的字段域。

2. ORDER BY in nested queries(嵌套查询中的ORDER BY)

SQL Server 不支持在嵌套查询中使用ORDER BY。这个查询不会工作:

这个性质在标准中没有定义,对SQL Server也是很特别的。

在嵌套查询和内联(单句查询)表很重视函数,ORDER BY是不支持,除非在SELECT中使用TOP。

SQL 不能保证结果皆的次序,除非你使用ORDER BY显式定义它,这就是为什么ORDER BY在嵌套查询中很重要了。如果你像指定次序,你应该在最外层查询中指定它。

然而,这个可能对于那些从其它引擎移植过来的查询会失败。一般地,ORDER BY允许在嵌套查询中以及要么传递到最外层查询中要么对排序没有假设(如ORDER BY被隐式的忽略了)。

这里没有变通方案,因为你不得不修改嵌套查询,如移除ORDER BY:

或者是添加TOP 100部分:

这两个解决方案会让优化引擎忽略ORDER BY。

1. Readers and writers block each other(读写相互阻塞)

SQL Server的锁系统使读阻塞写,反之亦然,有时甚至它们不能进入相同记录。

这种性质在标准中没有定义,在SQL Server和MySQL中的MyISAM中遵守。

任何正派的数据库系统都应遵守ACID性质,这个会确保事务具有原子性(Atomic),一致性(Consistent), 隔离性(Isolated)和 持久性(Durable).

除此之外,这就意味着没有任何语句可以部分改变数据库的状态(如同外面所看到的那样)。如果一个查询更新了100行,一定没有其它会话会发现30行已更新而70行没有:它总是那么做完,要么什么都不做。

不同的系统使用不同的方式达到这一目的,但是通常这个归结于两个方面中的一个:在更新时创建一份记录集备份,或者锁定记录使只可以一个会话进入。

SQL Server实现的第二种方法。如果一个会话更新一条记录,它会创建一个叫做锁(lock)的特殊的对象,它会阻止并发进入这条记录。即使一个会话读取一条记录,它会放置一个锁在上面(尽管有种方式可以使并发读成为可能)。

锁存放在内存中,并且维护起来很昂贵。所以随着语句的数目变得很大时,SQL Server可能决定增加锁的数目:使锁保护而不至于影响到记录,但是这条记录或者即使是整个表都存在一个数据库页中。这会减少内存,但是使锁缺少了可选性,所以这些记录当查询时可能会锁住,然后对于其它会话不可见。

为了解决这个问题,SQL Server提供了所谓的SNAPSHOT的特殊的事务隔离级别。它不是建立在实现一致性上上锁,而是在一个tempdb上复制一份受影响的记录(临时数据储存在一个特别的存储区域)。这允许写不阻塞读,反之亦然。但是会增加存储需求和系统I/O压力,所以可能会影响性能。

 

参与翻译(2人):Lesus, 媛媛小译

收藏 1 评论

相关文章

可能感兴趣的话题



直接登录
最新评论
  • Miracle_Wong   2013/07/28

    看起来很是高深的样子,最近在学习SQL Server,先mark了,以后留着用!

跳到底部
返回顶部