SQL Server Performance

WHERE 1 = 1

Discussion in 'General DBA Questions' started by ddupuis, Feb 10, 2010.

  1. ddupuis New Member

    There are some developers in our organization that like to write the SQL in their stored procedures like this:
    SELECT ...
    FROM ...
    WHERE 1 = 1
    AND ...
    My question is about the 1=1 as the first criteria in the where clause. Is it possible that it could have an undesired effect on the execution plan that is generated?
  2. preethi Member

    As far as the code is concerned 1=1 will not have any effect. But when it comes to execution plan, it will be a different story. I have seen a complex query with "0=1" as one of the conditions has created a complex execution plan, (It didn't ignore the process) "1=1" too may go in the same path
    It all depends on when the condition will be evaluated. In SQL 7 and 2000, the query optimizer is not optimized as later versions.
    I believe, unless you have some CASE statements which forces SQL Server to evaluate each row, the order of condition is immaterial.
  3. FrankKalis Moderator

    Would be interesting to hear the reasoning for this from that developers.
  4. Adriaan New Member

    Perhaps lazy programmers concatenating criteria for dynamic SQL. Instead of starting with "WHERE", they use "WHERE 1=1" and add " AND column = value" for each criteria.
    Magnus Reuter likes this.
  5. Madhivanan Moderator

    [quote user="Adriaan"]
    Perhaps lazy programmers concatenating criteria for dynamic SQL. Instead of starting with "WHERE", they use "WHERE 1=1" and add " AND column = value" for each criteria.
    [/quote]
    Exactly. I had seen such cases when I was asked to optimize some queries
  6. Madhivanan Moderator

    I forget to mention that sometimes back when I was writing dynamic code in Mysql where column name is passed, I overcome some weird error by dummingly adding 1=1
  7. dineshasanka Moderator

    I think 1=1 will be ignored by the query engine.
    If you want to have a table structure of HumanResources.Employee table into a new table (NewTable) with no data. (Performance wise this is not recomended. But this is fast!)
    SELECT * FROM HumanResources.Employee
    SELECT * INTO NewTable FROM HumanResources.Employee WHERE 1 = 0
    SELECT * FROM NewTable
  8. Magnus Reuter New Member

    As adriaan wrote: "Perhaps lazy programmers concatenating criteria for dynamic SQL. Instead of starting with "WHERE", they use "WHERE 1=1" and add " AND column = value" for each criteria."

    I am one of those lazy programmers :)
    I always start by writing WHERE 1 = 1 and then adding new rows with "AND ..." for criterias. It also makes it easy to comment/uncomment different criterias when troubleshooting unexpected results.
  9. Luis Martin Moderator

    Hi Magnus, welcome to the forums!
    Check thread date, is almost 4 years old;)

Share This Page