T-SQL 1=1 Performance Hit

问题:

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

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

回答2:

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.

回答3:

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.

回答4:

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.

回答5:

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.

回答6:

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.

回答7:

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.

回答8:

One potentially mildly negative impact of this is that the AND 1=1 will stop SQL Server's simple parameterisation facility from kicking in.

Demo script

DBCC FREEPROCCACHE;  /*<-- Don't run on production box!*/

CREATE TABLE [E7ED0174-9820-4B29-BCDF-C999CA319131]
(
X INT, 
Y INT,
PRIMARY KEY (X,Y)
);

GO
SELECT *
FROM   [E7ED0174-9820-4B29-BCDF-C999CA319131]
WHERE  X = 1
       AND Y = 2;
GO
SELECT *
FROM   [E7ED0174-9820-4B29-BCDF-C999CA319131]
WHERE  X = 2
       AND Y = 3;
GO   
SELECT *
FROM   [E7ED0174-9820-4B29-BCDF-C999CA319131]
WHERE  1 = 1
       AND X = 1
       AND Y = 2 
GO   
SELECT *
FROM   [E7ED0174-9820-4B29-BCDF-C999CA319131]
WHERE  1 = 1
       AND X = 2
       AND Y = 3    

---

SELECT usecounts,
       execution_count,
       size_in_bytes,
       cacheobjtype,
       objtype,
       text,
       creation_time,
       last_execution_time,
       execution_count
FROM   sys.dm_exec_cached_plans a
       INNER JOIN sys.dm_exec_query_stats b
         ON a.plan_handle = b.plan_handle
       CROSS apply sys.dm_exec_sql_text(b.sql_handle) AS sql_text
WHERE  text LIKE '%\[E7ED0174-9820-4B29-BCDF-C999CA319131\]%' ESCAPE '\'
       AND text NOT LIKE '%this_query%'
ORDER BY last_execution_time DESC       

GO

DROP TABLE [E7ED0174-9820-4B29-BCDF-C999CA319131]   

Shows that both the queries without the 1=1 were satisfied by a single parameterised version of the cached plan whereas the queries with the 1=1 compiled and stored a separate plan for the different constant values.

Ideally you shouldn't be relying on this anyway though and should be explicitly parameterising queries to ensure that the desired elements are parameterised and the parameters have the correct datatypes.