CODEWITHSUNDEEP
sqlsql-server-2012notnull
WiredTheories
WiredTheories

Reputation: 231

Finding Not null on multiple columns using COALESCE

I have a query to find not null on 2 columns on a table which is a view , hence it is taking a lot of time for execution. The query is : Query1

SELECT [Table1].M, [[Table1]].B, [Table1].P
FROM [Table1]
WHERE ((([[Table1]].B) Is Not Null) AND (([[Table1]].P) Is Not Null));

Does the below query does the same function as Query1 with faster execution time ?

SELECT [Table1].M, [[Table1]].B, [Table1].P
FROM [Table1]
WHERE COALESCE (([[Table1]].B),([[Table1]].P)) Is Not Null

Any help would be of great help and thanks in advance.

The view query

select dbo.TABLE1.[COL1]
    , dbo.TABLE1.[COL2]
    , RIGHT(dbo.TABLE1.M, 12) as M
    , dbo.TABLE2.[MD]
    , dbo.TABLE1.[COL3]
    , dbo.TABLE1.[COL4]
    , dbo.TABLE3.COL1
    , dbo.TABLE3.[COL2]
    , dbo.TABLE3.[COL3]
    , dbo.TABLE4.[COL1]
    , dbo.TABLE5.[COL1]
    , dbo.TABLE6.[COL1]
    , dbo.TABLE7.[COL1] as [BA]
    , dbo.TABLE8.[COL1]
    , dbo.TABLE3.[COL4]
    , dbo.TABLE3.[COL5]
    , dbo.TABLE3.[COL6]
from dbo.TABLE1
left outer join dbo.TABLE2
    on dbo.TABLE1.M = dbo.TABLE2.M
left outer join dbo.TABLE3
    on dbo.TABLE1.M = dbo.TABLE3.M
left outer join dbo.TABLE5
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE5.OBJ
left outer join dbo.TABLE6
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE6.OBJ
left outer join dbo.TABLE7
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE7.OBJ
left outer join dbo.TABLE4
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE4.OBJ
left outer join dbo.TABLE8
    on dbo.TABLE3.[OBJ_NR] = dbo.TABLE8.OBJ
where (
        (
            dbo.TABLE1.[COL1] not in (
                'XX'
                , 'YY'
                )
            )
        and (dbo.TABLE1.COL5 = 'x')
        )

Upvotes: 0

Views: 2321

Answers (2)

Tristan
Tristan

Reputation: 1014

I've tried this out on a table in my development DB. Here are the results:

with only PK index: 2 minutes for 4 million selected records out of 8 million table

with index on 3 selected columns (none of them PK) 1.8 seconds.

You might need to do some testing to get the right indexes for your setup but here is the sample of what i changed:

select [col1]
    , [col2]
    , [col3]
from [dbo].[tbl]
where col2 is not null
    and col3 is not null

create nonclustered index [idx_test] on [dbo].[tbl] (
    [col2] asc
    , [col3] asc
    ) INCLUDE ([col1])

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16351

No, both queries aren't equivalent.

The WHERE clause in the second one is equivalent to 

WHERE [[Table1]].B Is Not Null OR [[Table1]].P Is Not Null

COALESCE will evaluate the first parameter and return it if not null. Otherwise, it will return the second one if not null, and so on, until reaching the last parameter, which will be returned whatever its value. So COALESCE(...) IS NOT NULL needs only one not null value to return true, not all.

Upvotes: 3

Related Questions