June 24, 2012 at 12:17 pm
I thought COALESCE had 2 parameters. http://www.sqlservercentral.com/Forums/Post.aspx?SessionID=zfj3ym55ovoqokutkrrawu55
COALESCE ( expression [ ,...n ] )
I don't understand this example. It has 3 parameters.
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
June 24, 2012 at 12:38 pm
ReginaR1975 (6/24/2012)
I thought COALESCE had 2 parameters. http://www.sqlservercentral.com/Forums/Post.aspx?SessionID=zfj3ym55ovoqokutkrrawu55COALESCE ( expression [ ,...n ] )
I don't understand this example. It has 3 parameters.
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
http://msdn.microsoft.com/en-us/library/ms190349.aspx
In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and commission.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 24, 2012 at 4:23 pm
COALESCE allows an unlimited number of parameters and returns the value of the first expression, evaluated from left to right, that is not NULL.
See BOL on MSDN
June 24, 2012 at 4:29 pm
Thank you. I thought it was just 2 parameters.
June 25, 2012 at 6:19 am
Hi Regina,
Please try to search the syntax on BOL or do little Google for that before posting it to this forum.
As many of other useful queries remain ananswered due to the shortage of time for the SSC champs who are there to help us.:-)
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 25, 2012 at 7:32 am
^^^^ This is exactly the kind of attitude we DON'T want to have in these forums. I'm sorry @bhuvneshk, but we don't want to confuse laziness with misinterpretation and stiffle the desire to learn and understand. I believe that Regina simply didn't understand what the command did, asked a question, and learned something in the process. The two answers were offered in that format. Could the lesson have been learned by a more detailed trip through the BOL? Possibly, but there's no indication that it wouldn't have, either. At least this was a "help me understand" request, not a "do my homework for me" demand!
June 25, 2012 at 10:47 am
bhuvneshk (6/25/2012)
Hi Regina,Please try to search the syntax on BOL or do little Google for that before posting it to this forum.
As many of other useful queries remain ananswered due to the shortage of time for the SSC champs who are there to help us.:-)
This comment is totally UNCALLED FOR let each SSC champ/expert or just another member with enough knowledge make their own decision to answer the question.
We are a community of individuals, helping each other.
June 25, 2012 at 11:26 am
Nah. It's a perfectly good question.
Another thing to keep in mind about IsNull vs Coalesce is that Coalesce is ISO-standard SQL, and has some slight advantages because of that.
However, since IsNull has a fixed number of parameters, it is microscopically faster than Coalesce. I once ran a billion-row test on the two, and IsNull was consistently marginally faster. Not enough to matter in most cases, but measurable. That makes sense from the standpoint of less code at the engine-level, since it doesn't have to figure out how many parameters it's checking.
I've seen a lot of people get confused about those two. So it's a reasonable question.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2012 at 6:17 pm
GSquared (6/25/2012)
Nah. It's a perfectly good question.Another thing to keep in mind about IsNull vs Coalesce is that Coalesce is ISO-standard SQL, and has some slight advantages because of that.
However, since IsNull has a fixed number of parameters, it is microscopically faster than Coalesce. I once ran a billion-row test on the two, and IsNull was consistently marginally faster. Not enough to matter in most cases, but measurable. That makes sense from the standpoint of less code at the engine-level, since it doesn't have to figure out how many parameters it's checking.
I've seen a lot of people get confused about those two. So it's a reasonable question.
I agree with that since I'm coming from a C/C++ background. You'd have to push the number of arguments on the stack, then the called routine needs to be aware where the bounds are. Much simpler if you know there will be exactly two.
BTW, does any of you know what language the SQL engine is written in? Don't say lmgtfy 🙂
June 25, 2012 at 6:35 pm
Unless they coded ISNULL(a,b) as a wrapper around COALESCE(x1,x2,...,xn), which I would probably have done, just from a maintainability point of view. Then ISNULL should actually be slower due to the extra call and push of parameters.
BTW, Gus, why do you spell ISNULL as IsNull? Looks very like C style of coding? Just wondering.
June 25, 2012 at 10:56 pm
bitbucket-25253 (6/25/2012)
bhuvneshk (6/25/2012)
Hi Regina,Please try to search the syntax on BOL or do little Google for that before posting it to this forum.
As many of other useful queries remain ananswered due to the shortage of time for the SSC champs who are there to help us.:-)
This comment is totally UNCALLED FOR let each SSC champ/expert or just another member with enough knowledge make their own decision to answer the question.
We are a community of individuals, helping each other.
I don't want to discourage anybody.Every member is having right to ask any question.But now a days I have seen posts from SSC champs as well about the quality of question asked in this forum and I do agree with them.We can always find the syntax of SQL inbuilt function on BOL or somewhere else easily.Though I put the comment in good health still I feel really sorry if somebody got offended by the comment.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 26, 2012 at 4:23 am
CELKO (6/25/2012)
Jan Van der Eecken (6/25/2012)
Unless they coded ISNULL(a,b) as a wrapper around COALESCE(x1,x2,...,xn), which I would probably have done, just from a maintainability point of view. Then ISNULL should actually be slower due to the extra call and push of parameters.COALESCE(x1,x2,...,xn) has to scan each expression x and determine its data type. The highest data type is then the data type of the result. That means some CAST() might have to be done under the covers.
Validations for ISNULL and COALESCE is different.
ISNULL(NULL, NULL) -- is int
COALESCE(NULL, NULL) -- Will throw an error
COALESCE(CAST(NULL AS INTEGER), NULL) -- it valid and returns INTEGER
ISNULL takes only 2 parameters whereas COALESCE takes variable number of 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)
I see your point, Joe.
June 26, 2012 at 6:32 am
Jan Van der Eecken (6/25/2012)
Unless they coded ISNULL(a,b) as a wrapper around COALESCE(x1,x2,...,xn), which I would probably have done, just from a maintainability point of view. Then ISNULL should actually be slower due to the extra call and push of parameters.BTW, Gus, why do you spell ISNULL as IsNull? Looks very like C style of coding? Just wondering.
Camel-case of coalesced words makes them more readable, per typography studies. It's not something that matters in this case, just habit on my part.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply