WHERE 1 = 1 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

WHERE 1 = 1

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.

Would be interesting to hear the reasoning for this from that developers.

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
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.
Hi Magnus, welcome to the forums!
Check thread date, is almost 4 years old;)
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |