當這些 UDDT 的欄位用作謂詞時，使用者定義的數據類型是否總是會導致發生隱式轉換？

我有一個數據庫（來自供應商應用程序），由於某種未知原因，幾乎每一列都有一個 UDDT，即使 UDDT 在邏輯上與其實現的基礎類型相同。例如，此數據庫中的列可能是 UDDT bTinyInt(TinyInt)。

此數據庫中的兩個表具有共享完全相同的 UDDT 的列。例如TableA.Field1是 類型bVarchar(VARCHAR(10))並且TableB.Field2也是bVarchar(VARCHAR(10))。

當我通過上述欄位將這兩個表連接在一起時，我注意到執行計劃中的警告指出基數問題，因為這些欄位被隱式轉換為VARCHAR(MAX).

UDDT 在用作謂詞時是否總是會隱式轉換，即使在比較相同的 UDDT 時也是如此？

也不確定它是否也很重要，但是這兩個表所屬的數據庫是 collat​​ion Latin1_General_BIN。

在這種情況下，我無法重現任何警告或性能差異，即使bVarchar加入varchar(10). 例如

create database tt 

alter database tt collate Latin1_General_BIN
use tt 
go
create type bVarchar from varchar(10)

create table a(id int primary key identity, b bVarchar, c varchar(10));
create table b(id int primary key identity, b bVarchar, c varchar(10));

with q as
(
 select top 1000000 row_number() over (order by (select null)) i
 from sys.messages, sys.objects 
)
insert into a (b,c) 
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;  

with q as
(
 select top 1000000 row_number() over (order by (select null)) i
 from sys.messages, sys.objects 
)
insert into b (b,c) 
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;  

go


set statistics time on
set statistics io on

select count(*)
from a
join b 
 on a.b = b.b

select count(*)
from a
join b 
 on a.c = b.c

select count(*)
from a
join b 
 on a.b = b.c

set statistics time off
set statistics io off

輸出

   SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
  CPU time = 360 ms, elapsed time = 360 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 0 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 3510, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 2656, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
  CPU time = 268 ms,  elapsed time = 117 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
  CPU time = 297 ms,  elapsed time = 184 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

SQL Server Execution Times:
  CPU time = 280 ms,  elapsed time = 111 ms.

Completion time: 2020-10-17T09:56:35.6734439-05:00

引用自：https://dba.stackexchange.com/questions/276617