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