[轉帖]見識一下SQL Server隱式轉換處理的不同

https://cloud.tencent.com/developer/article/1873328

隱式轉換(Implicit Conversion)就像他的名字一樣，是個隱秘、不容易被發現的問題，但歸根結底，還是設計開發中未遵守相關的規範，或者說是不良的設計開發習慣所導致的。

如果在條件中的欄位和變數型別不一致，資料庫會按照低精度向高精度的順序進行隱式轉換，轉換的過程就會消耗資源，例如CPU，但是更關鍵的是如果隱式轉換的欄位是索引列，就會導致因使用了函式而不能用到索引，該使用索引掃描的執行計劃就變成了全表掃描，這對系統效能來說就是潛在的風險。

例如在Oracle中，型別轉換如下，

字串型別轉換關係，

測試表test的object_name是varchar2型別，subobject_name是nvarchar2型別，都建立了索引，

程式碼語言：javascript

複製

SQL> create table test as select * from dba_objects;
Table created.


SQL> select count(*) from test;
  COUNT(*)
----------
     97095


SQL> create index idx_test_01 on test(object_name);
Index created.


SQL> alter table test modify subobject_name nvarchar2(30);
Table altered.


SQL> create index idx_test_02 on test(subobject_name);
Index created.


SQL> desc test
 Name             Null?    Type
 ---------------- -------- ----------------------
 OWNER                     VARCHAR2(30)
 OBJECT_NAME               VARCHAR2(128)
 SUBOBJECT_NAME            NVARCHAR2(30)
 ...

構造where varchar2=nvarchar2，因為varchar2精度比nvarchar2小，所以需要將varchar2轉換為nvarchar2型別，由於varchar2是左值，對索引列做了函式操作(SYS_OP_C2C)，導致不能用到這個索引，因此是全表掃描，

程式碼語言：javascript

複製

SQL> var p nvarchar2(200);
SQL> exec :p := 'a';
PL/SQL procedure successfully completed.


SQL> select * from test where object_name = :p;
no rows selected
------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |   388 (100)|          |      0 |00:00:00.04 |    1396 |
|*  1 |  TABLE ACCESS FULL| TEST |      1 |     16 |   388   (1)| 00:00:05 |      0 |00:00:00.04 |    1396 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(SYS_OP_C2C("OBJECT_NAME")=:P)

構造where nvarchar2=varchar2，因為varchar2精度比nvarchar2小，所以需要將varchar2轉換為nvarchar2型別，但此時的varchar2是右值，雖然用了函式，但是並未影響左值的索引欄位nvarchar2，因此即使出現了隱式轉換，不會影響索引使用，會採用索引掃描，

程式碼語言：javascript

複製

SQL> var q varchar2(200);
SQL> exec :q := 'a';
PL/SQL procedure successfully completed.
SQL> select * from test where subobject_name = :q;
no rows selected
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |     1 (100)|          |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |      1 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_02 |      1 |      1 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBOBJECT_NAME"=SYS_OP_C2C(:Q))

但是最近碰到了一個SQL Server隱式轉換的問題，發現還是有區別。

P.S. SQL Server剛接觸，如果操作和原理上講的不對的，請各位指正。

測試場景1

建立一個SQL_Latin1_General_CP1_CI_AS排序規則的資料庫，測試表如下，一個欄位是varchar，一個欄位是nvarchar，都建立了索引，

程式碼語言：javascript

複製

create table test(c1 nvarchar(200), c2 varchar(200));
insert into test(c1,c2) select cast(a.name as nvarchar(200)), a.name from master.dbo.spt_values a where a.number<10000;
create nonclustered index idx_test_01 on test(c1);
create nonclustered index idx_test_02 on test(c2);

(1) 構造where nvarchar=varchar，

程式碼語言：javascript

複製

select * from test where c1='a';

此時選擇了Index Seek，再回表的操作，

(2) 構造where varchar=nvarchar，

程式碼語言：javascript

複製

select * from test where c2=N'a';

我們看到執行計劃中提醒表示式列出現了型別轉換，這會影響執行計劃選擇“SeekPlan”，執行了CONVERT_IMPLICIT函式的列就是左值c2，強制轉換為nvarchar，“SeekPlan”的執行計劃，我理解就是Oracle中的Index Unique Scan或Index Range Scan，而且當前確實選擇了全表掃描，Table Scan，這就是隱式轉換，導致不能使用索引的場景，

測試場景2

建立一個Latin1_General_CP1_CI_AS排序規則的資料庫，和場景1相同，測試表如下，一個欄位是varchar，一個欄位是nvarchar，都建立了索引，

程式碼語言：javascript

複製

create table test(c1 nvarchar(200), c2 varchar(200));
insert into test(c1,c2) select cast(a.name as nvarchar(200)), a.name from master.dbo.spt_values a where a.number<10000;
create nonclustered index idx_test_01 on test(c1);
create nonclustered index idx_test_02 on test(c2);

(1) 構造where nvarchar=varchar，

程式碼語言：javascript

複製

select * from test where c1='a';

效果和場景1是相同的，此時選擇了Index Seek，再回表的操作，

(2) 構造where varchar=nvarchar，

程式碼語言：javascript

複製

select * from test where c2=N'a';

這時就可以看出一些不同了，場景1中相同語句，因為隱式轉換，導致用了Table Scan，而此處，雖然謂詞提示CONVERT_IMPLICIT(c2)，但未作為Warning，而且執行計劃還是使用的Index Seek，路徑上和場景1稍有不同，我猜這個是不是因為CONVERT_IMPLICIT的使用導致的？有知道的朋友，可以介紹下，

我看了下，我們的測試庫，常用的排序規則，是Chinese_PRC_BIN，效果和場景2是相同的。

Jonathan Kehayias在這篇文章中，提到了SQL_Latin1_General_CP1_CI_AS和Latin1_General_CP1_CI_AS這兩種排序規則不同資料型別的轉換關係，如下所示，

P.S.

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

(1) SQL_Latin_General_CP1_CI_AS排序規則，

varchar到nvarchar的隱式轉換，是黃色的，意思是Causes Scan，即忽略索引，

(2) Latin_General_CI_AS排序規則，

varchar到nvarchar的隱式轉換，是綠色的，允許用Seek，

因此在SQL Server中，不同的排序規則，隱式轉換的影響可能是不同的，有的會影響索引的選擇，有的就無影響，我不知道SQL Server為什麼這麼多排序規則，我也不知道有沒有官方文件列出哪些排序規則對隱式轉換是敏感的，

因此這就給用了隱式轉換的應用帶來了風險，尤其是開發、測試、生產資料庫環境的排序規則不同的情況下，可能沒人注意排序規則，但是隱式轉換的影響可能就會不同，這就像定時炸彈，或許測試環境，隱式轉換沒關係，速度槓槓的，但到了生產，隱式轉換就開始起作用，將原本能索引掃描的強制改為了全表掃描，對系統的效能就會產生衝擊。

其實針對這類的問題，最佳方案就是能規範日常的設計和開發，定義合適的欄位型別，程式中的變數使用和定義相同的型別，無論用什麼排序規則，可以說就沒隱式轉換什麼事兒了。

退而求其次，如果不能做到規範的設計和開發，至少在開發測試的階段，或者透過工具，或者透過人肉，檢索下當前系統中用了全表掃描的語句，再根據欄位是否存在索引，判斷是否因為書寫不當造成了隱式轉換。