Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is an issue I come up against periodically and have not yet found a good solution for.

Supposing the following table structure

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

and the requirement is to determine whether either of the nullable columns B or C actually contain any NULL values (and if so which one(s)).

Also assume the table contains millions of rows (and that no column statistics are available that could be peeked at as I am interested in a more generic solution for this class of queries).

I can think of a few ways of approaching this but all have weaknesses.

Two separate EXISTS statements. This would have the advantage of allowing the queries to stop scanning early as soon as a NULL is found. But if both columns in fact contain no NULLs then two full scans will result.

Single Aggregate Query

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

This could process both columns at the same time so have a worst case of one full scan. The disadvantage is that even if it encounters a NULL in both columns very early on the query will still end up scanning the whole of the rest of the table.

User variables

I can think of a third way of doing this

BEGIN TRY
DECLARE @B INT, @C INT, @D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
    @C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
    /*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),
       ISNULL(@C,0)

but this is not suitable for production code as the correct behavior for an aggregate concatenation query is undefined. and terminating the scan by throwing an error is quite a horrible solution anyway.

Is there another option that combines the strengths of the approaches above?

Edit

Just to update this with the results I get in terms of reads for the answers submitted so far (using @ypercube's test data)

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP, MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

For @Thomas's answer I changed TOP 3 to TOP 2 to potentially allow it to exit earlier. I got a parallel plan by default for that answer so also tried it with a MAXDOP 1 hint in order to make the number of reads more comparable to the other plans. I was somewhat surprised by the results as in my earlier test I had seen that query short circuit without reading the whole table.

The plan for my test data that short circuits is below

Shortcircuits

The plan for ypercube's data is

Not Shortcircuit

So it adds a blocking sort operator to the plan. I also tried with the HASH GROUP hint but that still ends up reading all the rows

Not Shortcircuit

So the key seems to be to get a hash match (flow distinct) operator to allow this plan to short circuit as the other alternatives will block and consume all rows anyway. I don't think there is hint to force this specifically but apparently "in general, the optimiser chooses a Flow Distinct where it determines that fewer output rows are required than there are distinct values in the input set.".

@ypercube's data only has 1 row in each column with NULL values (table cardinality = 30300) and the estimated rows going into and out of the operator are both 1. By making the predicate a bit more opaque to the optimiser it generated a plan with the Flow Distinct operator.

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT 

Edit 2

One last tweak that occurred to me is that the query above could still end up processing more rows than necessary in the event that the first row it encounters with a NULL has NULLs in both column B and C. It will continue scanning rather than exiting immediately. One way of avoiding this would be to unpivot the rows as they are scanned. So my final amend to Thomas Kejser's answer is below

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

It would probably be better for the predicate to be WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULL but against the previous test data that one doesn't give me a plan with a Flow Distinct, whereas the NullExists IS NOT NULL one does (plan below).

Unpivoted

share|improve this question
    
Will not 2 separate EXISTS use the indices on those columns (one index by each subquery) to find if there are nulls? Or you mean that you don't have (and not allowed to add) index? –  ypercube Jun 15 '12 at 21:58
    
@ypercube - They could use separate columns indexes if they existed. They don't in this case though. –  Martin Smith Jun 15 '12 at 21:59
add comment

5 Answers

up vote 10 down vote accepted

How about:

SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS B
        , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS C
  FROM T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT
share|improve this answer
    
Oh. My. Goodness. Welcome to dba.se, @Thomas! –  Aaron Bertrand Jun 15 '12 at 22:29
    
@Thomas: Welcome to Stack Exchange. Have watched your SQL Bits Extreme DW presentation about twenty times. –  8kb Jun 16 '12 at 0:01
    
I like this approach. There are a few possible issues that I address in edits to my question though. As written TOP 3 could just be TOP 2 as currently it will scan until it finds one of each of the following (NOT_NULL,NULL),(NULL,NOT_NULL),(NULL,NULL). Any 2 out of those 3 would be sufficient - and if it finds (NULL,NULL) first then the second wouldn't be needed either. Also in order to short circuit the plan would need to implement the distinct via a hash match (flow distinct) operator rather than hash match (aggregate) or distinct sort –  Martin Smith Jun 17 '12 at 12:33
    
in where clause we can use 'B is NULL or C is NULL' instead of using three condition –  techExplorer Jun 25 '12 at 9:03
add comment

As I understand the question, you want to know whether a null exists in any of the columns values as opposed to actually returning the rows in which either B or C is null. If that is the case, then why not:

Select Top 1 'B as nulls' As Col
From T
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T
Where T.C Is Null

On my test rig with SQL 2008 R2 and one million rows, I got the following results in ms from the Client Statistics tab:

Kejser                          2907,2875,2829,3576,3103
ypercube                        2454,1738,1743,1765,2305
OP single aggregate solution    (stopped after 120,000 ms) Wouldn't even finish
My solution                     1619,1564,1665,1675,1674

If you add the nolock hint, the results are even faster:

Select Top 1 'B as nulls' As Col
From T With(Nolock)
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T With(Nolock)
Where T.C Is Null

My solution (with nolock)       42,70,94,138,120

For reference I used Red-gate's SQL Generator to generate the data. Out of my one million rows, 9,886 rows had a null B value and 10,019 had a null C value.

In this series of tests, every row in column B has a value:

Kejser                          245200  Scan count 1, logical reads 367259, physical reads 858, read-ahead reads 367278
                                250540  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367280

ypercube(1)                     249137  Scan count 2, logical reads 367276, physical reads 850, read-ahead reads 367278
                                248276  Scan count 2, logical reads 367276, physical reads 869, read-ahead reads 368765

My solution                     250348  Scan count 2, logical reads 367276, physical reads 858, read-ahead reads 367278
                                250327  Scan count 2, logical reads 367276, physical reads 854, read-ahead reads 367278

Before each test (both sets) I ran CHECKPOINT and DBCC DROPCLEANBUFFERS.

Here are the results when there are no nulls in the table. Note that the 2 exists solution provided by ypercube are nearly identical to mine in terms of reads and execution time. I (we) believe this is due to advantages of Enterprise/Developer edition having use of Advanced Scanning. If you were using only the Standard edition or lower, Kejser's solution may very well be the fastest solution.

Kejser                          248875  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367290

ypercube(1)                     243349  Scan count 2, logical reads 367265, physical reads 851, read-ahead reads 367278
                                242729  Scan count 2, logical reads 367265, physical reads 858, read-ahead reads 367276
                                242531  Scan count 2, logical reads 367265, physical reads 855, read-ahead reads 367278

My solution                     243094  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
                                243444  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
share|improve this answer
    
@MartinSmith - Can IAM ordered scans take advantage of advanced scanning? If not, that might explain it. –  Thomas Jun 16 '12 at 11:03
    
@MartinSmith - Not sure. I can reset the test to re-run where there were 10K some odd null C values. –  Thomas Jun 16 '12 at 11:46
    
You guys may want to take this to chat... –  JNK Jun 16 '12 at 12:02
    
To be clear though I do like this technique for when advanced scanning is available. It avoids the issue of not short circuiting if you get a blocking operator in the plan. –  Martin Smith Jun 16 '12 at 12:04
    
@Thomas - For the NOLOCK results it may well just be luck depending on whether it hits the NULL values earlier in the indexed ordered scan than in the IAM ordered one or not. –  Martin Smith Jun 16 '12 at 13:01
show 3 more comments

Are IF statements allowed?

This should allow you to confirm existence of B or C on one pass through the table:

DECLARE 
  @A INT, 
  @B CHAR(10), 
  @C CHAR(10)

SET @B = 'X'
SET @C = 'X'

SELECT TOP 1 
  @A = A, 
  @B = B, 
  @C = C
FROM T 
WHERE B IS NULL OR C IS NULL 

IF @@ROWCOUNT = 0 
BEGIN 
  SELECT 'No nulls'
  RETURN
END

IF @B IS NULL AND @C IS NULL
BEGIN
  SELECT 'Both null'
  RETURN
END 

IF @B IS NULL 
BEGIN
  SELECT TOP 1 
    @C = C
  FROM T
  WHERE A > @A
  AND C IS NULL

  IF @B IS NULL AND @C IS NULL 
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'B is null'
    RETURN
  END
END

IF @C IS NULL 
BEGIN
  SELECT TOP 1 
    @B = B
  FROM T 
  WHERE A > @A
  AND B IS NULL

  IF @C IS NULL AND @B IS NULL
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'C is null'
    RETURN
  END
END      
share|improve this answer
    
+1 This seems to strike a good balance. –  Martin Smith Jun 16 '12 at 9:02
add comment

Tested in SQL-Fiddle in versions: 2008 r2 and 2012 with 30K rows.

  • The EXISTS query shows a huge benefit in efficiency when it finds Nulls early - which is expected.
  • I get better performance with the EXISTS query - in all cases in 2012, which I can't explain.
  • In 2008R2, when there are no Nulls, it's slower than the other 2 queries. The more early it finds the Nulls, the faster it gets and when both columns have nulls early, it's much faster than the other 2 queries.
  • Thomas Kejser's query seems to perform slightly but constantly better in 2012 and worse in 2008R2, compared to Martin's CASE query.
  • 2012 version seems to have far better performance. It may have to do with the SQL-Fiddle servers' settings though and not only with improvements on the optimizer.

Queries and timings. Timings where done:

  • 1st with no Nulls at all
  • 2nd with column B having one NULL at a small id.
  • 3nd with both columns having one NULL each at small ids.

Here we go (there is an issue with the plans, I'll try again later. Follow the links for now):


Query with 2 EXISTS subqueries

SELECT 
      CASE WHEN EXISTS (SELECT * FROM test WHERE b IS NULL)
             THEN 1 ELSE 0 
      END AS B,
      CASE WHEN EXISTS (SELECT * FROM test WHERE c IS NULL)
             THEN 1 ELSE 0 
      END AS C ;

-------------------------------------
Times in ms (2008R2): 1344 - 596 -  1  
Times in ms   (2012):   26 -  14 -  2

Martin Smith's Single Aggregate Query

SELECT 
    MAX(CASE WHEN b IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN c IS NULL THEN 1 ELSE 0 END) AS C
FROM test ;

--------------------------------------
Times in ms (2008R2):  558 - 553 - 516  
Times in ms   (2012):   37 -  35 -  36

Thomas Kejser's query

SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT ;

--------------------------------------
Times in ms (2008R2):  859 - 705 - 668  
Times in ms   (2012):   24 -  19 -  18

My suggestion (1)

WITH tmp1 AS
  ( SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id 
  ) 

  SELECT 
      tmp1.*, 
      NULL AS id2, NULL AS b2, NULL AS c2
  FROM tmp1
UNION ALL
  SELECT *
  FROM
    ( SELECT TOP (1)
          tmp1.id, tmp1.b, tmp1.c,
          test.id AS id2, test.b AS b2, test.c AS c2 
      FROM test
        CROSS JOIN tmp1
      WHERE test.id >= tmp1.id
        AND ( test.b IS NULL AND tmp1.c IS NULL
           OR tmp1.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id
    ) AS x ;

--------------------------------------
Times in ms (2008R2): 1089 - 572 -  16   
Times in ms   (2012):   28 -  15 -   1

It needs some polishing on the output but the efficiency is similar to the EXISTS query. I thought it would be better when there are no nulls but testing shows it's not.


Suggestion (2)

Trying to simplify the logic:

CREATE TABLE tmp
( id INT
, b CHAR(1000)
, c CHAR(1000)
) ;

DELETE  FROM tmp ;

INSERT INTO tmp 
    SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id  ; 

INSERT INTO tmp 
    SELECT TOP (1)
        test.id, test.b, test.c 
      FROM test
        JOIN tmp 
          ON test.id >= tmp.id
      WHERE ( test.b IS NULL AND tmp.c IS NULL
           OR tmp.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id ;

SELECT *
FROM tmp ;

It seems to perform better in 2008R2 than the previous suggestion but worse in 2012 (perhaps the 2nd INSERT can be rewritten using IF, like @8kb's answer):

------------------------------------------
Times in ms (2008R2): 416+6 - 1+127 -  1+1   
Times in ms   (2012):  14+1 - 0+27  -  0+29
share|improve this answer
    
I can't see the plans unfortunately. Will take a look at the fiddle but it's quite late now so may not respond until tomorrow! –  Martin Smith Jun 15 '12 at 23:30
    
I have a problem posting the plans. I get back pngs. I'll try later. I'm editing now to add timigs. –  ypercube Jun 15 '12 at 23:39
    
+1 Not sure why the 2012 EXISTS case would give those results either though. Seems somewhat anomalous. –  Martin Smith Jun 16 '12 at 9:05
    
8kb seem to be doing a similar thing. I think it would be a much less clumsy approach when you have more than 2 columns. Not sure if SQL-Fiddle allows us to define procedures (I can't test in 2012 otherwise). –  ypercube Jun 16 '12 at 9:11
    
Agh! 30k rows in sql fiddle! You're being cruel to my little website. Isn't looking at the execution plan adequate to compare performance? –  Jake Feasel Jun 21 '12 at 0:21
show 3 more comments

When you use EXISTS, SQL Server knows you are doing an existence check. When it finds the first matching value, it returns TRUE and stops looking.

when you concatinate 2 columns and if any is null the result will be null

e.g

null + 'a' = null

so check this code

IF EXISTS (SELECT 1 FROM T WHERE B+C is null)
SELECT Top 1 ISNULL(B,'B ') + ISNULL(C,'C') as [Nullcolumn] FROM T WHERE B+C is null
share|improve this answer
    
That would only let me know that either B or C had a NULL value. How would I know which one it was? And whether the other column also contained any NULLS? –  Martin Smith Jun 15 '12 at 23:04
    
Although on re-reading the question I can see why you gave this answer now. Sorry! –  Martin Smith Jun 17 '12 at 21:09
    
No worries dear, in the beginning i thought you didn't care to know the column, but now i see (and if so which one(s)), i will try to edit it though –  AmmarR Jun 18 '12 at 4:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.