Which is better select 1 vs select * to check the existence of record?

Question

Which is the better option from the following cases?

1.

IF EXISTS (SELECT * FROM Table WHERE ID = 3)
BEGIN
    -------
END

Vs

2.

IF EXISTS (SELECT 1 FROM Table WHERE ID = 3)
BEGIN
    -------
END

Or are both are same?

Answer 1

EXISTS will check if any record exists in a set. so if you are making a SELECT from 1 million records or you are making a SELECT from 1 record(let say using TOP 1), they will have same result and same performance and even same execution plan.(why?) Because exists will not waits until 1 million record scan complete(or 1 record scan complete). Whenever it finds a record in a set, it will be return the result as TRUE(There is no matter in this case you are using * or column name both will have same performance result).

USE pubs
GO

IF EXISTS(SELECT * FROM dbo.titleauthor)
PRINT 'a'

IF EXISTS(SELECT TOP 1 * FROM dbo.titleauthor)
PRINT 'b'

below is the execution plan for these queries(as I have Screen size problem, I have cropped it's image)

But this scenario and performance and even execution plan will be completly change, when you are using queries as follow(I do not know why should use this query!):

USE pubs
GO

IF EXISTS(SELECT * FROM dbo.titleauthor)
PRINT 'a'

IF EXISTS(SELECT 1 )
PRINT 'b'

in this scenario, as SQL Server does not need to perform any scan operation in second query, then the execution plan will be changed as follow: