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

Question

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?

Answer 1

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.

1. sys.columns.user_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 user-defined type.

2. 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.