T-SQL 1=1 Performance Hit

Question

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
...

Answer 1

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.

Answer 2

No performance hit. Even if your WHERE clause is loaded with a large number of comparisons, this is tiny.

Best case scenario is that it's a bit-for-bit comparison. Worse case is that the digits are evaluated as integers.

Answer 3

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.

Answer 4

For queries of any reasonable complexity there will be no difference. You can look at some execution plans and also compare real execution costs, and see for yourself.

Answer 5

There is no difference, as they evaluated constants and are optimized out. I use both 1=1 and 0=1 in both hand- and code-generated AND and OR lists and it has no effect.

Answer 6

This has no performance impact, but there the SQL text looks like it has been mangled by a SQL injection attack. The '1=1' trick appears in many sql injection based attacks. You just run the risk that some customer of yours someday deploys a 'black box' that monitors SQL traffic and you'll find your app flagged as 'hacked'. Also source code analyzers may flag this. Its a long long shot, of course, but something worth putting into the balance.

Answer 7

Since the condition is always true, SQL Server will ignore it. You can check by running two queries, one with the condition and one without, and comparing the two actual execution plans.

An alternative to achieve your ease of commenting requirement is to restructure your query:

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

You can then add/remove/comment out lines in the where conditions and it will still be valid SQL.