Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Variables, Nulls and Coalesce Expand / Collapse
Author
Message
Posted Wednesday, November 25, 2009 1:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Ya got me.



Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #824929
Posted Thursday, November 26, 2009 7:26 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 6:50 AM
Points: 540, Visits: 254
This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.
Post #825221
Posted Thursday, November 26, 2009 7:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:53 AM
Points: 1,105, Visits: 301
dun (11/26/2009)
This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.

Glad you enjoyed it This example actually came to mind because of the amount of times I fell over things like this when I was doing serious T-SQL programming for the first time a couple of years ago, when trying to improve and modify the thousands of lines of code left for us to ... try to deal with ... by an (SEI Level 5 - ha!) outsourcing company who wrote our then brand-new system.

-- Kev


-------------------------------
Oh no!
Post #825224
Posted Friday, November 27, 2009 6:52 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, May 24, 2013 5:20 AM
Points: 660, Visits: 558
Nice question.

Along the same lines of "no rows returned, do nothing", I have a feeling that it may be a case of "400 rows returned, assign 400 values to the same variable" so you are just left with the last one it assigned.

My question is, if you're not doing anything with that variable (i.e. "select @val = col from tab" rather than "select @val = @val + col from tab"), does the optimizer spot this and skip all the variable assignments and just do the last one, or are you inadvertantly creating overhead by assigning every single value that you will never see?


--------
I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams
Post #825603
Posted Friday, November 27, 2009 7:00 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 2,528, Visits: 2,402
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.
Post #825608
Posted Friday, November 27, 2009 10:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:53 AM
Points: 1,105, Visits: 301
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!
Post #825692
Posted Wednesday, December 2, 2009 11:45 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 22, 2012 7:08 AM
Points: 233, Visits: 112
nice one! :) its strange!

Thanks,

Ashka Modi
Software Engineer || credEcard Technologies (india) Pvt. Ltd.
Post #827959
Posted Friday, December 11, 2009 10:35 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 11, 2013 12:41 PM
Points: 621, Visits: 297
He I got this one ...



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Post #833286
Posted Saturday, January 30, 2010 7:01 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 8,832, Visits: 9,389
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 <> iValue

and

set @a = (select  iValue from @vals where iKey <> iValue)

behave completely differently when the filter passes no rows.


Tom
Post #856639
Posted Tuesday, August 20, 2013 2:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,890, Visits: 2,329
good tricky question


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1486137
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse