All geek questions in one place

Is changing IF EXIST (SELECT 1 FROM) to IF EXIST (SELECT TOP 1 FROM) has any side effects?

I have an existing SP running on my production server. I found a significant increase in productivity from shifts IF EXIST(SELECT 1 FROM ) to IF EXIST(SELECT TOP 1 1 FROM )and IF NOT EXIST(SELECT 1 FROM )to IF NOT EXIST(SELECT TOP 1 1 FROM ). The only difference is the TOP 1 keyword . Just curious to know if this change has any side effect?

+3
source share
2 answers

No, there shouldn't be a difference. EXISTSdiscarded as soon as one matching line is detected. Therefore, he always preferred, for example, (select COUNT(*) from ...) > 0- a COUNTto make all the lines count.

If you create the following four queries:

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)

And include execution plans, you will see that the second request creates an execution plan, which includes an operator TOP. The third and fourth queries give identical plans. TOPignored.

+8
source

When you add TOP 1, it does not go to other lines. This is what makes the difference. Otherwise, he will read the whole table from start to finish.

, , , where. . , .

TOP .

-1

Source: https://habr.com/ru/post/1606896/

More articles:


All Articles