Variables, Nulls and Coalesce
-
Carlo Romagnano (11/27/2009)
Rob Goddard (11/27/2009)
does the optimizer spot this and skip all the variable assignments and just do the last one?All the variable assignments are done.
I spose this makes sense given the quick method of building a csv from a column i.e. it's clearly possibly that a single variable declaration from a select which returns multiple rows can make use of information from every row.
For the optimiser to spot that it was being assigned to a single variable AND that it would only require data from one row to create the necessary value, it would have to be an exceedingly clever piece of logic.
-- Kev
-------------------------------Oh no!
-
nice one!
its strange!Thanks,
Ashka Modi
Software Engineer || credEcard Technologies (india) Pvt. Ltd. -
He I got this one ...
-
It would have been a good idea to reference http://msdn.microsoft.com/en-us/library/ms187330.aspx as well as the coalesce page, because people thinking that if no value is returned the value is set to null is a pretty common error. I must have explained a few dozen times over the years that
select @a = iValue from @vals where iKey <> iValueandset @a = (select iValue from @vals where iKey <> iValue)behave completely differently when the filter passes no rows.Tom
-
good tricky question
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply