使用NULL查询性能 [英] Query Performance with NULL
问题描述
我想知道NULL值如何影响SQL Server 2005中的查询性能。
I would like to know about how NULL values affect query performance in SQL Server 2005.
我有一个类似于这个（简化）的表：
I have a table similar to this (simplified):
ID | ImportantData | QuickPickOrder
--------------------------
1 | 'Some Text' | NULL
2 | 'Other Text' | 3
3 | 'abcdefg' | NULL
4 | 'whatever' | 4
5 | 'it is' | 2
6 | 'technically' | NULL
7 | 'a varchar' | NULL
8 | 'of course' | 1
9 | 'but that' | NULL
10 | 'is not' | NULL
11 | 'important' | 5
我正在这样做一个查询：
And I'm doing a query on it like this:
SELECT *
FROM MyTable
WHERE QuickPickOrder IS NOT NULL
ORDER BY QuickPickOrder
所以QuickPickOrder基本上是一个列，用于从更大的列表中挑选出一些常用的选项。它还提供了它们向用户显示的顺序。 NULL值意味着它不会显示在快速选择列表中。
So the QuickPickOrder is basically a column used to single out some commonly chosen items from a larger list. It also provides the order in which they will appear to the user. NULL values mean that it doesn't show up in the quick pick list.
我一直被告知，数据库中的NULL值是有点邪恶的，至少从一个规范化的观点，但它是一个可以接受的方式过滤掉不必要的行在WHERE约束？
I've always been told that NULL values in a database are somehow evil, at least from a normalization perspective, but is it an acceptable way to filter out unwanted rows in a WHERE constraint?
最好使用特定的数值，如-1或0 ，表示不想要的项目？是否有其他替代方法？
Would it be better to use specific number value, like -1 or 0, to indicate items that aren't wanted? Are there other alternatives?
编辑：
该示例不能精确地表示实际值与NULL的比率。更好的示例可能会为每个非NULL显示至少10个NULL。表格大小可能为100到200行。
The example does not accuratly represent the ratio of real values to NULLs. An better example might show at least 10 NULLs for every non-NULL. The table size might be 100 to 200 rows. It is a reference table so updates are rare.
推荐答案
SQL Server 索引 NULL 的值，所以这很可能只是使用索引 QuickPickOrder ，用于过滤和订购。
SQL Server indexes NULL values, so this will most probably just use the Index Seek over an index on QuickPickOrder, both for filtering and for ordering.
这篇关于使用NULL查询性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
