June 6, 2014 at 3:27 pm
A recent post just reminded me of one of my greatest pet peeves... people that correct your English, spelling, or punctuation on forum posts. I've also found that they usually do that when they consider it beneath them to defend any point that they've been trying to make or can't actually do so.
--Jeff Moden
June 7, 2014 at 10:54 am
I'm not sure if I'm even allowed to have a pet peeve as I have only been doing this a year. However one thing that has bothered me is every time I go into a certain developers queries I have noticed that they constantly do
a ISNULL check on Non-Nullable columns. Actaully they seem to apply it to every column without discretion.
Example: ISNULL(col1,'0') Where col1's constraint/properties does not allow nulls to begin with.
Is this even a legit complaint? I guess what irritates me is the fact that they never seem to check the column properties before coding all of that clutter. Which means in my mind that they probably don't check a great many other things.
***SQL born on date Spring 2013:-)
June 7, 2014 at 11:16 am
thomashohner (6/7/2014)
I'm not sure if I'm even allowed to have a pet peeve as I have only been doing this a year. However one thing that has bothered me is every time I go into a certain developers queries I have noticed that they constantly doa ISNULL check on Non-Nullable columns. Actaully they seem to apply it to every column without discretion.
Example: ISNULL(col1,'0') Where col1's constraint/properties does not allow nulls to begin with.
Is this even a legit complaint? I guess what irritates me is the fact that they never seem to check the column properties before coding all of that clutter. Which means in my mind that they probably don't check a great many other things.
Very fortunately, SQL Server will frequently ignore an ISNULL on a non-nullable column but I agree... it makes for unnecessary clutter. And I also agree that doing things by rote like this means that haven't checked on a whole lot of other things in the name of "getting it done". It's part of the reason why there are so many performance problems.
Although nullability works differently for most applications outside of the defaults for SSMS (which explains why some front-enders do the following), a couple of my "favorite" mistakes, even when a column is nullable, are...
WHERE ISNULL(somecol,' ') <> ' '
... or ...
WHERE ISNULL(somecol,0) <> 0
--Jeff Moden
June 7, 2014 at 11:36 am
Very fortunately, SQL Server will frequently ignore an ISNULL on a non-nullable column but I agree... it makes for unnecessary clutter. And I also agree that doing things by rote like this means that haven't checked on a whole lot of other things in the name of "getting it done". It's part of the reason why there are so many performance problems.
Although nullability works differently for most applications outside of the defaults for SSMS (which explains why some front-enders do the following), a couple of my "favorite" mistakes, even when a column is nullable, are...
WHERE ISNULL(somecol,' ') <> ' '... or ...
WHERE ISNULL(somecol,0) <> 0
Your examples are exactly some of the things I see in these queries.
***SQL born on date Spring 2013:-)
June 7, 2014 at 12:07 pm
Jeff, I'm so sorry this may be a very dumb question but in your examples when the column is NULLABLE why is that expression wrong? I just tested it on a NULLABLE column and it seems to work. However I do know what seems to work and what works are two different things. Sorry about my confusion. Looking for some insight and education on this.
Thomas
This is what I ran:
USE Thomas_Test
CREATE TABLE NULL_TEST (Column1 VARCHAR(5) NOT NULL, Column2 Numeric(2) NULL)
GO
INSERT INTO NULL_TEST VALUES ('test1', 2),('test3',null),('test4','5'),('test5','6')
GO
SELECT *
FROM NULL_TEST
WHERE ISNULL(Column2,0) <> 0
SELECT *
FROM NULL_TEST
GO
DROP TABLE NULL_TEST
***SQL born on date Spring 2013:-)
June 7, 2014 at 12:58 pm
thomashohner (6/7/2014)
Jeff, I'm so sorry this may be a very dumb question but in your examples when the column is NULLABLE why is that expression wrong? I just tested it on a NULLABLE column and it seems to work. However I do know what seems to work and what works are two different things. Sorry about my confusion. Looking for some insight and education on this.Thomas
This is what I ran:
USE Thomas_TestCREATE TABLE NULL_TEST (Column1 VARCHAR(5) NOT NULL, Column2 Numeric(2) NULL)
GO
INSERT INTO NULL_TEST VALUES ('test1', 2),('test3',null),('test4','5'),('test5','6')
GO
SELECT *
FROM NULL_TEST
WHERE ISNULL(Column2,0) <> 0
SELECT *
FROM NULL_TEST
GO
DROP TABLE NULL_TEST
Added another query for you to look at.
CREATE TABLE NULL_TEST (Column1 VARCHAR(5) NOT NULL, Column2 Numeric(2) NULL)
GO
INSERT INTO NULL_TEST VALUES ('test1', 2),('test3',null),('test4','5'),('test5','6')
GO
SELECT *
FROM NULL_TEST
WHERE ISNULL(Column2,0) <> 0
SELECT *
FROM NULL_TEST
WHERE Column2 <> 0
SELECT *
FROM NULL_TEST
GO
DROP TABLE NULL_TEST;
June 7, 2014 at 1:07 pm
Now I'm thoroughly confused? Why does that work? I thought NULL wasn't equal to anything. :blush:
***SQL born on date Spring 2013:-)
June 7, 2014 at 1:44 pm
thomashohner (6/7/2014)
Now I'm thoroughly confused? Why does that work? I thought NULL wasn't equal to anything. :blush:
Not only is it not equal to anything, it also is not unequal to anything either.
But I suspect that the issue Jeff was getting at was that "where isnull(col,0) <> 0) is presumably intended to pass all rows which don't have null in that column but if doesn't pass rows which are not null if they just ha[en to have 0 in that column, so as a way of passing all rows which don't have null in col it just doesn't work.
Tom
June 7, 2014 at 2:35 pm
Further Lynn's demonstration, here are few different ways the SQL Server handles NULL test (using Lynn's data).
😎
select * from
(
SELECT Column1, coalesce(Column2,0.0) as Column2
FROM NULL_TEST
) as x where x.Column2 <> 0
/*
[Expr1003] = Scalar Operator(CASE WHEN [tempdb].[dbo].[NULL_TEST].[Column2] IS NOT NULL THEN CONVERT_IMPLICIT(numeric(3,1),[tempdb].[dbo].[NULL_TEST].[Column2],0) ELSE (0.0) END)
*/
select * from
(
SELECT Column1, coalesce(Column2,0.0) as Column2
FROM NULL_TEST
) as x where x.Column2 = 0
/*
[Expr1003] = Scalar Operator(CASE WHEN [tempdb].[dbo].[NULL_TEST].[Column2] IS NOT NULL THEN CONVERT_IMPLICIT(numeric(3,1),[tempdb].[dbo].[NULL_TEST].[Column2],0) ELSE (0.0) END)
*/
select * from
(
SELECT Column1, isnull(Column2,0.0) as Column2
FROM NULL_TEST
) as x where x.Column2 = 0
/*[Expr1003] = Scalar Operator(isnull([tempdb].[dbo].[NULL_TEST].[Column2],(0.)))
*/
select * from
(
SELECT Column1, isnull(Column2,0.0) as Column2
FROM NULL_TEST
) as x where x.Column2 != 0
/*
[Expr1003] = Scalar Operator(isnull([tempdb].[dbo].[NULL_TEST].[Column2],(0.)))
*/
select * from
(
SELECT Column1, isnull(Column2,0.0) as Column2
FROM NULL_TEST
) as x where x.Column2 <> 0
/*
[Expr1003] = Scalar Operator(CASE WHEN [tempdb].[dbo].[NULL_TEST].[Column2] IS NOT NULL THEN CONVERT_IMPLICIT(numeric(3,1),[tempdb].[dbo].[NULL_TEST].[Column2],0) ELSE (0.0) END)
*/
SELECT
Column1
,ISNULL(Column2,0) AS Column2
FROM NULL_TEST
WHERE Column2 <> 0
/*
[Expr1003] = Scalar Operator([tempdb].[dbo].[NULL_TEST].[Column2])
*/
SELECT
Column1
,CASE
WHEN Column2 IS NULL THEN 0
ELSE Column2
END AS Column2
FROM NULL_TEST
WHERE Column2 <> 0
/*
[Expr1003] = Scalar Operator(CASE WHEN [tempdb].[dbo].[NULL_TEST].[Column2] IS NULL THEN (0.) ELSE [tempdb].[dbo].[NULL_TEST].[Column2] END)
*/
SELECT
Column1
,CASE
WHEN Column2 IS NULL THEN 0
ELSE Column2
END AS Column2
FROM NULL_TEST
WHERE Column2 = 0
/*
[Expr1003] = Scalar Operator(CASE WHEN [tempdb].[dbo].[NULL_TEST].[Column2] IS NULL THEN (0.) ELSE [tempdb].[dbo].[NULL_TEST].[Column2] END)
*/
SELECT
Column1
,CASE
WHEN Column2 IS NULL THEN 0
ELSE Column2
END AS Column2
FROM NULL_TEST
WHERE Column2 != 0
/*
[Expr1003] = Scalar Operator(CASE WHEN [tempdb].[dbo].[NULL_TEST].[Column2] IS NULL THEN (0.) ELSE [tempdb].[dbo].[NULL_TEST].[Column2] END)
*/
June 7, 2014 at 8:14 pm
thomashohner (6/7/2014)
Jeff, I'm so sorry this may be a very dumb question but in your examples when the column is NULLABLE why is that expression wrong? I just tested it on a NULLABLE column and it seems to work. However I do know what seems to work and what works are two different things. Sorry about my confusion. Looking for some insight and education on this.Thomas
The code does NOT come up with the wrong answer. As you point out, it comes up with the technically accurate answer. So, let's look at your WHERE clause...
WHERE ISNULL(Column2,0) <> 0
What does that code actually do?
Quite literally, is says that it should treat all nulls as if they had the value of 0 and then return things that don't have a value of 0.
Now for the question behind the magic. If the WHERE clause is written like the following...
WHERE Column2 <> 0
... why doesn't it return the rows where Column2 is NULL?
Once you figure that out, then tell me why this...
WHERE ISNULL(Column2,0) <> 0
... is worse than this ...
WHERE Column2 <> 0
I'm not giving the answer directly because you'll remember this forever if you have your own "Eureka" momement.
--Jeff Moden
June 7, 2014 at 8:35 pm
Oh dear god this will consume me tomorrow trying to figure this out. But I agree letting me try to do this on my own will make it stick. I'm glad I didn't get scared and posted what I did not understand. I just may learn something here. I will get back to you fine gents when I have a answer!
***SQL born on date Spring 2013:-)
June 8, 2014 at 8:05 am
Okay I really feel stupid after looking at this some more. :blush:
WHERE Column <>0
This "works" because NULLS are part of the three valued predicate logic. True, False and Unknown. NULLS are neither equal nor not equal to anything so they are excluded anytime the predication is =,<>
Hence this will only return values <> 0 since NULLS are part of the Unknown predicate they don't return either.
So when predicating to True, False and Unknown are excluded. When predicating to False, True and Unknown are excluded.
As far as WHERE ISNULL(Column1, 0) <> 0
This is not as good because calling on a function in the WHERE clause makes a Index useless. Does this also mean that ISNULL is processed like a SCALAR function a row at a time?
I'm sure there's more and I'll continue researching. But am I getting warmer?
***SQL born on date Spring 2013:-)
June 8, 2014 at 9:41 am
thomashohner (6/8/2014)
Okay I really feel stupid after looking at this some more. :blush:
I very much appreciate your humility here because it's one of those things that I hold most dear in Developers and DBAs. It sometimes makes a huge difference in being "just" a DBA and being an "Exceptional DBA". But, there's no need to feel "stupid" here. It's something that everyone has a problem with at one time or another, present company (ME!) being no exception.
WHERE Column <>0
This "works" because NULLS are part of the three valued predicate logic. True, False and Unknown. NULLS are neither equal nor not equal to anything so they are excluded anytime the predication is =,<>
Hence this will only return values <> 0 since NULLS are part of the Unknown predicate they don't return either.
So when predicating to True, False and Unknown are excluded. When predicating to False, True and Unknown are excluded.
As far as WHERE ISNULL(Column1, 0) <> 0
This is not as good because calling on a function in the WHERE clause makes a Index useless. Does this also mean that ISNULL is processed like a SCALAR function a row at a time?
Heh... I'll say it for you... EUREKA! 🙂
I'm sure there's more and I'll continue researching. But am I getting warmer?
There always will be and yes, you've pretty much nailed what I was getting at. Even when working with "NULL comparisons" today, I still setup small tests to make sure that NULLs aren't going to bite me because I still make mistakes if I'm in a hurry. The tests not only slow me down to think about it, they prove to me that the criteria that I'm about to drop into production will actually work as expected. For example of this case...
WITH
cteNullTest(N) AS
(
SELECT -2 UNION ALL
SELECT -1 UNION ALL
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT NULL
)
SELECT *
FROM cteNullTest
WHERE N <> 0
;
... and I KNOW I'd make such mistakes without such a test because I've done so in an area just as critical as making such a mistake in prod... on forums where people rely on stuff like this being right. :blush:
There is one thing, though. Once I've spent the time to figure out such a thing, I remember that others may not spend the time to figure things out or they just might not know about how NULLs work and don't work. With that in mind, I take the time to write some "horribly complicated code" that a lot of people are dead set against because it "slows them down too much" even though it makes the intent of the code crystal clear for those that don't understand NULLs. Heh... you ready for this?
WHERE Column <> 0 --Is NOT NULL and is NOT ZERO
😛
--Jeff Moden
June 8, 2014 at 10:17 am
Thanks for the feedback Jeff,
This became a very valuable lesson in my eyes. I am already wanting to go down to the office and open up some of my previous queries because of this understanding of NULLS. I want to double check my logic where NULLS may play a role.
I have read several SQl books most aimed at beginners to intermediate levels. However only the most recent that I just started reading explained NULLS as part of the 3 part predicate logic in detail. The others just implied it by saying it doesn't equal nor not equal.
It was a definite aha moment:-D
P.S sorry for jacking the thread on a side tangent:-P
***SQL born on date Spring 2013:-)
June 9, 2014 at 8:24 am
Luis Cazares (6/6/2014)
djj (6/6/2014)
And I'm not obfuscating the names. :crazy:
But the meaning is obvious
Ambivalently named table that will take 60 years to recover from the mental scarring when trying to deliver
Viewing 15 posts - 166 through 180 (of 271 total)
You must be logged in to reply to this topic. Login to reply