February 2, 2012 at 7:06 am
good article ..can be used as a best practices for developer.. thumbs up ! barua
February 2, 2012 at 7:07 am
skippybiff (2/2/2012)
Isn't COALESCE ANSI Standard?
Yes.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 7:09 am
I am in the habit of using COALESCE for such things, mainly because I also use MySql, which doesn't have ISNULL. But everyone should read the link that SQL Kiwi posted (https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null) and grok what COALESCE does.
February 2, 2012 at 7:10 am
I agree, COALESCE does have issues when nesting queries. If you decide to use the function it's probably best to select the case statement into a variable first and run COALESCE with the value not the query. That resolves the issue in this case at least.
Knowing it has issues with what would appear to be basic TSQL makes me wonder if it should be trusted in production environments....
February 2, 2012 at 7:14 am
When you rate SSC articles, there are five possible star ratings: awful, poor, average, good, and excellent. I went for poor, on the basis that it isn't awful, but I hope it isn't as good as average either 😉
Ah I see, then I would have to agree.
February 2, 2012 at 7:25 am
SQL Kiwi (2/2/2012)
carlos.magno (2/2/2012)
The "BIG" problems that this Microsoft articles describes with Coalesce are just if you are using a subquery inside the function.But I guess that is not the most common use of this function.
So I will continue using COALESCE instead of ISNULL.
Spot the subquery:
SELECT COALESCE(CASE WHEN RAND() <= 0.5 THEN 999 END, 999);Explain how that should return NULL from time to time (as it does).
Awesome example! It illustrates the point that the problem is not just with subqueries but with non-deterministic values in general, which subquery values are, per ANSI. The non-null value is evaluated TWICE, just as it would be in the equivalent CASE.
February 2, 2012 at 7:28 am
@SQL Kiwi: Ohhh, I get it now...because RAND is non-deterministic it will return different values in the various CASE statement branches.
Thanks for explaining it further!
George
February 2, 2012 at 7:55 am
George H. (2/2/2012)
Thanks for explaining it further!
No worries. While we're chatting, you might find this interesting too:
DECLARE @Example AS TABLE (col1 integer PRIMARY KEY);
-- Seek
SELECT * FROM @Example AS e WHERE ISNULL(col1, 1000) = 5;
-- Scan
SELECT * FROM @Example AS e WHERE COALESCE(col1, 1000) = 5
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 2, 2012 at 9:37 am
SQL Kiwi (2/2/2012)
George H. (2/2/2012)
Thanks for explaining it further!No worries. While we're chatting, you might find this interesting too:
DECLARE @Example AS TABLE (col1 integer PRIMARY KEY);
-- Seek
SELECT * FROM @Example AS e WHERE ISNULL(col1, 1000) = 5;
-- Scan
SELECT * FROM @Example AS e WHERE COALESCE(col1, 1000) = 5
WHAT A TWIST!!! 😉
Thanks for schooling us n00bZ SQL Kiwi! :hehe:
And I would agree, the article was not terrible, but not great. To mtassin's point, As soon as I saw the temp table with the static values I was like "Say Whu???" Granted, I understand this was just a means to quickly demonstrate a point about IsNull (which as other's have already pointed out, is not necessarily article-worthy given MSDN should be your bible for SQL Server 101... but I digress).
In any case, hopefully no one actually got the impression that this was a best practices article on anything other than "How NOT to use ISNULL" 😛
February 2, 2012 at 11:25 am
Mark Hill-285393 (2/2/2012)
I am in the habit of using COALESCE for such things, mainly because I also use MySql, which doesn't have ISNULL. But everyone should read the link that SQL Kiwi posted (https://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null) and grok what COALESCE does.
On some SQL database engines, even the behaviour of NULL doesn't conform to ANSI standards. When I first started working with Oracle, I kept tripping over the fact that an empty string '' IS NULL, meaning that '' is not equal to ''. Welll, actually '' != '' isn't true either, because it's really NULL, but you get the point.
/* Disclaimer: This is the world according to Oracle, not SQL Server */
select case when '' is null then 'True' else 'False' end from dual;
select case when '' = '' then 'True' else 'False' end from dual;
select case when '' != '' then 'True' else 'False' end from dual;
CASEWHEN''ISNULLTHEN'TRUE'ELSE'FALSE'END
----------------------------------------
True
CASEWHEN''=''THEN'TRUE'ELSE'FALSE'END
-------------------------------------
False
CASEWHEN''!=''THEN'TRUE'ELSE'FALSE'END
--------------------------------------
False
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 2, 2012 at 12:02 pm
Eric M Russell (2/2/2012)
On some SQL database engines, even the behaviour of NULL doesn't conform to ANSI standards. When I first started working with Oracle, I kept tripping over the fact that an empty string '' IS NULL, meaning that '' is not equal to ''. Welll, actually '' != '' isn't true either, because it's really NULL, but you get the point.
Bizarre! I hope I never have to use Oracle! If I do, it looks like I'll be spending a lot of time "debugging gravity!"
February 2, 2012 at 3:17 pm
@Paul White
-- Seek
SELECT * FROM @Example AS e WHERE ISNULL(col1, 1000) = 5;
I'm just learning this stuff, I don't pretend to know what I'm talking about, and maybe this is a bit off topic anyway, but another consideration about isnull is its use in the WHERE clause or JOINs. I don't get how a non-SARGable function like ISNULL allows a SEEK in this example.
At our business, we frequently rely on date columns in which an unfinished event is represented by a NULL. People tell me the use ISNULL in the WHERE clause because "...it's quicker to type..." For the sake of typing a few characters it looks to me like we can sacrifice performance. How much we sacrifice depends on the indexes and the query. I'm not passing this off as a fact, merely as a hypothesis for discussion at this stage.
My basic hypothesis is that this:
WHERE (SomeDateTimeWithNulls >= @end_date OR SomeDateTimeWithNulls is null) will often be quicker and rarely be slower than this:
WHERE isnull(SomeDateTimeWithNulls,@end_date) >= @end_date
To support my hypothesis I use Jeff Moden's million row table script and Adam Machanic's SQLQueryStress tool set to 6 iterations and 6 threads. Here's the code:
/**********************************************************************************************************************
Purpose:
Create a voluminous test table with various types of highly randomized data.
--Jeff Moden
--GPO: In this case I have used a gobal temp test table so that I can see it with my SQLQueryStress queries
**********************************************************************************************************************/
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('tempdb..##test') IS NOT NULL
DROP TABLE ##test;
--===== Create and populate a 1,000,000 row test table.
-- "SomeID" has a range of 1 to 1,000,000 unique numbers
-- "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- "SomeMoney has a range of 0.00 to 100.00 non-unique numbers
-- "SomeDateTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times
-- GPO: "SomeDateTimeWithNulls" set a certain random proportion of these dates to null
-- "SomeDate" has a range of >=01/01/2000 and <01/01/2020 non-unique "whole dates"
-- "SomeName" contains random characters at random lengths from 2 to 20 characters
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))
+ CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')),
SomeMoney = CAST(RAND(CHECKSUM(NEWID())) * 100 AS DECIMAL(9,2)), --Note rounding
SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeDateTimeWithNulls = case when ABS(CHECKSUM(NEWID())) % 100 = 1
then NULL
else RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME)
end,
SomeDate = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 19 + 2)
INTO ##test
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
ALTER TABLE ##test
ADD CONSTRAINT PK_##test PRIMARY KEY CLUSTERED (SomeID) WITH FILLFACTOR = 90
;
create unique nonclustered index ix_##test2 on ##test (SomeDateTimeWithNulls asc,SomeInt asc,SomeID)
include (SomeLetters2)
;
/**********************************************************************************************************************
Purpose: My testing queries for use in Adam Machanic's SQLStressTest http://www.datamanipulation.net/sqlquerystress/
--GPO
**********************************************************************************************************************/
--------- Get the values for the parameter substitution query in SQLStressTest
SELECT top 36 SomeDateTime
FROM ##test
WHERE SomeDateTime > '20171201'
ORDER BY SomeInt
--------- -------------------------------------------------------------------------------------------------
SELECT SomeLetters2
,SomeDateTimeWithNulls
,SomeInt
FROM ##test
WHERE (SomeDateTimeWithNulls >= @end_date OR SomeDateTimeWithNulls is null)
and SomeInt > '45000'
--------- -------------------------------------------------------------------------------------------------
SELECT SomeLetters2
,SomeDateTimeWithNulls
,SomeInt
FROM ##test
WHERE isnull(SomeDateTimeWithNulls,@end_date) >= @end_date
and SomeInt > '45000'
The average actual seconds per iteration (if I'm doing this right:unsure:) is about 0.0338 without the ISNULL and 0.1273 with the ISNULL. And the isnull results in a scan whereas without the isnull results in a merge interval and a seek. For the sake of 17 characters there's a big difference (in this example, which I don't think is too contrived).
Cheers
GPO
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
February 4, 2012 at 3:14 pm
CELKO (2/2/2012)
You misssed a lot.
Maybe so but the article had a single purpose which was mostly covered.
ISNULL takes only 2 parameters whereas COALESCE takes variable number of parameters
Something else might be an issue but only having 2 parameters isn't really an issue if you only need 2 parameters. 😉
COALESCE correctly promotes its result to the highest data type in the expression list
13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5
The proprietary ISNULL() uses the first data type and gets things wrong
13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6
You would need to write:
13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)
That's quite valuable but only if you need for the conversion to be done. One might also say that ISNULL has an advantage of always returning the datatype of the first parameter.
As with anything else in SQL, "It Depends". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 4, 2012 at 3:18 pm
What can I say? I was looking to see if anything else needed to be added and you've covered it all. Nicely done.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply