CODEWITHSUNDEEP
sqlsql-serversql-server-2012
d.997
d.997

Reputation: 73

Performance for SELECT query to check if the record exist in the table or not

Let's say I've [User] table and I want to check if the email exist in that table or not. Are there any differences between following two queries? Which one will perform fast and why?

Option 1:

DECLARE @EXIST as bit

SELECT TOP 1 @EXIST = 1 
FROM [dbo].[User]
WHERE UserEmail = N'[email protected]'

Option 2:

DECLARE @EXIST as bit
SELECT @EXIST = 1 
FROM [dbo].[User]
WHERE UserEmail = N'[email protected]'

I think option 1 might be faster because of TOP 1, but not exactly sure since I'm not SQL expert.

Upvotes: 0

Views: 689

Answers (1)

StackUser
StackUser

Reputation: 5398

You can use Exists clause since it is specifically for existence check.

IF EXISTS(SELECT 1 FROM FROM [dbo].[User] WHERE UserEmail = N'[email protected]') SET @EXIST = 1

If you plan to choose only between 2 options then go for option-1 since there might be an error if your table contains duplicate records for the given email when we choose option-2.

We have to check the execution plan before making any decision.

Upvotes: 1

Related Questions