在SQL Server中，sys.types表中的user_type_id和system_type_id有什么区别？

Question

在SQL Server中，sys.types视图中的user_type_id和system_type_id有什么区别？

我想通过将sys.columns与sys.types进行内连接来获取用户表中列的数据类型，但这两个视图都有user_type_id和system_type_id字段，哪一个应该使用？

- Josephchan

---

你看过文档了吗？ - Damien_The_Unbeliever

Answer 1

几乎永远不要使用 sys.columns.system_type_id = sys.types.system_type_id 进行连接。这会导致在用户定义类型的情况下出现重复记录。

有两个 JOIN 是有意义的。对于内置类型，它们都可以工作。

1. sys.columns.user_type_id = sys.types.user_type_id

   对于内置类型，它返回内置类型。

   对于用户定义类型，它返回用户定义类型。

2. sys.columns.system_type_id = sys.types.user_type_id

   对于内置类型，它返回内置类型。

   对于用户定义类型，它返回内置基本类型。例如，如果您想获取所有varchar列，包括基于 varchar 的所有用户定义列，则可能有意义。

- Heinzi

Answer 2

来自Heinzi的回答（2014年5月5日）：

sys.columns.system_type_id = sys.types.user_type_id

对于内置类型，它返回内置类型。

对于用户定义的类型，它返回内置基本类型。例如，如果您想获取所有varchar列，包括基于varchar的所有用户定义的列，则这可能是有意义的。

我想补充一点，如果您要列出所有列及其数据类型，则此（第二个）解决方案不会是一个完整的解决方案。

自SQL Server 2008以来，空间和分层数据被引入为（显然是）内置的用户定义数据类型。这些从未与任何基础类型相关联。这意味着尽管它们具有system_type_id（即240），但该ID不对应于任何user_type_id。

换句话说，尝试将system_type_id列与user_type_id列连接将从查询结果中删除所有具有空间或分层数据类型的记录。

如果您只对内置基本类型感兴趣，但也想查看包含空间和分层数据的列的结果，则可以使用以下修改后的连接：

select t.name, ...
from sys.columns c join sys.types t
on c.system_type_id = t.user_type_id
   or (c.system_type_id = 240 and c.user_type_id = t.user_type_id)

这将生成包括层次结构和空间数据在内的所有列的结果。

- Charles M