Are there differences between a table variable and a table type in Microsoft SQL Server? Or is a table type just a table variable that is defined once for broad usage?


A user-defined table type created with CREATE TYPE is only meta-data. An instance of the type is created at run time when the type is used as a local variable or table-valued parameter.

A local table variable can be declared by specifying either a table type name or inline table type definition. Both methods provide similar functionality when used only as a local variable, albeit the type name promotes reuse for broad usage as you mentioned. However, with the type name declaration method, one can also pass the table variable as a parameter to procedure or function as long as the parameter definition is of the same user-defined table type.

Table types declared with the in-line syntax cannot be used as parameter values in T-SQL. Nor can table type parameters be declared using an in-line table definition.


Every variable has a type, whether that’s an int, a date, or a table. So you can declare a variable to be a table, and specify the columns when you declare the variable. Alternatively, if you create a user defined table type for particular columns and indexes, you can reuse that type in other scenarios for other variables. But each variable is independent, the same as if you have to char(10) variables.

Not the answer you're looking for? Browse other questions tagged or ask your own question.