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

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

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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: 2 days ago @ 1:14 AM
Points: 28, Visits: 450
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-Forever

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

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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-Forever

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

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 40,210, Visits: 36,619
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, October 15, 2014 4:57 AM
Points: 731, Visits: 2,034
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: 2 days ago @ 1:14 AM
Points: 28, Visits: 450
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, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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: 2 days ago @ 1:14 AM
Points: 28, Visits: 450
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