Creating and Using Your Own Datatypes

If you've worked with any programming language before, the idea of a user-defined type is already familiar: a named type you define yourself, built on top of a language's existing primitive types, so that data representing the same kind of thing is declared consistently everywhere it's used. SQL Server 2025 offers the same idea for table columns, through the CREATE TYPE statement.

A user-defined type is always built from an existing SQL Server base type; SQL Server has no built-in datatype called address, for instance, but you can create one yourself based on VARCHAR, giving every column that uses it a consistent length and nullability without repeating the same declaration by hand everywhere it's needed.

CREATE TYPE address FROM VARCHAR(30) NOT NULL;

This creates a type named address, based on VARCHAR(30), that never allows NULL. Once created, it can be used anywhere an ordinary datatype would be used, in a table column, a variable declaration, or a stored procedure parameter:

CREATE TABLE dbo.Customers
(
    CustomerID      INT NOT NULL PRIMARY KEY,
    CustomerAddress address NOT NULL
);

A column declared this way behaves exactly like a VARCHAR(30) NOT NULL column. address is really just a name for that combination of base type, length, and nullability, not a new kind of storage or a new form of validation; SQL Server doesn't check anything about an address value beyond the ordinary rules that already apply to any VARCHAR(30) NOT NULL column.

CREATE TYPE is the current, supported way to do this. An older system stored procedure, sp_addtype, did the same job in earlier versions of SQL Server, but it's deprecated and shouldn't be used in new development; any script still calling sp_addtype is a candidate for conversion to CREATE TYPE the next time it's touched.

The real benefit of a scalar type shows up once several columns, in the same table or across many tables, are all meant to hold the same kind of value. Declaring both address lines in an employee table with the same type keeps them in sync automatically:

CREATE TABLE dbo.Employee
(
    EmployeeID   INT NOT NULL PRIMARY KEY,
    AddressLine1 address NOT NULL,  -- uses the user-defined type
    AddressLine2 address NULL       -- would need a separate nullable type, see below
);

Every column declared as address is guaranteed to agree on length and nullability, rather than relying on every table definition across the database repeating VARCHAR(30) NOT NULL correctly by hand, which is exactly the kind of small, easy-to-miss inconsistency that creeps into a schema over time as more tables and more developers touch it. Note that AddressLine2 above can't actually be declared NULL this way, since the address type itself was created as NOT NULL; a genuinely nullable address column needs its own, separately created type.

One limitation is worth knowing up front: there's no ALTER TYPE statement for changing a scalar type's underlying definition. If address needs to allow NULL after all, the only way to do that is to drop the existing type and create a new one in its place:

DROP TYPE dbo.address;

CREATE TYPE address FROM VARCHAR(30) NULL;

DROP TYPE only succeeds if nothing in the database still references the type, no table column, no variable declaration, nothing at all. In practice, that usually means removing the old type from every column that uses it first, which can be a meaningful amount of work on a type that's already in wide use across a schema. Choosing a scalar type's definition carefully up front, rather than discovering a missing requirement after dozens of tables already depend on it, avoids needing to unwind that work later. If nullability genuinely needs to vary, creating two separate types from the start, say address and addressNullable, is usually simpler than trying to retrofit one type to cover both cases.

A scalar type isn't limited to table columns; it can declare a local variable or a stored procedure parameter the same way any built-in datatype would:

DECLARE @myAddress address;
SET @myAddress = '123 Main St';

SELECT @myAddress AS AddressValue;

This is particularly useful for a stored procedure that accepts or returns the same kind of value in several places; declaring the parameter as address rather than VARCHAR(30) keeps the procedure's definition in step with the tables it reads from and writes to, so a future change to the underlying length only has to be reconsidered in one place conceptually, even though, as the previous section covered, actually changing it still means dropping and recreating the type.

It's worth being clear about one thing a user-defined type does not do: CREATE TYPE has no way to attach a default value to the type itself, the way the much older, now-deprecated sp_addtype/sp_bindefault pair once allowed a default to be bound directly to a type and inherited by every column that used it. In current SQL Server, a DEFAULT is always a per-column constraint, applied individually on each table that uses the type, exactly as covered in an earlier lesson in this module:

CREATE TABLE dbo.Customers
(
    CustomerID      INT NOT NULL PRIMARY KEY,
    CustomerAddress address NOT NULL
        CONSTRAINT DF_Customers_CustomerAddress DEFAULT ('Unknown')
);

Two tables can use the same address type with two entirely different defaults, or no default at all, since the type only standardizes the column's base type, length, and nullability, not what happens when a value is left unspecified.

Everything covered so far in this lesson, address included, is what SQL Server calls an alias type: a named shorthand for an existing base type, its length, and its nullability, with no behavior of its own beyond that. SQL Server also supports a more powerful kind of user-defined type, a CLR user-defined type, built from a .NET assembly and registered with CREATE TYPE ... EXTERNAL NAME, that can carry its own methods, its own internal structure, and genuinely custom validation logic, a type representing a 3D coordinate with its own distance-calculation method, for instance.

CLR types are considerably more work to build and deploy, since they require writing and registering actual .NET code alongside the database, and they're correspondingly rare outside specialized cases. For the overwhelming majority of situations, including every example in this lesson, an alias type built with CREATE TYPE ... FROM is simpler, requires no code outside T-SQL, and is the right starting point; reach for a CLR type only when a column genuinely needs behavior that an alias over an existing base type can't express.

CREATE TYPE can also define a structured table type, rather than a single scalar value:

CREATE TYPE AddressTableType AS TABLE
(
    Street  VARCHAR(50),
    City    VARCHAR(30),
    ZipCode VARCHAR(10)
);

A table type like this one solves a different problem from a scalar type, and the two are easy to confuse. It can't be used as a column's datatype:

-- fails: a table type cannot be used to declare an ordinary column
CREATE TABLE dbo.Addresses
(
    AddressID INT NOT NULL PRIMARY KEY,
    Location  AddressTableType
);

It exists specifically to let an entire result set be passed into a stored procedure as a single table-valued parameter instead:

CREATE PROCEDURE dbo.InsertAddresses
    @Addresses AddressTableType READONLY
AS
BEGIN
    INSERT INTO dbo.Addresses (Street, City, ZipCode)
    SELECT Street, City, ZipCode FROM @Addresses;
END;

A scalar type describes one value's shape; a table type describes a whole table's shape, useful for sending a caller's entire batch of rows to a stored procedure in a single call rather than looping over individual parameterized inserts. Only the scalar kind can ever be used to declare an ordinary column.

To see which user-defined types already exist in a database, rather than relying on institutional memory or hunting through old scripts, query sys.types directly:

SELECT name, system_type_id, max_length, is_nullable
FROM sys.types
WHERE is_user_defined = 1;

Removing a type that's no longer needed uses DROP TYPE, with the same restriction noted above, it has to be completely unreferenced first:

DROP TYPE dbo.AddressTableType;

User-defined types are database-scoped: a type created in one database isn't available in another without being created there separately. A multi-database system that wants the same type available everywhere needs to run the same CREATE TYPE statement against each database individually, there's no equivalent of a server-wide type.

A few mistakes come up often enough with user-defined types to be worth calling out directly.

• Creating the table before the type exists. CREATE TYPE has to run and succeed before any CREATE TABLE statement that references it; a deployment script that creates tables in alphabetical order, say, can fail here if a type's own script happens to sort after the first table that needs it.
• Forgetting the type in a new environment's deployment script. A type is easy to overlook when scripting out a database's schema for a new environment, since it isn't a table, a view, or a stored procedure, the categories most deployment tooling is built around checking first. A type missing from a fresh database's setup script surfaces as a confusing "type does not exist" error on the very first CREATE TABLE that needs it.
• Assuming the type enforces more than its base type does. As covered earlier in this lesson, an alias type only fixes a base type, a length, and a nullability setting; it doesn't add pattern validation, range checking, or any other business rule. A column genuinely needing that kind of check still needs a CHECK constraint of its own, type or no type.
• Treating a table type as interchangeable with a scalar type. The error message SQL Server returns for trying to use a table type as a column's datatype is not always an obvious one at first glance; keeping the two kinds of CREATE TYPE conceptually separate avoids the confusion entirely.

A user-defined type isn't the only way to get a consistent column declaration, and it isn't always the right choice. The alternative is simply declaring the base type directly, every time:

CREATE TABLE dbo.Employee
(
    AddressLine1 VARCHAR(30) NOT NULL,
    AddressLine2 VARCHAR(30) NOT NULL
);

This avoids any dependency on a type existing beforehand, and it sidesteps the no-ALTER TYPE limitation entirely, since an ordinary column's length or nullability can be changed directly with ALTER TABLE ... ALTER COLUMN, covered in an earlier lesson in this module, without the all-references-must-be-removed-first restriction a user-defined type carries.

A scalar user-defined type earns its keep when the same declaration needs to stay consistent across many columns or many tables, and especially when a team of developers, rather than one person, is the one creating new tables that need to agree on it. For a one-off column, or a table whose definition is likely to change on its own schedule independent of anything else in the database, declaring the base type inline is usually the simpler, lower-friction choice.

This lesson closes out a module that's covered a table column's full set of attributes one at a time: its datatype, its length, precision, and scale, whether it allows NULL, its default value, the IDENTITY property, the ROWGUIDCOL flag, and now a user-defined type standing in for a base type. A single, realistic table draws on most of them at once:

CREATE TYPE address FROM VARCHAR(100) NOT NULL;

CREATE TABLE dbo.Orders
(
    OrderID     INT IDENTITY(1, 1) NOT NULL
        CONSTRAINT PK_Orders_OrderID PRIMARY KEY CLUSTERED,

    OrderGuid   UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL
        CONSTRAINT UQ_Orders_OrderGuid UNIQUE NONCLUSTERED
        CONSTRAINT DF_Orders_OrderGuid DEFAULT (NEWSEQUENTIALID()),

    ShipToAddress address NOT NULL,

    DiscountPercent DECIMAL(5, 2) NOT NULL
        CONSTRAINT DF_Orders_DiscountPercent DEFAULT (0.00),

    OrderStatus VARCHAR(20) NOT NULL
        CONSTRAINT DF_Orders_OrderStatus DEFAULT ('Pending'),

    CreatedAt   DATETIME2(3) NOT NULL
        CONSTRAINT DF_Orders_CreatedAt DEFAULT (SYSDATETIME())
);

OrderID covers identity and primary-key behavior from two lessons back; OrderGuid covers ROWGUIDCOL for a table that might eventually need merge replication; ShipToAddress covers this lesson's user-defined type; DiscountPercent and CreatedAt cover precision/scale and fractional-second precision; and every column's nullability and default are specified explicitly, rather than left to whatever a session or database default happens to be, the theme that ran through this entire module from the first lesson on datatypes onward.

Click the Exercise link below to practice creating your own datatype.
Create Transact Sql Datatype - Exercise

In the next lesson, you'll review the material covered across this module so far.