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

Exists and IF Exists Expand / Collapse
Author
Message
Posted Monday, January 10, 2011 6:41 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: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
Hi

Which is better in the below query

--- This eg is from HELP File

USE AdventureWorks ;
GO

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO



USE AdventureWorks ;
GO

SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT 1
FROM HumanResources.Employee AS b
WHERE a.ContactId = b.ContactID
AND a.LastName = 'Johnson');
GO


in the same way

IF EXISTS (SELECT * FROM Tab_Name)
Begin
.....
End
OR
IF EXISTS (SELECT 1 FROM Tab_Name)
Begin
.....
End
Which is better
selecting all columns or single Column which is better.

Thanks
Parthi



Thanks
Parthi
Post #1045263
Posted Monday, January 10, 2011 7:31 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
They're exactly the same. The columns are ignored in an EXISTS.


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 #1045299
Posted Monday, January 10, 2011 7:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:54 AM
Points: 28, Visits: 449
I prefer:

IF EXISTS(SELECT TOP 1 1 FROM ...
Post #1045310
Posted Monday, January 10, 2011 7:48 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: Wednesday, December 26, 2012 11:03 PM
Points: 586, Visits: 2,195
GilaMonster (1/10/2011)
They're exactly the same. The columns are ignored in an EXISTS.




So if there is millions of records are there we can prefer either option ,is it is good???
Now i am having only 1000+records for testing i need to Implement this on million records to check whether EXISTS condition works perfectly

Thanks
Parthi


Thanks
Parthi
Post #1045312
Posted Monday, January 10, 2011 8:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
As I said, they're exactly the same. During the parsing of the query any reference to columns is stripped out of the exists.


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 #1045323
Posted Monday, January 10, 2011 8:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 39,977, Visits: 36,340
nekonecny (1/10/2011)
I prefer:

IF EXISTS(SELECT TOP 1 1 FROM ...


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 #1045324
Posted Monday, January 10, 2011 9:55 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 6:02 AM
Points: 731, Visits: 2,025
Those are exactly same. It does not matter, whether its * or 1 in the case of Exists.
Post #1045424
Posted Monday, January 10, 2011 1:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:54 AM
Points: 28, Visits: 449
I need only one row (top 1), if i use "EXISTS". Is Optimizer so clever to eliminate other potential rows?
Post #1045529
Posted Monday, January 10, 2011 1:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,032, Visits: 5,283
nekonecny (1/10/2011)
I need only one row (top 1), if i use "EXISTS". Is Optimizer so clever to eliminate other potential rows?
Yes, it is.. An EXISTS check completes as soon as a single row is found, it doesn't need to look any further and it knows it. The logic is "short-circuited". In either case as soon as a single row is found matching the criteria it returns TRUE. It seems TOP might actually cost you performance..

CEWII
Post #1045544
Posted Monday, January 10, 2011 1:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:54 AM
Points: 28, Visits: 449
Elliott Whitlow (1/10/2011)
nekonecny (1/10/2011)
I need only one row (top 1), if i use "EXISTS". Is Optimizer so clever to eliminate other potential rows?
Yes, it is.. An EXISTS check completes as soon as a single row is found, it doesn't need to look any further and it knows it. The logic is "short-circuited". In either case as soon as a single row is found matching the criteria it returns TRUE. It seems TOP might actually cost you performance..

CEWII


Thanks! I am smarter now
Post #1045572
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse