Diff Between Top 1 1 and Select 1 in SQL Select Query

Diff between Top 1 1 and Select 1 in SQL Select Query

SELECT TOP 1 Means Selecting the very 1st record in the result set

SELECT 1 Means return 1 as the result set

SELECT TOP 1 1 FROM [SomeTable] WHERE <SomeCondition> Means if the condition is true and any rows are returned from the select, only return top 1 row and only return integer 1 for the row (no data just the integer 1 is returned).

select top 1 * vs select top 1 1

SQL Server detects EXISTS predicate relatively early in the query compilation / optimisation process, and eliminates actual data retrieval for such clauses, replacing them with existence checks. So your assumption:

I now see that the first is 80% of the execution time (relative to the batch of 2) whilst the second is only 20%.

is wrong, because in the preceding comparison you have actually retrieved some data, which doesn't happen if the query is put into the (not) exists predicate.

Most of the time, there is no difference how to test for the existence of rows, except for a single yet important catch. Suppose you say:

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

somewhere in the code module (view, stored procedure, function etc.). Then, later, when someone else will decide to put WITH SCHEMABINDING clause into this code module, SQL Server will not allow it and instead of possibly binding to the current list of columns it will throw an error:

Msg 1054, Level 15, State 7, Procedure BoundView, Line 6

Syntax '*' is not allowed in schema-bound objects.

So, in short:

if exists (select 0 from ...)

is a safest, fastest and one-size-fits-all way for existence checks.

SELECT TOP 1 1 VS IF EXISTS(SELECT 1

I'd recommend IF EXISTS(SELECT * ...), unless this is actually causing a performance issue. It expresses the intent of the query in a much better understood fashion than alternatives.

I'd avoid COUNT(*) (as in the current answers) unless you actually need the count of rows from the table.

If you want the "efficiency" of checking the rowcount from the result, I'd probably go for:

select 1 where exists(select * from BigTable where SomeColumn=200)

Which produces the same result set as your second query (either 0 or 1 row)

Performance effect of using TOP 1 in a SELECT query

You may get some performance difference from just using top, but the real performance you get by using indexes.

If you have an index for the UserName and Application fields, the database doesn't even have to touch the table until it has isolated the single record. Also, it will already know from the table statistics that the values are unique, so using top makes no difference.

Difference between Top and Limit Keyword in SQL

If you are using SQL Server use TOP if you are using MySQL or Postgres use Limit!

AFAIK there is no product that currently supports both. Here's one list of current implementations and here's another (covers more products but in less detail)

IF EXISTS (SELECT 1...) vs IF EXITS (SELECT TOP 1 1...)

If you view the execution plan for these queries you can see that they are identical. Good coding practices would ask that you leave out the "TOP 1" but they should run identical either way.

NOT IN vs NOT EXISTS and select 1 1?

Your first query will get you only top most record (very first record) out of the total rows in result set. So, if your query returns 10 rows .. you will get the first row. Read more about TOP

SELECT TOP 1 FROM tblSomeTable

In your Second query the part under () is a subquery, in your case it's a correlated subquery which will be evaluated once for each row processed by the outer query.

NOT EXISTS will actually check for existence of the rows present in subquery

WHERE NOT EXISTS
(
SELECT TOP 1 1 FROM tblEmployee e2 WHERE e2.E_ID = e.E_ID AND isFired = 'N'
)

Read more about Correlated subquery as well as Subqueries with EXISTS

What does TOP 1 mean in an sql query?

The query in the example will return the first RequestID from the table PublisherRequests.

The order of the results without an Order By clause is arbitrary. So, your example will return an arbitrary RequestID (i.e. the first RequestID in an arbitrarily ordered list of RequestIDs).

You can change the order by defining an Order By.

For example, to get the last entered ID, you can write

Select Top 1 RequestID
From PublisherRequests
Order By RequestID Desc

Updated to include corrected order information from @Kirtan Gor and @AlexK

MAX vs Top 1 - which is better?

Performance is generally similar, if your table is indexed.

Worth considering though: Top usually only makes sense if you're ordering your results (otherwise, top of what?)

Ordering a result requires more processing.

Min doesn't always require ordering. (Just depends, but often you don't need order by or group by, etc.)

In your two examples, I'd expect speed / x-plan to be very similar. You can always turn to your stats to make sure, but I doubt the difference would be significant.

---

Related Topics