One is to SELECT … INTO a new temp table. There are a number of ways to do this inadvertedly. “… but who creates nullable columns without actual NULL values?” I hear you say. I wrote a post a while ago about using set operators like INTERSECT to compare or join on null values, but Paul White has a really nice post that goes a lot more in depth. SELECT i FROM #inner Left Anti Semi join, but with a Scan instead of Seek But instead, EXCEPT does compare NULL values, unlike the equality operator in a regular join. It generates the same query plan in this case, but the downside is that you can only return the key column(s). WHERE i.i IS NULL Shoot first, filter rows later. You could write the query with a LEFT JOIN and a WHERE clause, but what it gains in readability, it loses adding an extra Filter operator that could slow the query down just a fraction: SELECT o.* WHERE NOT EXISTS (SELECT i FROM #inner AS i You could rewrite the query to use a NOT EXISTS construct, which will be optimized to form the exact same Merge Join plan as we saw above. WHERE i IS NOT NULL) Back to doing Merge Joins again. You could change the column to a non-nullable type (so SQL Server won’t have to check for NULL values in the first place), or you could just tell SQL Server to ignore NULL values, by eliminating them with a WHERE clause: SELECT * There are a number of ways we can simplify things. So the lower-right clustered index seek actually checks if there is a NULL value in the inner table’s join column, and if there is, the entire join subsequent Merge Join between the inner and outer tables will return zero rows. And so it is with the inner table, if there happens to be a NULL value among those rows. … always returns false, because the NULL value could represent essentially anything, including the x. We expected the Semi Join to turn into an Anti Semi Join, but the plan now also contains a Nested Loop branch with a Row Count Spool – what’s that about? Turns out the Row Count Spool, along with its index seek, has to do with the NOT IN() and the fact that we’re looking at a nullable column. WHERE i NOT IN (SELECT i FROM #inner) Why so complicated? So what happens if we change the IN() to a NOT IN()? SELECT * This one equates to a really nice merge join, because the two tables have matching clustered indexes on the join column. Now, let’s look at some simple IN () queries. Remove 10 random rows, to make things a little more interesting. Create the inner table, fill it with a copy of the outer table,ĬREATE UNIQUE CLUSTERED INDEX UCIX ON #inner (i) Here’s a quick setup: - Create the outer table, give it some rows: Instead of comparing a fixed set of values, let’s look at a whole table. To prove this, look at the third comparison, where there’s no 1, but still a NULL. The second comparison is false, but not because there’s a 1 in the list, but rather because there’s a NULL. So the first comparison is true, and it’s unaffected by the fact that there’s a NULL value in the list we’re comparing with. (CASE WHEN 1 NOT IN (2, 3, 4, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN 1 NOT IN (0, 1, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN 1 IN (0, 1, NULL) THEN 'True' ELSE 'False' END) Now, let’s turn it around and look if we can look for a constant in a dataset that includes a NULL value: (CASE WHEN NULL NOT IN (0, 1, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN NULL IN (0, 1, NULL) THEN 'True' ELSE 'False' END) (CASE WHEN NULL IN (0, 1) THEN 'True' ELSE 'False' END) So it stands to reason that this also applies to IN and NOT IN: - False: (CASE WHEN NULL=NULL THEN 'True' ELSE 'False' END) (CASE WHEN 0!=NULL THEN 'True' ELSE 'False' END) (CASE WHEN 0=NULL THEN 'True' ELSE 'False' END) This is because NULL values aren’t real values as such, but rather “unknowns”. For instance, a comparison between two NULL values, or even a comparison between a NULL value and a non-value, will always be false. Specifically, because of how NULL values are compared, they can dramatically affect how some lookup operations perform. We need to talk about the nullable columns in your database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |