В чем разница между user_type_id и system_type_id в представлении sys.types в sql-сервере?
Я хочу, чтобы внутренние соединения sys.columns с sys.types получали типы данных столбцов в пользовательской таблице, но эти два представления имеют два поля user_type_id и system_type_id, какой из них следует использовать?
Вы почти никогда не хотите присоединяться к sys.columns.system_type_id = sys.types.system_type_id. Это приведет к повторяющимся записям в случае пользовательских типов.
Есть два JOIN, которые имеют смысл. Оба они работают одинаково для встроенных типов.
sys.columns.user_type_id = sys.types.user_type_id
Для встроенного типа он возвращает встроенный тип.
Для пользовательского типа он возвращает пользовательский тип.
sys.columns.system_type_id = sys.types.user_type_id
Для встроенного типа он возвращает встроенный тип.
Для пользовательского типа он возвращает встроенный базовый тип. Это может иметь смысл, например, если вы хотите получить все столбцы varchar, включая все пользовательские столбцы на основе varchar.
Из ответа Хайнци (5 мая 2014 года):
sys.columns.system_type_id = sys.types.user_type_id
Для встроенного типа он возвращает встроенный тип.
Для пользовательского типа он возвращает встроенный базовый тип. Это может иметь смысл, например, если вы хотите получить все столбцы varchar, включая все > пользовательские столбцы на основе varchar.
Я бы добавил, что это (второе) решение не будет полным решением, если вы хотите перечислить все столбцы и их типы данных.
Начиная с SQL Server 2008, пространственные и иерархические данные были введены как (по-видимому) встроенные пользовательские типы данных. Они никогда не были связаны с каким-либо базовым типом. Это означает, что хотя у них есть system_type_id (а именно, 240), этот идентификатор не соответствует ни одному 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)
Это приведет к получению результатов для всех столбцов, включая иерархические и пространственные данные.