Create a User-Defined Data Type Alias

In Object Explorer, expand Databases, expand a database, expand Programmability, expand Types, right-click User-Defined Data Types, and then click New User-Defined Data Type.

Allow NULLs

Specify whether the user-defined data type can accept NULL values. The nullability of an existing user-defined data type is not editable.

Data type

Select the base data type from the list box. The list box displays all data types except for the geography, geometry, hierarchyid, sysname, timestamp , and xml data types. The data type of an existing user-defined data type is not editable.

Default

Optionally select a rule or a default to bind to the user-defined data type alias.

Length/Precision

Displays the length or precision of the data type as applicable. Length applies to character-based user-defined data types; Precision applies only to numeric-based user-defined data types. The label changes depending on the data type selected earlier. This box is not editable if the length or precision of the selected data type is fixed.

Length is not displayed for nvarchar(max), varchar(max), or varbinary(max) data types.

Name

If you are creating a new user-defined data type alias, type a unique name to be used across the database to represent the user-defined data type. The maximum number of characters must match the system sysname data type. The name of an existing user-defined data type alias is not editable.

Rule

Optionally select a rule to bind to the user-defined data type alias.

Scale

Specify the maximum number of decimal digits that can be stored to the right of the decimal point.

Schema

Select a schema from a list of all schemas available to the current user. The default selection is the default schema for the current user.

Storage

Displays the maximum storage size for the user-defined data type alias. Maximum storage sizes vary, based on precision.

1 – 9	5
10 – 19	9
20 – 28	13
29 – 38	17

For nchar and nvarchar data types, the storage value is always two times the value in Length.

Storage is not displayed for nvarchar(max), varchar(max), or varbinary(max) data types.

In the New User-defined Data Type dialog box, in the Schema box, type the schema to own this data type alias, or use the browse button to select the schema.

In the Name box, type a name for the new data type alias.

In the Data type box, select the data type that the new data type alias will be based on.

Complete the Length, Precision, and Scale boxes if appropriate for that data type.

Check Allow NULLs if the new data type alias can permit NULL values.

In the Binding area, complete the Default or Rule boxes if you want to bind a default or rule to the new data type alias. Defaults and rules cannot be created in SQL Server Management Studio. Use Transact-SQL. Example code for creating defaults and rules are available in Template Explorer.

Connect to the Database Engine.

From the Standard bar, click New Query.

Copy and paste the following example into the query window and click Execute. This example creates a data type alias based on the system-supplied varchar data type. The ssn data type alias is used for columns holding 11-digit social security numbers (999-99-9999). The column cannot be NULL.