SQL Server: UDT versus UDDT?

Question

What's the difference between User-Defined Types (UDT) and User-Defined Data Types (UDDT)?

Answer 1

User-Defined Data Types (UDDT) are simple aliases, in pure T-SQL, based on the "native" types that come with SQL Server (i.e. all except the CLR types of Geography, Geometry, and Hierarchy). These are for convenience in allowing for consistency in data models, the idea being that you avoid having the same "property" across tables defined in different ways.

An example of a UDDT is defining SSN to be a CHAR(11). Or maybe ClientID would be INT.

User-Defined Types (UDT) are "complex" types written in SQLCLR (i.e. NET / CLR Integration) and are whatever you create them to be. They are what the Geography, Geometry, and Hierarchy types are based on. Their direct representation is VARBINARY, but you don't interact with them directly (not usually). Like those 3 CLR types and the XML datatype, you interact with UDTs via methods and properties that are defined in them. They can have both static and instance based methods and properties. You can even override the operators by which they are compared so that you can do special handling when used in the context of =, <, or whatever you decide to override.

An example can be an Array / Collection. You store a collection internally, and expose methods for .Add(), .GetByIndex(), etc. You can expose properties such as .Length, etc. So you would declare it like:

DECLARE @VarName SchemaName.TypeName;

You can update it through properties and methods:

SET @VarName = @VarName.Method(@var1, 'LiteralValue');

You can get values out of it through properties and methods:

SELECT @VarName.AnotherMethod(), @VarName.SomeProperty;

If you want examples that show some of these variations and that you can even play with, you can download the Free version of SQL# library (which I wrote, but the Types are free) which comes with three UDTs, one of which is Type_HashTable that has some good examples in the manual. You can find the info for Type_HashTable starting on page 178 of the manual (page 186 in the PDF).