where 1=-1 and 1=1 會不會影響查詢效率？，where-1

where 1=-1 and 1=1 會不會影響查詢效率？，where-1

           

        今天用sql profiler跟一個底層產生的SQL 的時候，跟到這樣一段代碼：

     

WITH TempQuery AS(    SELECT *, ROW_NUMBER() OVER (ORDER BY  CreateTime DESC) AS 'RowNumberForSplit'FROM (select E.Name as Name, U.RealyName as RealyName,C.[Description] as Descriptions,'求職者' as tsf ,C.Result,C.CreateTime from [Mr].[User_Complaint] UC inner join [Mr].[User] U on UC.UserCode=U.Code inner join [Mr].[Complaint] C  on UC.ComplaintCode=C.Code inner join [Mr].[Enterprise] E on UC.EnterpriseCode=E.Code union select E.Name as Name, U.RealyName as RealyName,C.[Description] as Descriptions,'企業' as tsf ,C.Result,C.CreateTime from [Mr].[Enterprise_Complaint] EC inner join [Mr].[Enterprise] E on EC.EnterpriseCode=E.Code inner join [Mr].[Complaint] C on EC.ComplaintCode =C.Code inner join [Mr].[User] U on EC.UserCode=U.Code) CPWHERE 1 = 1  AND  1=1)SELECT * FROM TempQuery WHERE RowNumberForSplit BETWEEN 1 AND 10;SELECT COUNT(1) AS TOTAL_COUNT FROM (select E.Name as Name, U.RealyName as RealyName,C.[Description] as Descriptions,'求職者' as tsf ,C.Result,C.CreateTime from [Mr].[User_Complaint] UC inner join [Mr].[User] U on UC.UserCode=U.Code inner join [Mr].[Complaint] C  on UC.ComplaintCode=C.Code inner join [Mr].[Enterprise] E on UC.EnterpriseCode=E.Code union select E.Name as Name, U.RealyName as RealyName,C.[Description] as Descriptions,'企業' as tsf ,C.Result,C.CreateTime from [Mr].[Enterprise_Complaint] EC inner join [Mr].[Enterprise] E on EC.EnterpriseCode=E.Code inner join [Mr].[Complaint] C on EC.ComplaintCode =C.Code inner join [Mr].[User] U on EC.UserCode=U.Code) CP WHERE 1 = 1  AND  1=1

       然後你就看到後面跟著的where 1=1 and 1=1,以前也用過這個東西拼過條件，但是後來有人說這樣影響查詢效能，再後來又有人說不影響。然後我就迷茫了。。。

      還是自己做個實驗測試下吧。

       首先，先看一下沒有這個條件的查詢：

      

/****** Script for SelectTopNRows command from SSMS  ******/SELECT TOP 100000 [RESOURCE_ID]      ,[CLASS]      ,[SORT_ID]      ,[XML_CONTENT]      ,[SEARCH_CONTENT]      ,[ROW_ID]  FROM [MCS_WORKFLOW].[WF].[GENERIC_FORM_RELATIVE_DATA] WHERE 1=1 AND 1=1

       然後使用執行計畫來估計下：

         

       

       然後加入條件：

          

      

在執行計畫中可以看到，開銷幾乎全部在叢集索引表的掃描上，對比，發現這兩張表資料一致。

       

       

      

     嘿嘿，看來他們的查詢效率是一樣的。

     but why????百度下吧。。。。