Is Changing IF EXIST(SELECT 1 FROM ) to IF EXIST(SELECT TOP 1 FROM ) has any side effects?
sql, sql-server, sql-server-2012, t-sql
Solution
No, there should be no difference. `EXISTS` bails out as soon as it has found a single matching row. That's why it's always preferred over e.g. `(select COUNT(*) from ...) > 0` - a `COUNT` would force all rows to be considered.
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 turn on execution plans, you'll see that the second query generates an execution plan that includes a `TOP` operator. The 3rd and 4th queries produce identical plans. The `TOP` is ignored.
Problem
I have an existing SP running on my production server. I have found some significant performance gain from changing `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 TOP 1 keyword. Just curious to know whether changing this has any side effect?