Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kev Gill » Variables, Nulls and Coalesce
Add to briefcase
19 posts, Page 2 of 2
««12

Variables, Nulls and Coalesce Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Tom Garth
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 04, 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
dun
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: Friday, December 07, 2012 8:25 AM
Points: 540, Visits: 245
This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.
Post #825221
Kevin Gill
Posted Thursday, November 26, 2009 7:30 AM
SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 932, Visits: 229
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
Rob Goddard
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: Tuesday, April 02, 2013 3:48 AM
Points: 660, Visits: 557
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
Carlo Romagnano
Posted Friday, November 27, 2009 7:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:56 AM
Points: 1,972, Visits: 1,822
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
Kevin Gill
Posted Friday, November 27, 2009 10:20 AM
SSC Eights!

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

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 932, Visits: 229
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
Ashka Modi
Posted Wednesday, December 02, 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: 232, Visits: 112
nice one! :) its strange!

Thanks,

Ashka Modi
Software Engineer || credEcard Technologies (india) Pvt. Ltd.
Post #827959
Bhavesh_Patel
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
L' Eomot Inversé
Posted Saturday, January 30, 2010 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:12 AM
Points: 7,106, Visits: 7,171
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #856639
« Prev Topic | Next Topic »

Add to briefcase
19 posts, Page 2 of 2
««12

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.