Select top 1 * vs select top 1 1

Question

I know a lot of such questions, but I can not find the one that concerns my question.

Looking at this question, Does IF EXIST (SELECT 1 FROM) change to IF EXIST (SELECT TOP 1 FROM) any side effects?

In particular, referring to this section in the answer:

select * from sys.objects
select top 1 * from sys.objects
select 1 where exists(select * from sys.objects)
select 1 where exists(select top 1 * from sys.objects)

I use some of my own tests to understand them correctly. As indicated in the answer:

select 1 where exists(select top 1 * from sys.objects)
select 1 where exists(select top 1 1 from sys.objects)

both invoke the same execution plan, and also invoke the same plan as

select 1 where exists(select * from sys.objects)
select 1 where exists(select 1 from sys.objects)

From my research on issues such as this one, “SELECT TOP 1 1” VS “IF EXISTS (SELECT 1” . I believe this is a consistent best practice:

select 1 where exists(select * from sys.objects)

My first question is: why is this preferable:

select 1 where exists(select 1 from sys.objects)

( "-1", ):

select top 1 * from sys.objects
select top 1 1 from sys.objects

, 80% ( 2), - 20%.

select 1 where exists(select 1 from sys.objects)

?

Answer 1

SQL Server EXISTS / , . , :

, - 80% ( 2), - 20%.

, , , (not) exists.

, , , . , :

if exists (select * from dbo.SomeTable)
...

- (, , ..). , , - WITH SCHEMABINDING , SQL Server :

Msg 1054, 15, 7, BoundView, 6
'*' , .

, :

if exists (select 0 from ...)

, .

Answer 2

:

select top 1 * from sys.objects
select top 1 1 from sys.objects

SQL- ( ), , "1" .

, exists, SQL Server , , , select * select 1.

, 1 , .

select * select 1 , 1 , , 2 "X" - , . ... and exists (select 1 ...

Answer 3

EXISTS - , , , - . 1 * , , , false.

, , .

select 1 where exists(select * from sys.objects)
select 1 where exists(select 1 from sys.objects)

, , . , :

select top 1 * from sys.objects
select top 1 1 from sys.objects

, . , : syspalnames, syssingleobjrefs sysschobjs.

, EXISTS - SELECT 1 SELECT * - . SELECT 1, SELECT * , Microsoft.

Answer 4

, . :

Top 1 Top n n sql. Top 1 1 Top n s n s SQL-.

, 10 . .

SELECT TOP 10 FirstName, LastName
  FROM [tblUser]
  where EmailAddress like 'john%'

select top 10 'test' - , ( , ), "test".

SELECT TOP 10 'test'
  FROM [tblUser]
  where EmailAddress like 'john%'

, select TOP 1 * , select TOP 1 1 , "1". , , Null .

:

SELECT TOP 10 'test', FirstName
  FROM [tblUser]
  where EmailAddress like 'john%'

, , "test", , 10 .