Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

T-SQL 1=1 Performance Hit

Tags:

For my SQL queries, I usually do the following for SELECT statements:

SELECT ...
FROM table t
WHERE 1=1
  AND t.[column1] = @param1
  AND t.[column2] = @param2

This will make it easy if I need to add / remove / comment any WHERE clauses, since I don't have to care about the first line.

Is there any performance hit when using this pattern?

Additional Info:

Example for sheepsimulator and all other who didn't get the usage.

Suppose the above query, I need to change @param1 to be not included into the query:

With 1=1:

...
WHERE 1=1 <-- no change
  --AND t.[column1] = @param1 <-- changed
  AND t.[column2] = @param2 <-- no change
...

Without 1=1:

...
WHERE <-- no change
  --t.[column1] = @param1 <-- changed
  {AND removed} t.[column2] = @param2 <-- changed
...
like image 800
Adrian Godong Avatar asked Jun 26 '09 14:06

Adrian Godong


People also ask

What does 1 1 Mean SQL query?

It is a common statement that is used to return all the records from a given table. The statement where 1=1 in SQL means true. It is the same operation as running the select statement without the where clause. You might ask, what is the purpose of the clause where 1=1?

What does +1 means in SQL?

WHERE 1 is a synonym for "true" or "everything." It's a shortcut so they don't have to remove the where clause from the generated SQL.

Which loop is faster in SQL?

Use UNION ALL instead of UNION whenever is possible That is why UNION ALL is faster. Because it does not remove duplicated values in the query. If there are few rows (let's say 1000 rows), there is almost no performance difference between UNION and UNION ALL.


2 Answers

No, SQL Server is smart enough to omit this condition from the execution plan since it's always TRUE.

Same is true for Oracle, MySQL and PostgreSQL.

like image 92
Quassnoi Avatar answered Oct 02 '22 04:10

Quassnoi


It is likely that if you use the profiler and look, you will end up seeing that the optimizer will end up ignoring that more often than not, so in the grand scheme of things, there probably won't be much in the way of performance gain or losses.

like image 35
TheTXI Avatar answered Oct 02 '22 05:10

TheTXI