通用資料表運算式(CTE)引發的改變執行順序同WHERE條件順序引發的bug,ctewhere
以下類比一下CTE出錯
/*測試環境Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) */
產生表Tab資料:
--> --> (Roy)產生測試數據 if not object_id('Tab') is null drop table TabGoCreate table Tab([Col1] int,[COl2] nvarchar(5))Insert Tabselect 1,N'a,b,c' union allselect 2,N'd,e' union allselect 3,N'f'Go
方法1:用CTE引發函數出錯
if object_id('Tempdb..#Tab') is not null drop table #Tabselect top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns bdeclare @Str varchar(10)='a';with Cteas(Select a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from Tab a,#Tab bwhere charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=',')select Col1 from Cte where @str=COl2/*訊息 537,層級 16,狀態 3,第 8 行傳遞給 LEFT 或 SUBSTRING 函數的長度參數無效。*/
方法2:直接用語句時不會報錯:
if object_id('Tempdb..#Tab') is not null drop table #Tabselect top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns bdeclare @Str varchar(10)='a'Select a.Col1from Tab a,#Tab bwhere charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','and substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@Str/*Col11*/
方法3:把Where條件換一下順序也出錯
if object_id('Tempdb..#Tab') is not null drop table #Tabselect top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns bgodeclare @Str varchar(10)='a'Select a.Col1from Tab a,#Tab bwhere substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@Str and charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
查原因從執行計畫來找
if object_id('Tempdb..#Tab') is not null drop table #Tabselect top 100 ID=Identity(int,1,1) into #Tab from syscolumns a,syscolumns bgoSET SHOWPLAN_ALL ON; go--a.查看方法1執行計畫declare @Str varchar(10)='a';with Cteas(Select a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from Tab a,#Tab bwhere charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=',')select Col1 from Cte where @str=COl2go--b.查看方法2執行計畫declare @Str varchar(10)='a'Select a.Col1from Tab a,#Tab bwhere charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','and substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@StrgoSET SHOWPLAN_ALL offgo
方法1、方法2產生的執行計畫圖
注意看以上第4行的運算,再查第3行執行順序
方法1:可看到語句先執行條件(@str=COl2),再執行(charindex(',',','+a.Col2,b.ID)=b.ID)方法2:可看到語句先執行條件(charindex(',',','+a.Col2,b.ID)=b.ID),再執行(substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) =@Str)
方法3同方法1一樣原因,條件的順序也會引發執行出錯