Variables, Nulls and Coalesce

Question

Variables, Nulls and Coalesce

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Kevin Gill SSCrazy Points: 2348 More actions · Answer 1

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!

Ashka Modi Old Hand Points: 377 More actions · Answer 2

nice one! 🙂 its strange!

Thanks,

Ashka Modi
Software Engineer || credEcard Technologies (india) Pvt. Ltd.

Bhavesh_Patel SSCrazy Points: 2259 More actions · Answer 3

He I got this one ... 😛

[font="System"]Bhavesh Patel[/font]

http://bhaveshgpatel.wordpress.com/

TomThomson SSC Guru Points: 104773 More actions · Answer 4

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 <> iValueand

set @a = (select iValue from @vals where iKey <> iValue)behave completely differently when the filter passes no rows.

Tom

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

good tricky question 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/