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