where 1=-1 and 1=1 會(huì)不會(huì)影響查詢(xún)效率？
來(lái)源：程序員人生 發(fā)布時(shí)間：2015-08-20 08:37:45 閱讀次數(shù)：4489次
今天用sql profiler跟1個(gè)底層生成的SQL 的時(shí)候，跟到這樣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,'企業(yè)' 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,'企業(yè)' 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,之前也用過(guò)這個(gè)東西拼過(guò)條件，但是后來(lái)有人說(shuō)這樣影響查詢(xún)性能，再后來(lái)又有人說(shuō)不影響。然后我就迷茫了。。。
還是自己做個(gè)實(shí)驗(yàn)測(cè)試下吧。
首先，先看1下沒(méi)有這個(gè)條件的查詢(xún)：
/****** 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
然后使用履行計(jì)劃來(lái)估計(jì)下：
然后加入條件：
在履行計(jì)劃中可以看到，開(kāi)消幾近全部在聚集索引表的掃描上，對(duì)照上圖，發(fā)現(xiàn)這兩張表數(shù)據(jù)1致。
嘿嘿，看來(lái)他們的查詢(xún)效力是1樣的。
but why????百度下吧。。。。
生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
