主页 / dba / 问题 / 276617
Accepted
J.D.
Asked: 2020-10-07 05:27:55 +0800 CST

当这些 UDDT 的字段用作谓词时，用户定义的数据类型是否总是会导致发生隐式转换？

  • 772

我有一个数据库（来自供应商应用程序），由于某种未知原因，几乎每一列都有一个 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。

sql-server sql-server-2016

2 个回答

  1. Best Answer

    在这种情况下，我无法重现任何警告或性能差异，即使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
    • 2

  2. 没有性能问题UDDTs。UDDTs在谓词中使用时没有警告消息。

    原因 ：

    select * from sys.columns

    在此查询中，请注意system_type_id& user_type_id.Sql optmizer 已经知道并使用系统数据类型。

    • 1

相关问题