where 1=-1 and 1=1 会不会影响查询效率？
来源：程序员人生 发布时间：2015-08-20 08:37:45 阅读次数：4289次
今天用sql profiler跟1个底层生成的SQL 的时候，跟到这样1段代码：
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) CP
WHERE 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,之前也用过这个东西拼过条件，但是后来有人说这样影响查询性能，再后来又有人说不影响。然后我就迷茫了。。。
还是自己做个实验测试下吧。
首先，先看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
然后使用履行计划来估计下：
然后加入条件：
在履行计划中可以看到，开消几近全部在聚集索引表的扫描上，对照上图，发现这两张表数据1致。
嘿嘿，看来他们的查询效力是1样的。
but why????百度下吧。。。。
生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
