April 15, 2016 at 8:17 am
No problem here, just thought people should know cos it was surprising to find out.
Ran these tests on Adventureworks2012 on a SQL2012 instance.
Seems like ISNULL is sargable IF the column you are operating on is not nullible.
I guess they check that it is redundant logic and just ignore it.
Seems like Coalesce is not sargable at all despite having very similar functionality.
I tested on a column which was not nullible, and another that was.
TEST 1 - Not Nullible column
use adventureworks2012
go
set statistics io on
select SalesOrderID from Sales.SalesOrderDetail where SalesOrderID = 43665
Table 'SalesOrderDetail'. Scan count 1, logical reads 3
select SalesOrderID from Sales.SalesOrderDetail where isnull(SalesOrderID,-1) = 43665 --Surprised by this one
Table 'SalesOrderDetail'. Scan count 1, logical reads 3
select SalesOrderID from Sales.SalesOrderDetail where coalesce(SalesOrderID,-1) = 43665
Table 'SalesOrderDetail'. Scan count 1, logical reads 276
Test 2 - Nullible column
Created an index on the nullible column.
create index IXMiddlename on Person.person(middlename)
set statistics io on
Select businessentityid from person.person where middlename= 'Richard'
Table 'Person'. Scan count 1, logical reads 2
Select businessentityid from person.person where isnull(middlename,'')= 'Richard'
Table 'Person'. Scan count 1, logical reads 36
Select businessentityid from person.person where coalesce(middlename,'')= 'Richard'
Table 'Person'. Scan count 1, logical reads 36
Interesting
April 15, 2016 at 8:38 am
<RANT ON>
Wrapping a function around a column in the WHERE clause is probably the second worst thing I see in aggregate in my consulting (with the first being mismatched data types). So I certainly hope no one EVER uses ISNULL/COALESCE around a NOT NULL column. Doing so is just unforgivably sloppy because the schema definition is KNOWN WHEN THE CODE IS WRITTEN!!! That is the same reason why it is so awful that so many people (and even frameworks) send in TSQL with an incorrect data type!
</RANT ON>
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 10:00 am
TheSQLGuru (4/15/2016)
Wrapping a function around a column in the WHERE clause is probably the second worst thing I see in aggregate in my consulting (with the first being mismatched data types).
Then where should I place UDFs of the multi-statement and of the scalar varieties? Are they they zeroeth worst thing you've seen? Or did you just never encounter those clients?
April 15, 2016 at 10:27 am
Hugo Kornelis (4/15/2016)
TheSQLGuru (4/15/2016)
Wrapping a function around a column in the WHERE clause is probably the second worst thing I see in aggregate in my consulting (with the first being mismatched data types).Then where should I place UDFs of the multi-statement and of the scalar varieties? Are they they zeroeth worst thing you've seen? Or did you just never encounter those clients?
I was also a zealot when it came to using functions on a column.
MS have obviously done some work in this regard as now I cannot anymore claim that all functions on any columns in a 'where' clause is terrible.
They have done similar work with Cast as doing a cast(intcolumn as smallint) doesnt break sargability.
Regarding the UDF's, I have had to replace a date formatting Scalar function embedded in 300 views on a data warehouse.
Server was never running more than 5% CPU and jobs were taking an age and a day when I joined the team, because, guess why?
no parallelism. 32 cores bone idle most of their lives.
I have a deep deep seated, maybe bordering on irrational, hate, and I don't mean dislike, I mean hate, of scalar functions because of that experience and would rank that as number one.
April 15, 2016 at 10:40 am
The simplest rules is:
Never use ISNULL in a WHERE or JOIN clause.
Whether the column is nullable or not is irrelevant.
The proper way to code this:
where isnull(SalesOrderID,-1) = 43665 --NEVER!!
is this:
where SalesOrderId = 43655
since null will never be "=" to anything.
Btw, the proper way to code this:
where isnull(SalesOrderID,43665) = 43665
is this:
where (SalesOrderID IS NULL OR SalesOrderID = 43665)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 15, 2016 at 10:53 am
Hugo Kornelis (4/15/2016)
TheSQLGuru (4/15/2016)
Wrapping a function around a column in the WHERE clause is probably the second worst thing I see in aggregate in my consulting (with the first being mismatched data types).Then where should I place UDFs of the multi-statement and of the scalar varieties? Are they they zeroeth worst thing you've seen? Or did you just never encounter those clients?
No, I have enountered Scalar/Multi-statementTVF UDFs at many clients. They are in part lumped in with the function around column. And obviously they are devastatingly bad (especially that little-known parallelism inhibitor). But datatype issues are just overwhelmingly rampant (including being done BY DESIGN with ORMs and even ADO.NET if you don't know what you are doing or don't pay attention) AND cause severe problems, so they percolate up to the top of the heap on the Guru's Badness Meter.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 10:56 am
ScottPletcher (4/15/2016)
The simplest rules is:Never use ISNULL in a WHERE or JOIN clause.
Whether the column is nullable or not is irrelevant.
The proper way to code this:
where isnull(SalesOrderID,-1) = 43665 --NEVER!!
is this:
where SalesOrderId = 43655
since null will never be "=" to anything.
Btw, the proper way to code this:
where isnull(SalesOrderID,43665) = 43665
is this:
where (SalesOrderID IS NULL OR SalesOrderID = 43665)
How do you code this one Scott?
WHERE SalesOrderID <> 43665
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 11:15 am
TheSQLGuru (4/15/2016)
ScottPletcher (4/15/2016)
The simplest rules is:Never use ISNULL in a WHERE or JOIN clause.
Whether the column is nullable or not is irrelevant.
The proper way to code this:
where isnull(SalesOrderID,-1) = 43665 --NEVER!!
is this:
where SalesOrderId = 43655
since null will never be "=" to anything.
Btw, the proper way to code this:
where isnull(SalesOrderID,43665) = 43665
is this:
where (SalesOrderID IS NULL OR SalesOrderID = 43665)
How do you code this one Scott?
WHERE SalesOrderID <> 43665
That way, you have no choice. But SQL can still use an index seek when appropriate. But when you use ISNULL, it can't (until/unless MS puts a special bypass in the optimizer to handle it).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 15, 2016 at 11:54 am
ScottPletcher (4/15/2016)
TheSQLGuru (4/15/2016)
ScottPletcher (4/15/2016)
The simplest rules is:Never use ISNULL in a WHERE or JOIN clause.
Whether the column is nullable or not is irrelevant.
The proper way to code this:
where isnull(SalesOrderID,-1) = 43665 --NEVER!!
is this:
where SalesOrderId = 43655
since null will never be "=" to anything.
Btw, the proper way to code this:
where isnull(SalesOrderID,43665) = 43665
is this:
where (SalesOrderID IS NULL OR SalesOrderID = 43665)
How do you code this one Scott?
WHERE SalesOrderID <> 43665That way, you have no choice. But SQL can still use an index seek when appropriate. But when you use ISNULL, it can't (until/unless MS puts a special bypass in the optimizer to handle it).
Yes, I know you have no choice on the NOT EQUALS scenario. I just wanted it on the thread so that anyone reading your NEVER and IRRELEVANT statements would see that they are not absolutely correct. Otherwise they would be getting the wrong answer without warning or error, and that is by far the worst thing that can happen in data processing on any system. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 15, 2016 at 12:06 pm
TheSQLGuru (4/15/2016)
ScottPletcher (4/15/2016)
TheSQLGuru (4/15/2016)
ScottPletcher (4/15/2016)
The simplest rules is:Never use ISNULL in a WHERE or JOIN clause.
Whether the column is nullable or not is irrelevant.
The proper way to code this:
where isnull(SalesOrderID,-1) = 43665 --NEVER!!
is this:
where SalesOrderId = 43655
since null will never be "=" to anything.
Btw, the proper way to code this:
where isnull(SalesOrderID,43665) = 43665
is this:
where (SalesOrderID IS NULL OR SalesOrderID = 43665)
How do you code this one Scott?
WHERE SalesOrderID <> 43665That way, you have no choice. But SQL can still use an index seek when appropriate. But when you use ISNULL, it can't (until/unless MS puts a special bypass in the optimizer to handle it).
Yes, I know you have no choice on the NOT EQUALS scenario. I just wanted it on the thread so that anyone reading your NEVER and IRRELEVANT statements would see that they are not absolutely correct. Otherwise they would be getting the wrong answer without warning or error, and that is by far the worst thing that can happen in data processing on any system. 😎
Huh? How does this relate to never using ISNULL in a WHERE or JOIN, with the NULL status of the underlying column being irrevelant? I didn't mention "<>".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 15, 2016 at 2:03 pm
MadAdmin (4/15/2016)
Hugo Kornelis (4/15/2016)
TheSQLGuru (4/15/2016)
Wrapping a function around a column in the WHERE clause is probably the second worst thing I see in aggregate in my consulting (with the first being mismatched data types).Then where should I place UDFs of the multi-statement and of the scalar varieties? Are they they zeroeth worst thing you've seen? Or did you just never encounter those clients?
I was also a zealot when it came to using functions on a column.
MS have obviously done some work in this regard as now I cannot anymore claim that all functions on any columns in a 'where' clause is terrible.
They have done similar work with Cast as doing a cast(intcolumn as smallint) doesnt break sargability.
Regarding the UDF's, I have had to replace a date formatting Scalar function embedded in 300 views on a data warehouse.
Server was never running more than 5% CPU and jobs were taking an age and a day when I joined the team, because, guess why?
no parallelism. 32 cores bone idle most of their lives.
I have a deep deep seated, maybe bordering on irrational, hate, and I don't mean dislike, I mean hate, of scalar functions because of that experience and would rank that as number one.
Amen. And just when I thought scalar UDFs couldn't be any more evil I went and read this: Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints[/url]
-- Itzik Ben-Gan 2001
April 17, 2016 at 12:48 pm
Alan.B (4/15/2016)
Amen. And just when I thought scalar UDFs couldn't be any more evil I went and read this: Another Hidden Parallelism Killer: Scalar UDFs In Check Constraints[/url]
Just en closing.
These tests that I attached are descriptive and not prescriptive.
Imagine being in a meeting or declining code in a code review with incorrect reasons, i.e. making a claim that all functions on a column in a where clause will definitely cause a scan and you are asked to prove it, and in that particular case, it does a seek.
Knowledge is king.
Same for CAST and looks like in some cases, CONVERT.
April 18, 2016 at 3:58 pm
TheSQLGuru (4/15/2016)
How do you code this one Scott?
WHERE SalesOrderID <> 43665
How about:
WHERE SalesOrderID < 43665 or SalesOrderID > 43665
Don Simpson
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply