Export (0) Print
Expand All

Create a User-Defined Data Type Alias

This topic describes how to create a new user-defined data type alias in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

In This Topic

Limitations and Restrictions

  • The name of a user-defined data type alias must comply with the rules for identifiers.

Security

Permissions

Requires CREATE TYPE permission in the current database and ALTER permission on schema_name. If schema_name is not specified, the default name resolution rules for determining the schema for the current user apply.

Arrow icon used with Back to Top link [Top]

To create a user-defined data type

  1. 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.

  2. 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.

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

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

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

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

  7. 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.

Arrow icon used with Back to Top link [Top]

To create a user-defined data type alias

  1. Connect to the Database Engine.

  2. From the Standard bar, click New Query.

  3. 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.

CREATE TYPE ssn
FROM varchar(11) NOT NULL ;

Arrow icon used with Back to Top link[Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft