in sql server what is the difference between user_type_id and system_type_id in sys.types

Publish date: 2023-12-22

What is the difference between user_type_id and system_type_id in the view of sys.types in sql server?

I want to inner join sys.columns with sys.types to get the data types of columns in a user table, but these two views both have two fields user_type_id and system_type_id, which one should be used?

2 Answers

You almost never want to join sys.columns.system_type_id = sys.types.system_type_id. This will lead to duplicate records in the case of user-defined types.

There are two JOINs which do make sense. Both of them work equivalently for built-in types.

From Heinzi's answer (May 5, 2014):

sys.columns.system_type_id = sys.types.user_type_id

For a built-in type, it returns the built-in type.

For a user-defined type, it returns the built-in base type. This might make >sense, for example, if you want to get all varchar columns, including all >user-defined columns based on varchar.

I would add that this (second) solution will not be a complete solution if you are looking to list all columns and their data types.

Since SQL Server 2008, spatial and hierarchical data have been introduced as (apparently) built-in user-defined data types. These have never been linked to any base type. This means that although they have a system_type_id (namely, 240), this id does not correspond to any user_type_id.

In other words, an attempt to join a system_type_id column with a user_type_id column will remove all records with spatial or hierarchical data types from the query results.

If you are only interested in the built-in base types, but want to see results for columns containing spatial and hierarchical data as well, you may want to use the following modified join:

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) 

This will generate results for all columns, including those with hierarchical and spatial data.

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaWlrZG2Cc36OoqVmq6GherSx0a%2Bcq2Wnna61ecisZK2glWKxqrLFnqmeppOaeqOx07CcnqZdqsCmvoytsKmdXZ6xbq3NnWSssaOpsq5507KnnmWZmXqqug%3D%3D