In SQL Server, you can use the TYPE_ID() function to return the ID of a data type, based on its name. This can be useful when querying a system view that stores a data type’s ID but not its name. It’s usually easier to remember the name. Not so easy to remember the ID.

You can use TYPE_ID() for system data types and user-defined data types.

Example 1 – Basic Usage

Here’s a basic example to demonstrate how it works.

SELECT TYPE_ID('varchar') AS Result;

Result:

+----------+
| Result   |
|----------|
| 167      |
+----------+

This result tells us that the varchar data type has an ID of 167.

Example 2 – A Database Example

Here’s an example of using TYPE_ID() in a WHERE clause to filter the results to just a given data type.

SELECT 
  o.name AS [Object Name], 
  c.name AS [Column Name],  
  c.user_type_id AS [Type ID],
  TYPE_NAME(c.user_type_id) AS [Type Name]
FROM sys.objects AS o   
JOIN sys.columns AS c  ON o.object_id = c.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID('varchar');

Result:

+---------------+---------------+-----------+-------------+
| Object Name   | Column Name   | Type ID   | Type Name   |
|---------------+---------------+-----------+-------------|
| Individual    | FirstName     | 167       | varchar     |
| Individual    | LastName      | 167       | varchar     |
| Occupation    | JobTitle      | 167       | varchar     |
| Event         | EventName     | 167       | varchar     |
| Scoreboard    | Player        | 167       | varchar     |
| Team          | TeamName      | 167       | varchar     |
| Client        | FirstName     | 167       | varchar     |
| Client        | LastName      | 167       | varchar     |
| Colors        | ColorName     | 167       | varchar     |
+---------------+---------------+-----------+-------------+

You’ll notice that I’m also using TYPE_NAME() in this example to return the name based on its ID.

Example 3 – User-Defined Types

You can also use TYPE_ID() for user-defined types. Here’s an example that includes a user-defined type alias in the results.

SELECT 
  o.name AS [Object Name], 
  c.name AS [Column Name],  
  c.user_type_id AS [Type ID],
  TYPE_NAME(c.user_type_id) AS [Type Name],
  CASE 
    WHEN t.is_user_defined = 1 THEN 'Yes'
    ELSE 'No' 
  END AS [User Defined?]
FROM sys.objects AS o   
JOIN sys.columns AS c  ON o.object_id = c.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID('varchar')
OR c.user_type_id = TYPE_ID('clientcode');

Result:

+---------------+---------------+-----------+-------------+-----------------+
| Object Name   | Column Name   | Type ID   | Type Name   | User Defined?   |
|---------------+---------------+-----------+-------------+-----------------|
| Individual    | FirstName     | 167       | varchar     | No              |
| Individual    | LastName      | 167       | varchar     | No              |
| Occupation    | JobTitle      | 167       | varchar     | No              |
| Event         | EventName     | 167       | varchar     | No              |
| Scoreboard    | Player        | 167       | varchar     | No              |
| Team          | TeamName      | 167       | varchar     | No              |
| Client        | ClientCode    | 257       | clientcode  | Yes             |
| Client        | FirstName     | 167       | varchar     | No              |
| Client        | LastName      | 167       | varchar     | No              |
| Colors        | ColorName     | 167       | varchar     | No              |
+---------------+---------------+-----------+-------------+-----------------+

Here the clientcode type is a user-defined type alias, and it’s is_user_defined flag is 1. In this case I use a CASE expression to return Yes (and to return No if its 0).

Example 4 – Invalid Type or Insufficient Permission

If you provide an invalid type name or you don’t have sufficient permission to reference the type, the result will be NULL.

SELECT TYPE_ID('oops') AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+