December 16, 2010 at 5:10 am
CELKO (12/15/2010)
>> Now Microsoft may or may not have implemented COALESCE() by ANSI-Standards. I don't know. But given that no one can compare one unknown value (NULL) to another unknown value, I don't think using COALESCE() will help the OP. Especially since COALESCE returns NULL if all the expressions being evaluated are NULL. <<What should it return? 42? The convention in Standard SQL is that NULLs propagate -- "Ab nulo, ex nulo"
You're missing the point of my comment. He's trying to match a potentially NULL value to another value. You can't do that with a function that is designed to return NULL. NULL doesn't match anything. It simply isn't mathematically possible to match an unknown value against a known value and get an answer of any worth. Hence COALESCE() will not work and his use of ISNULL() is entirely appropriate because he's substituting a blank string for any possible NULL response.
-------Back to the beginning--------------
RE: The money. I did research after I asked that question because it really concerned me. After looking at it, and looking at other people's responses, I disagree that there's really a math problem with this data type. It's how the data type is used (usually in equations its not meant to be used in) that would cause a problem. Having worked in Accounts Payable / Receivable for most of my life before I became a DBA, I know how to use it. One doesn't go around dividing it down until it gets past the digits it needs to be unless one is prepared to lose change.
There are certainly situations where it's inadvisable to use it, such as when you need more decimal places that money allows for, but to be fair, it's a perfectly solid data type that does exactly what it should do. No more, no less. Exactly like a lot of things in SQL Server.
CELKO (12/15/2010)
>> Dialect??? Not sure what you mean by that. Please explain. <<Proprietary and not portable. CREATE INDEX is not in Standard SQL, but it is portable because of the X\Open consortium specs. Dialects just don't port and will be the first things to deprecate when the Standard is in teh next release.
So you're telling people they can't use the tools that are provided to them in the software that they use? That's just silly. Especially as most companies are so loath to change expensive software programs for another that it makes portability almost a non-issue.
CELKO (12/15/2010)
>> This [meta data affixes on data element name] is a personal preference issue. Using "T-" as a naming standards is at best annoying to type, but shouldn't cause any problems unless it's a reserved keyword issue that I'm unaware of.<<1) Violates ISO-11179 rules about data element names
I have to disagree with this one. Standards aren't standards unless everyone adopts them. You can call Blue Ray a standard if you want, it won't prevent people from buying DVD. Companies don't provide ISO rules to their programmers. Programmers aren't going to go out and buy standards books on their own. And where are the training classes? I used to work at a university that certified people in ISO standards via Conferences and other CTE classes. I can promise you that this standard was not one of them.
CELKO (12/15/2010)
2) Violates a basic rule of data modeling.
Not sure what rule you're talking about. A lot of the books I've read specifically instruct people to name tables, views, procs and functions using an enterprise-wide standard that tells everyone what they're dealing with when they're coding. In fact, I think I remember reading a Kimball paper that said the same thing.
CELKO (12/15/2010)
3) Screws up your data dictionary;
What's your definition of "data dictionary"? Mine is a report I wrote up that specifically lists tables, views, procs and functions in different areas of the report and then lists dependencies on each. I don't see how my data dictionary would be screwed up by something as simple as a name.
CELKO (12/15/2010)
4) In a language with only one data structure, it is redundant and looks as silly as putting "noun-" in front of every noun in an English sentence. It says that you are still writing BASIC, where the one-pass interpreter needed the $ tell it this variable was string and not a float.
Again, this is your personal preference, a statement of opinion, like the statement right before it. Nothing in this statement is scientific or logical. Reason 1 is the closest thing you have to an actual fact in your response. Reason 2 is skirting the line because it again depends on what books someone's been reading about data modeling.
I can see both sides of the naming argument, but nothing about either side has any "proof" that the data (or reports) will get hosed if you go one way or another on the issue. That being the case, I stand by my opinion that naming conventions are a matter of choice and the only thing that can be wrong about them is if they aren't consistently implemented across the enterprise.
December 16, 2010 at 5:11 am
WayneS (12/15/2010)
CELKO (12/15/2010)
>> There's math problems with money? Please, do tell. I haven't heard anything about them and as I use that field [sic: columns are not fields] quite a bit, I need to know. <<Actually, (when dealing with SQL Server) money is neither a field or column. Money is a data type.
Yeah, that was my bad. I found myself typing field and column all day yesterday every time I meant to type data type. I don't know what happened. DOH.
December 16, 2010 at 5:13 am
Gianluca Sartori (12/16/2010)
CELKO (12/15/2010)
3) Why are you wasting time with dynamic SQL?Actually, dynamic SQL is the most efficient way to handle optional parameters in stored procedures.
Using COALESCE, ISNULL, CASE and OR to express the same in static SQL leads to severe performance problems:
1) They all lead to index scans instead of seeks
2) Static SQL has issues with plan caching and parameter sniffing. See Erland Sommarskog's site for a more detailed explanation.
I haven't heard of performance problems with those functions in static SQL. I use CASE and OR all the time. Now I'm going to have to look this up and see what the heck you're talking about.
Will you stop making me do research, darnit!
December 16, 2010 at 6:32 am
Brandie Tarvin (12/16/2010)
Gianluca Sartori (12/16/2010)
CELKO (12/15/2010)
3) Why are you wasting time with dynamic SQL?Actually, dynamic SQL is the most efficient way to handle optional parameters in stored procedures.
Using COALESCE, ISNULL, CASE and OR to express the same in static SQL leads to severe performance problems:
1) They all lead to index scans instead of seeks
2) Static SQL has issues with plan caching and parameter sniffing. See Erland Sommarskog's site for a more detailed explanation.
I haven't heard of performance problems with those functions in static SQL. I use CASE and OR all the time. Now I'm going to have to look this up and see what the heck you're talking about.
Will you stop making me do research, darnit!
When dealing with optional parameters, you can code the expression as static SQL or dynamic SQL.
All the syntaxes to code the expression as static SQL lead to index scans, while adding the expression only when the parameter has a non-null value lets a seek happen.
Example:
-- Let's assume NULL is the default value for an optional parameter when not passed.
-- When @column_name is not passed, you want the statement to return all column names.
DECLARE @column_name varchar(128)
-- This is how you would do it if you didn't have optional parameters
-- It is implemented with an index seek
SELECT name
FROM syscolumns A
WHERE name = @column_name
-- These are some ways to code for the optional parameter.
-- They all lead to index scans
SELECT name
FROM syscolumns A
WHERE name = ISNULL(@column_name, name)
SELECT name
FROM syscolumns A
WHERE name = COALESCE(@column_name, name)
SELECT name
FROM syscolumns A
WHERE name = @column_name OR @column_name IS NULL
SELECT name
FROM syscolumns A
WHERE name = CASE WHEN @column_name IS NULL THEN name ELSE @column_name END
-- This is how you would do it with dynamic SQL
-- The filter expression is added only when needed and goes for and index seek
DECLARE @sql varchar(8000)
SET @sql = 'SELECT name FROM syscolumns A '
IF @column_name IS NOT NULL
SET @sql = @sql + ' WHERE name = @column_name '
EXEC sp_executesql @sql, N'@column_name varchar(128)', @column_name
See what I mean?
I don't want to go into deep details, Erland did it much better than I could ever do.
-- Gianluca Sartori
December 16, 2010 at 10:57 am
Brandie Tarvin (12/16/2010)[hrYou're missing the point of my comment. He's trying to match a potentially NULL value to another value. You can't do that with a function that is designed to return NULL. NULL doesn't match anything. It simply isn't mathematically possible to match an unknown value against a known value and get an answer of any worth. Hence COALESCE() will not work and his use of ISNULL() is entirely appropriate because he's substituting a blank string for any possible NULL response.
I am not following you, but I guess by this comment and your pervious one about ISNULL versus COALESCE, it seems you do not understand them.
The OP was using ISNULL to return a non-null value (ie - INSULL(ColName, 0) or ISNULL(@Varibale, '') etc..). So just like any function if you do not use it right it won't work right. If you replaced ISNULL for COALESCE you'd still get the same results. So, again, I'm confused at what point you are trying to make? Can COALESCE return NULL? Yes is can. But, if used appropriatly, it won't and, furthermore, as Joe mentioned its ANSI compliant. So, why would use use anything else?
I'll also add my disgust with ISNULL because of it's difference with the other IS... functions.. ISDATE returns a boolean... ISNUMERIC returns a boolean.. All is good witht he world.. ISNULL returns.. wait what? the first non-null values? WTF?
I have to disagree with this one. Standards aren't standards unless everyone adopts them. You can call Blue Ray a standard if you want, it won't prevent people from buying DVD. Companies don't provide ISO rules to their programmers. Programmers aren't going to go out and buy standards books on their own. And where are the training classes? I used to work at a university that certified people in ISO standards via Conferences and other CTE classes. I can promise you that this standard was not one of them.
Partial true.. If people don't use a STANDARD doesn't make it not a standard.. (wait is that english?) 🙂
I hand out standards to the people I work with. Inclusing ISO standards. If I'm in charge they will follow them. If I'm not in charge, I'll attempt to help the person in charge understand why we should use them. Usually it's an uphill battle because if they don't want to support a coding standard then they ussuall don't even understand the difference between 1st and 2nd normal form. So, baby steps.
Not sure what rule you're talking about. A lot of the books I've read specifically instruct people to name tables, views, procs and functions using an enterprise-wide standard that tells everyone what they're dealing with when they're coding. In fact, I think I remember reading a Kimball paper that said the same thing.
Anything you've read by Kimball should be forgotten. If you still have the book you read that in (assuming it was a book) you should burn it now to make sure it doesn't infect any other minds. That man has propbably done more damage to the data industry and any other person on the planet. Brining up his name to people that actuall undertand relation theory is not going to help your arguments at all.
December 16, 2010 at 11:00 am
Lamprey13 (12/16/2010)
Anything you've read by Kimball should be forgotten. If you still have the book you read that in (assuming it was a book) you should burn it now to make sure it doesn't infect any other minds. That man has propbably done more damage to the data industry and any other person on the planet. Brining up his name to people that actuall undertand relation theory is not going to help your arguments at all.
*blink*
December 16, 2010 at 11:30 am
Lamprey13 (12/16/2010)
Brandie Tarvin (12/16/2010)[hrYou're missing the point of my comment. He's trying to match a potentially NULL value to another value. You can't do that with a function that is designed to return NULL. NULL doesn't match anything. It simply isn't mathematically possible to match an unknown value against a known value and get an answer of any worth. Hence COALESCE() will not work and his use of ISNULL() is entirely appropriate because he's substituting a blank string for any possible NULL response.
I am not following you, but I guess by this comment and your pervious one about ISNULL versus COALESCE, it seems you do not understand them.
I understand them just fine. COALESCE does not give me the answer I seek. ISNULL does.
Create Table dbo.#Table1 (OrderID int, SalesID int);
Insert into dbo.#Table1 (OrderID, SalesID)
(Select 1,1
UNION ALL
Select 2,3
UNION ALL
Select 3,3
UNION ALL
Select 4,NULL
UNION ALL
Select 5,2);
Declare @SalesmanID int;
Set @SalesManID = 3;
Select t1.SalesID, t1.OrderID
FROM dbo.#Table1 t1
WHERE ISNULL(t1.SalesID,'') = @SalesManID;
Select t1.SalesID, t1.OrderID
FROM dbo.#Table1 t1
WHERE COALESCE(t1.SalesID, @SalesManID) = @SalesManID;
To make my point even further, if using ISNULL() vs COALESCE in a JOIN statement:
Create Table dbo.#Table1 (SalesID int, Salesman varchar(10));
Create Table dbo.#Table2 (OrderID int, SalesID int);
Insert into dbo.#Table1 (SalesID, Salesman)
(Select 1, 'Jeff'
UNION ALL
Select 2, 'Mary'
UNION ALL
Select 3, 'Bo');
Insert into dbo.#Table2 (OrderID, SalesID)
(Select 1,1
UNION ALL
Select 2,3
UNION ALL
Select 3,3
UNION ALL
Select 4,NULL
UNION ALL
Select 5,2);
Declare @SalesmanID int;
Set @SalesManID = 3;
Select t1.Salesman, t2.OrderID
FROM dbo.#Table2 t2
LEFT OUTER JOIN dbo.#Table1 t1
ON ISNULL(t2.SalesID,'') = t1.SalesID;
Select t1.Salesman, t2.OrderID
FROM dbo.#Table2 t2
LEFT OUTER JOIN dbo.#Table1 t1
ON COALESCE(t2.SalesID, @SalesManID) = t1.SalesID;
December 16, 2010 at 11:57 am
Brandie Tarvin (12/16/2010)
Lamprey13 (12/16/2010)
Brandie Tarvin (12/16/2010)[hrYou're missing the point of my comment. He's trying to match a potentially NULL value to another value. You can't do that with a function that is designed to return NULL. NULL doesn't match anything. It simply isn't mathematically possible to match an unknown value against a known value and get an answer of any worth. Hence COALESCE() will not work and his use of ISNULL() is entirely appropriate because he's substituting a blank string for any possible NULL response.
I am not following you, but I guess by this comment and your pervious one about ISNULL versus COALESCE, it seems you do not understand them.
I understand them just fine. COALESCE does not give me the answer I seek. ISNULL does.
<snip>
To make my point even further, if using ISNULL() vs COALESCE in a JOIN statement:
I still don't understand what you are showing. You are using them in different ways. So yeah, you are going to get different results.
Try using ISNULL and COALESCE in similar ways and you get the same result:Select t1.SalesID, t1.OrderID
FROM dbo.#Table1 t1
WHERE ISNULL(t1.SalesID,'') = @SalesManID;
Select t1.SalesID, t1.OrderID
FROM dbo.#Table1 t1
WHERE COALESCE(t1.SalesID, '') = @SalesManID;
Perhaps I'm not understanding what you are trying to demonstrate, so feel free to elaborate. But, it still seems like you don't understand the similarities and differences between the two.
December 16, 2010 at 12:04 pm
Lamprey13 (12/16/2010)
I'll also add my disgust with ISNULL because of it's difference with the other IS... functions.. ISDATE returns a boolean... ISNUMERIC returns a boolean.. All is good witht he world.. ISNULL returns.. wait what? the first non-null values?
I wouldn't go so far as to say "disgust", but I do agree that the name for this function is not particularly well suited for what it does.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 17, 2010 at 12:33 pm
I won't use COALESCE, because it's broken:
SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
SELECT ISNULL((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
The final 999 value in both forms should ensure the overall result is never NULL, right?
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 17, 2010 at 2:48 pm
True that it is dialect. Guess what? This site is about sql server and it's dialect. It is not called sql programming for ISO compliant dba. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2010 at 4:34 pm
Paul White NZ (12/17/2010)
I won't use COALESCE, because it's broken:
SELECT COALESCE((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
SELECT ISNULL((SELECT CASE WHEN RAND() <= 0.5 THEN 999 END), 999);
The final 999 value in both forms should ensure the overall result is never NULL, right?
Paul
Interesting, can you produce a NULL by using something other than the RAND() function?
I never use the two (SELECT and RAND()) in a case expression anyway. But, I'm curious what casues the issue.
December 17, 2010 at 4:36 pm
CELKO (12/17/2010)
Yep, it is time to report a bug! That second SELECT inside the parameter list is dialect and ought to blow up, too.
Serious question:
Should scalar subqueries not be allowed in COALESCE? It's just a sugar-coated CASE statement, and I'm sure subqueries are allowed there. I honestly do not know the answer to this.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
December 17, 2010 at 4:50 pm
Lamprey13 (12/17/2010)
Interesting, can you produce a NULL by using something other than the RAND() function?
Sure. That's was just a compact way to demo the issue. Pretty much any subquery that accesses data can be made to do the same thing.
This is a bug Hugo found, see http://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null
It's a nasty bug to fix, so workarounds or avoidance are the best bet.
I was being a little cheeky before: I have been known to use COALESCE (carefully avoiding this bug, and other 'issues') but not because I want to write portable code (I couldn't care less about that). No, sometimes it is just more convenient than ISNULL. Each has its pros and cons in SQL Server, but I find myself working with ISNULL much more often.
An underrated advantage of ISNULL is that it cannot return NULL. The optimizer knows this, and all sorts of cool optimizations are possible with a column that is guaranteed not to contain NULL from some point in an execution plan forward.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply