Differences between table variables and custom types of temporary tables

When table variables use custom types, the type is valid in the current database.
When a temporary table uses a custom type, the type is valid in the tempdb database.

The following are three examples:

-- Test Example 1: create a custom type ssnuse tempdbgoif object_id ('tempdb .. # testtable ') is not null drop table # testtable; if exists (select 1 from sys. types where name = 'ssn') Drop type DBO. SSN; gouse testgoif exists (select 1 from sys. types where name = 'ssn') Drop type DBO. SSN; go -- create a custom type create type DBO in test. SSN from Char (9) Not null goraiserror ('table variable defined successfully',) with Nowait; declare @ testtable table (rowid int iden Tity, SSN) goraiserror ('failed to create a temporary table ', 2715) with Nowait; Create Table # testtable (rowid int identity, SSN)/* success message for defining table variables, level 16, Status 7, 3rd rows, 2nd columns, parameters, or variables: the data type SSN cannot be found. */Go

-- Test Example 2: create a custom ssnuse testgoif exists (select 1 from sys. types where name = 'ssn') Drop type DBO. SSN; gouse tempdbgoif object_id ('tempdb .. # testtable ') is not null drop table # testtable; if exists (select 1 from sys. types where name = 'ssn') Drop type DBO. SSN; go -- create a custom type create type DBO in tempdb. SSN from Char (9) Not NULL; gouse testgoraiserror ('table definition variable failed',) with Nowait; declare @ testtable ( Rowid int identity, SSN) goraiserror ('temporary table created successfully', 2715) with Nowait; Create Table # testtable (rowid int identity, SSN)/* message, level 16, Status 7, 3rd rows, 2nd columns, parameters, or variables: the data type SSN cannot be found. Temporary table created */go

-- Test example 3: create a custom ssnuse tempdbgoif object_id ('tempdb .. # testtable ') is not null drop table # testtable; if exists (select 1 from sys. types where name = 'ssn') Drop type DBO. SSN; gocreate type DBO. SSN from Char (9) Not NULL; gouse testgoif exists (select 1 from sys. types where name = 'ssn') Drop type DBO. SSN; gocreate type DBO. SSN from Char (9) Not NULL; goraiserror ('table variable defined successfully',) with Nowait; declare @ testtable table (rowid int identity, SSN) goraiserror ('temporary table created successfully',) with Nowait; Create Table # testtable (rowid int identity, SSN)/* Table variable created successfully temporary table created successfully */