SQLServer資料類型優先順序對效能的影響

標籤：

原文: SQLServer資料類型優先順序對效能的影響

譯自：

http://www.mssqltips.com/sqlservertip/2749/sql-server-data-type-precedence/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012814

 

問題：

         我在我的應用程式中使用簡單的查詢/預存程序訪問一個很大的表。但執行了很長時間。在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類型。

 

 

當索引列不再被轉換所影響時，最佳化器可以自由地選擇最優執行計畫。

 

不管你是在應用程式或者在預存程序中定義查詢參數，確保查詢參數中的資料類型和查詢列的資料類型相吻合能避免索引掃描和其他轉換引起的問題。

補充：資料類型的優先順序，從高到底：

1. user-defined data types (highest)

2. sql_variant

3. xml

4. datetimeoffset

5. datetime2

6. datetime

7. smalldatetime

8. date

9. time

10. float

11. real

12. decimal

13. money

14. smallmoney

15. bigint

16. int

17. smallint

18. tinyint

19. bit

20. ntext

21. text

22. image

23. timestamp

24. uniqueidentifier

25. nvarchar (including nvarchar(max) )

26. nchar

27. varchar (including varchar(max) )

28. char

29. varbinary (including varbinary(max) )

30. binary (lowest)

SQLServer資料類型優先順序對效能的影響