T-SQL 1=1 Performance Hit

sql-server tsql

14,500

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

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

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

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

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

View more solutions

14,500

Author by

Adrian Godong

Updated on June 06, 2022

Comments

Adrian Godong about 2 years
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
...
```
- J. Polfer about 15 years
  
  I'm sorry if this sounds kinda daff, but I'm really not sure how addding 1=1 to your WHERE clause gains you the above benefits; could you elaborate on this for those of us who would like to learn this possible best practice?
- Quassnoi about 15 years
  
  @sheepsimulator: it's super easy to comment and uncomment any condition merely by prepending it with double dash (--)
- tekBlues about 15 years
  
  Hey Adrian, I always do the same thing, it's so comfortable to build dynamic queries...
RichardOD about 15 years

I agree. @Adrian- if write the two queries together and turn on the execution plan, you will probably see that both queries are 50% each.
Philip Kelley about 15 years

A cool trick, but this is a valid point. I may start using this during development, but I'd want to take it out before releasing it to Production.
Adrian Godong about 15 years

Not a valid point I'd say. SQL injection uses "OR 1=1" not "1=1 AND". Those two are distinctly different, if an app can't differentiate them, are you sure the other results are reliable?
Adrian Godong about 15 years

Adding a line will require modification of the last line.
Danny Beckett about 11 years

Do you have a source for this? I believe you, I'd just link something to reference.
Quassnoi about 11 years

@Danny: for MySQL, yes: dev.mysql.com/doc/refman/5.6/en/where-optimizations.html Constant condition removal (needed because of constant folding), for others no but constant folding is core feature of any optimizer
danielson317 almost 8 years

If your doing this on a web app or SAAS system the customer likely isn't capable of installing their own monitoring tools. And of course if you install your own you know these queries are safe.
Alexandre about 3 years

Also commenting the last line will require modification of the penultimate line.