SQLServer數據類型優先級對性能的影響

　　問題： 　　我在我的應用程序中使用簡單的查詢/存儲過程訪問一個很大的表。但執行了很長時間。在where子句中，我使用了有索引并且高選擇性(selective)并且沒有用函數包裹的字段。但是看起來就像沒有使用索引一樣，問題出在那里? 　　解決方案： 　　出現這種微秒的問題原因可能是作為參數的數據類型與查詢中的數據類型不一致。在這種情況下，SQLServer將會要么把where中的列，要么把參數的數據類型隱式轉換為更高級或者更低級的數據類型。當作為被查詢列被轉換時(轉換競爭中的犧牲者)，將引起掃描(scan)來滿足查詢請求。讓我們看看以下兩個例子，第一個例子使用示例數據庫AdventureWorks，我們將通過一個客戶的AccountNumber在Sales.Customer表中查詢這個客戶。AccountNumber這一列的數據類型是varchar(10)并且上面有一個唯一索引。運行下面的查詢并且查看執行計劃，可以看到結果如我們所愿： 　　create proceduredbo.PrecedenceTest 　　( 　　@AccountNumber varchar(10) 　　) 　　as 　　begin 　　set nocount on 　　select * 　　from Sales.Customer 　　where AccountNumber = @AccountNumber 　　end 　　go 　　exec dbo.PrecedenceTest'AW00030113' 　　go 　　執行計劃如下： 　　接著讓我們在參數上做些小改動，把它改為nvarchar(10)，然后重新執行語句： 　　alter procedure dbo.PrecedenceTest 　　( 　　@AccountNumber nvarchar(10) 　　) 　　as 　　begin 　　set nocount on 　　select * 　　from Sales.Customer 　　where AccountNumber = @AccountNumber 　　end 　　go 　　exec dbo.PrecedenceTest 'AW00030113' 　　go 　　執行計劃顯示，優化器選擇了掃描TerritoryID上的索引。 　　檢查Filter操作，可以看到AccountNumber列上被隱式轉換了類型來匹配傳入的參數。由于數據類型varchar比參數類型nvarchar級別更低，導致其所在的索引失效。 　　現在讓我們驗證一下，在較低級別的數據類型作為查找參數下的情況。在這個例子中，Person.Person 表的LastName列是nvarchar類型，并且上面存在一個可用的索引，存儲過程傳入的參數是varchar類型： 　　alter procedure dbo.PrecedenceTest( 　　@LastName varchar(50) 　　) 　　as 　　begin 　　set nocount on 　　select * 　　from Person.Person 　　where LastName = @LastName 　　end 　　go 　　exec dbo.PrecedenceTest 'Tamburello' 　　go 　　執行計劃顯示，優化器選擇使用了索引查找： 　　點開Index Seek的詳細信息，可以看到列LastName的數據類型因為傳入參數的原因而隱式轉換成更高級的nvarchar類型。 　　當索引列不再被轉換所影響時，優化器可以自由地選擇最優執行計劃。 　　不管你是在應用程序或者在存儲過程中定義查詢參數，確保查詢參數中的數據類型和查詢列的數據類型相吻合能避免索引掃描和其他轉換引起的問題。 　　補充：數據類型的優先級，從高到底： 　　user-defined data types (highest) 　　sql_variant 　　xml 　　datetimeoffset 　　datetime2 　　datetime 　　smalldatetime 　　date 　　time 　　float 　　real 　　decimal 　　money 　　smallmoney 　　bigint 　　int 　　smallint 　　tinyint 　　bit 　　ntext 　　text 　　image 　　timestamp 　　uniqueidentifier 　　nvarchar (including nvarchar(max) ) 　　nchar 　　varchar (including varchar(max) ) 　　char 　　varbinary (including varbinary(max) ) 　　binary (lowest)

原文轉自：http://www.anti-gravitydesign.com