Menu
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
...
800
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?
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.
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.
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.
92
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.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
