10. 唯一NULL
http://goo.gl/XHiAY
当在列上声明唯一约束后，SQL Server会把所有的NULL值当视为唯一的。这就意味着在一个唯一约束的列你不能有超过一个NULL.
这种性质违背了标准并且仅仅是SQL Server特有的。
为了解决这一问题，要通过声明一个排除NULL值的唯一索引来执行该约束。
CREATE UNIQUE INDEX ux_mytable_mycolumn ON mytable (mycolumn) WHERE mycolumn IS NOT NULL
请注意，这将阻止使用索引来 搜索NULL值。
幸运的是，要搜索一个NULL值（即使是在一个变量中）你也不得不使用一个谓词，IS NULL，由于NULL=NULL不等于TRUE，所以不满足WHERE条件。
因此，你可以创建一个额外的（非唯一的）索引：
CREATE INDEX ix_mytable_mycolumn_null ON mytable (mycolumn) WHERE mycolumn IS NULL
这将用于IS NULL搜索。
9. NULLS FIRST
http://goo.gl/62ExV
当对包含NULL值的一列使用ORDER BY时，这些NULL值会最先出现。这种行为没有被标准规定，在SQL Server和MySQL中可以看到。
Oracle和PostgreSQL默认把 NULL值排在最后。同时，这两个数据库管理系统支持一个扩展来生成ORDER BY语句： 每个表达式接收可选修饰符：NULL FIRST和NULL LAST，指导引擎怎样排序NULL值然后通过表达式返回。这些修饰符可以在索引定义时使用，因此结果集的次序不仅可以在查询中定义，而且可以在索引中定 义。
为了解决NULL值问题，可以在查询时使用额外的排序表达式：
SELECT * FROM mytable ORDER BY CASE WHEN mycolumn IS NULL THEN 1 END, mycolumn
不幸的是，SQL Server不像Oracle和PostgreSQL，不允许使用这些语句来创建索引。所以为了能够在索引中使用它，你不得不增加一已计算好的列到表中：
ALTER TABLE mytable ADD mycolumn_nulls_last AS CASE WHEN mycolumn IS NULL THEN 1 END GO CREATE INDEX ix_mytable_mycolumn_nulls_last ON mytable (mycolumn_nulls_last, mycolumn) GO
为了这个索引能够在查询中被使用，你应该总是在ORDER BY使用mycolumn_nulls_last,如：
SELECT * FROM mytable ORDER BY mycolumn_nulls_last, mycolumn
8.谓词 IN 中的多列
http://goo.gl/0yCqD
你无法在IN谓词中使用多个列。下面这个查询是无效的：
SELECT * FROM mytable WHERE (col1, col2) IN ( SELECT col1, col2 FROM othertable )
这种性质违反了标准并且是SQL Server特有的。
为了解决这个问题，用EXISTS来代替IN:
SELECT * FROM mytable m WHERE EXISTS ( SELECT NULL FROM othertable o WHERE o.col1 = m.col1 AND o.col2 = m.col2 )
请注意这仅仅适用于IN，而不适用于NOT IN.
在处理NULL值方面，NOT IN与NOT EXISTS有一点不同。
为了模仿下面这个NOT IN查询：
SELECT * FROM mytable m WHERE (col1, col2) NOT IN ( SELECT col1, col2 FROM othertable )
我们会用下面这个语句：
SELECT * FROM mytable m WHERE NOT EXISTS ( SELECT NULL FROM othertable o WHERE o.col1 = m.col1 AND o.col2 = m.col2 ) AND NOT EXISTS ( SELECT NULL FROM othertable o WHERE o.col1 IS NULL OR o.col2 IS NULL )
第二个谓词确保othertable在col1或者col2列都没有NULL值。任何此类值都会使原始查询不会返回任何记录，尽管其他值会。
7. No USING clause(没有USING语句)
http://goo.gl/Fx0je
SQL Server在连接中不支持USING语句。
这种性质有悖于标准，只针对于SQL Server·[@Lesus 注：像MySQL和PostgreSQL都支持USING语句].
USING语句允许你像这样写连接语句：
SELECT * FROM mytable JOIN othertable USING (id)
代替
SELECT * FROM mytable m JOIN othertable o USING o.id = m.id
注意这两个查询并不是完全相同的：第一个会一次性返回id。
这通常并不是个问题关键，然而有种情况就是FULL JOIN时就有用了。
想象一个像这样的PostgreSQL查询：
SELECT * FROM table1 FULL JOIN table2 USING (id) FULL JOIN table3 USING (id) ORDER BY id LIMIT 10
这个相当明了：它会选取3个表中全连接有序记录的前10条，如果可能的话，带有NULL值的其它两个表会潜在出现丢失记录。
让我们在SQL Server中实现同样的：
SELECT TOP 10 * FROM table1 t1 FULL JOIN table2 t2 ON t2.id = t1.id FULL JOIN table3 t3 ON t3.id = COALESCE(t1.id, t2.id) ORDER BY COALESCE(t1.id, t2.id, t3.id)
如你所看到的那样，我需要使用丑陋的COALESCE来连接和排序，因为使用全连接任何一个表中都会可能在id列上产生NULL值。这个会阻止优化器使用合并连接，它是用来对有索引的id列的最快的方法。
为了享受MERGE JOIN的好处，我需要像这样重写这个查询：
SELECT * FROM ( SELECT TOP 10 id FROM ( SELECT id FROM table1 UNION ALL SELECT id FROM table2 UNION SELECT id FROM table3 ) q ORDER BY id ) q LEFT JOIN table1 t1 ON t1.id = q.id LEFT JOIN table2 t2 ON t2.id = q.id LEFT JOIN table3 t3 ON t3.id = q.id
SQL Server能够使用MERGE UNION而不是MERGE JOIN，它使我们的目的十分有益。
6. String concatenation(字符串连接)
http://goo.gl/BTaVx
连接字符串使用的和数字的加法一样的操作符+。这可能会在数字和字符串混合时导致冲突。这种行为还没在标准中定义，只是针对SQL Server。
当试图连接数字和字符串时，请记住是字符串转换为数字，然后在加，而不是数字转换为字符串，然后连接。
操作符从左到右执行。
这里是一些查询[示例]:
SELECT '1' + 1 -- 2
一个字符串加上一个数字等价于一个数字和一个转换为数字的字符串。
SELECT 1 + '1' -- 2
一个数字加上一个字符串等价于一个数字和一个转换为数字的字符串。
SELECT '3' + '2' -- '32'
一个字符串加上字符串等价于一个字符串和其它字符串的连接。
SELECT '1' + '2' + 3 -- 15
一个字符串加上字符串再加上数字为一个数字。首先，两个字符串连接（产生为'12'）,然后，这个结果字符串转换为数字再和3相加（产生15）.
SELECT '1' + 'a' + 3 -- Conversion failed when converting the varchar value '1a' to data type int.: SELECT '1' + 'a' + 3
首先，这个两个字符串相连接，产生'1a'。然后，试图将结果字符串转换为一个数字，这会失败，就会产生如上的错误。
如果你想连接一个数字和一个字符串，显式转换前者为后者：
SELECT 'Value' + CAST(123 AS CHAR) -- 'Value123'
5. MAX(BIT)
http://goo.gl/jLl4S
SQL Server 支持一种特别的数据类型来存储布尔值(booleans)，称之为位（BIT）。然而，你不能在这些领域上聚合AND 或者OR 等（通常可以使用MAX和MIN完成）。
这种行为还没有被标准定义，只针对SQL Server。
位类型是为存储布尔值而设计的，一条记录中1字节存储8位，9到16位存储到两个字节中，以此类推，是这种类型存储十分高效。
然而如果我们想聚合这些值时，会遇到错误：
CREATE TABLE bitvalues ( id INT NOT NULL PRIMARY KEY, v1 BIT, v2 BIT, v3 BIT, ) CREATE INDEX ix_bitvalues_v1 ON bitvalues (v1) CREATE INDEX ix_bitvalues_v2 ON bitvalues (v2) CREATE INDEX ix_bitvalues_v3 ON bitvalues (v3) INSERT INTO bitvalues VALUES (1, 0, 0, 1), (2, 0, 1, 1) SELECT MAX(v1), MAX(v2), MAX(v3), MIN(v1), MIN(v2), MIN(v3) FROM bitvalues -- Operand data type bit is invalid for max operator.: SELECT MAX(v1), MAX(v2), MAX(v3), MIN(v1), MIN(v2), MIN(v3) FROM bitvalues
一个简单的方法就是把它们转换为整数：
SELECT MAX(CAST(v1 AS INT)), MAX(CAST(v2 AS INT)), MAX(CAST(v3 AS INT)), MIN(CAST(v1 AS INT)), MIN(CAST(v2 AS INT)), MIN(CAST(v3 AS INT)) FROM bitvalues
但是这会阻止使用索引。
更高效的方法就是使用TOP 1 / ORDER BY来代替MAX和MIN。与MAX和MIN不一样排序，它可以在位上工作：
SELECT ( SELECT TOP 1 v1 FROM bitvalues ORDER BY v1 DESC ), ( SELECT TOP 1 v2 FROM bitvalues ORDER BY v2 DESC ), ( SELECT TOP 1 v3 FROM bitvalues ORDER BY v1 DESC ), ( SELECT TOP 1 v1 FROM bitvalues ORDER BY v1 ), ( SELECT TOP 1 v2 FROM bitvalues ORDER BY v2 ), ( SELECT TOP 1 v3 FROM bitvalues ORDER BY v3 )
4. TRIM
http://goo.gl/AjTtZ
SQL Server 没有实现可以去除字符串两端的空白字符的TRIM函数。
这种行为违反了标准，只针对SQL Server。
联合使用LTRIM和RTRIM来实现：
SELECT LTRIM(RTRIM(' my string with spaces ')) -- 'my string with spaces'
Lesus
翻译于 昨天(10:40)
0人顶
顶 翻译的不错哦!
3. LEAST and GREATEST(最小值和最大值)
http://goo.gl/K7YDV
SQL Server不支持LEAST和GREATEST：这两个函数对应于从参数列表中返回最小值或最大值。
这没有在标准中规定，在SQL Server中很特殊。
你可以使用嵌套的CASE语句来模拟这些函数：
SELECT CASE WHEN x > y THEN x ELSE y END FROM mytable
但是这个并不具备可读性和即使只是3列就容易出错（就不用说更多了（to say nothing of more））
更好的方式是可以在子查询中使用MAX或者MIN，使用组合联接(UNION)查询来返回合适的字段域。
SELECT ( SELECT MIN(v) FROM ( SELECT x UNION ALL SELECT y UNION ALL SELECT z UNION ALL SELECT t ) q(v) ) FROM mytable
2. ORDER BY in nested queries(嵌套查询中的ORDER BY)
http://goo.gl/sTXFc
SQL Server 不支持在嵌套查询中使用ORDER BY。这个查询不会工作：
SELECT * FROM ( SELECT * FROM mytable WHERE col1 = 42 ORDER BY id ) q
这个性质在标准中没有定义，对SQL Server也是很特别的。
在嵌套查询和内联（单句查询）表很重视函数，ORDER BY是不支持，除非在SELECT中使用TOP。
SQL 不能保证结果皆的次序，除非你使用ORDER BY显式定义它，这就是为什么ORDER BY在嵌套查询中很重要了。如果你像指定次序，你应该在最外层查询中指定它。
然而，这个可能对于那些从其它引擎移植过来的查询会失败。一般地，ORDER BY允许在嵌套查询中以及要么传递到最外层查询中要么对排序没有假设(如ORDER BY被隐式的忽略了)。
这里没有变通方案，因为你不得不修改嵌套查询，如移除ORDER BY：
SELECT * FROM ( SELECT * FROM mytable WHERE col1 = 42 ) q
或者是添加TOP 100部分：
SELECT * FROM ( SELECT TOP 100 PERCENT * FROM mytable WHERE col1 = 42 ORDER BY id ) q
这两个解决方案会让优化引擎忽略ORDER BY。
1. Readers and writers block each other（读写相互阻塞）
http://goo.gl/GZ2jM
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压力，所以可能会影响性能。
原文http://www.oschina.net/translate/10-things-in-sql-server-which-don-t-work-as-expected
