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 «««45678

Time Bomb Coding Expand / Collapse
Author
Message
Posted Tuesday, March 2, 2010 12:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:46 AM
Points: 18, Visits: 152
True, but COUNT(NULL) and COUNT(1/NULL) are not mentioned in the T-SQL COUNT() reference. Since the results of SELECT (1/NULL) is NULL, this would appear to be a way to pass the NULL value to COUNT(), but BOL doesn't mention it, nor is there any explanation of why the result of COUNT(1/NULL) would be zero. If I were to take a stab, I would guess that supplying NULL to the COUNT() function satisfies the parameter condition but does not count any rows. There may be some mathematical explanation of the behavior, but I wouldn't know what it is.
Post #875443
Posted Monday, June 7, 2010 8:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 1, 2012 1:35 AM
Points: 70, Visits: 67
Hi David

I would like to add something in Your "simple example"...

IF EXISTS(SELECT 1 FROM ... WHERE...)

Instead of this Use following...


IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)


Post #933554
Posted Monday, June 7, 2010 9:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 42,849, Visits: 35,978
sanjays-735734 (6/7/2010)
Hi David

I would like to add something in Your "simple example"...

IF EXISTS(SELECT 1 FROM ... WHERE...)

Instead of this Use following...


IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)


Why?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #933564
Posted Monday, June 7, 2010 11:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:44 PM
Points: 2,901, Visits: 1,806
sanjays-735734 (6/7/2010)
Hi David

I would like to add something in Your "simple example"...

IF EXISTS(SELECT 1 FROM ... WHERE...)

Instead of this Use following...


IF EXISTS(SELECT TOP(1) 1 FROM ... WHERE...)




Sanjays, I've found that the IF EXISTS in nearly all cases generates the same execution plan.

You can get away with SELECT * however I've always hammered in the disciplines of not using SELECT * so I don't confuse developers with the cases where it doesn't matter.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #933621
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse