A domain is the set of all possible legitimate values that columns of a table may contain. Almost all DBMSs use base data types such as INT, CHAR, and DATE to define the set of possible values for a column. This method of enforcing “domain integrity” is incomplete, as can be seen from the following example.
The person table has a column, zip, that specifies the ZIP code of the city in which the person lives. This column can be defined using the SMALLINT or CHAR(5) data type. The definition with the SMALLINT data type is inaccurate, because the SMALLINT data type contains all positive and negative values between –215–1 and 215.
The definition using CHAR(5) is even more inaccurate, because all characters and special signs can also be used in such a case. Therefore, an accurate definition of ZIP codes requires defining an interval of positive integers between 00601 and 99950 and assigning it to the zip column.
CHECK constraints (defined in the CREATE TABLE or ALTER TABLE statement) can enforce more precise domain integrity because their expressions are flexible, and they are always enforced when the column is inserted or modified.
The Transact-SQL language provides support for domains by creating alias data types using the CREATE TYPE statement. The following two sections describe alias and Common Language Runtime (CLR) data types.
Alias Data Types
An alias data type is a special kind of data type that is defined by users using the existing base data types. Such a data type can be used with the CREATE TABLE statement to define one or more columns in a database.
The CREATE TYPE statement is generally used to create an alias data type. The syntax of this statement to specify an alias data type is as follows:
CREATE TYPE [ type_schema_name. ] type_name
{ [ FROM base_type [ ( precision [ , scale ] ) ] [ NULL | NOT NULL ] ]
| [ EXTERNAL NAME assembly_name [ .class_name ] ]}
Code language: PHP (php)
Example 5.14 shows the creation of an alias data type using the CREATE TYPE statement.
USE sample;
CREATE TYPE zip
FROM SMALLINT NOT NULL;
Code language: PHP (php)
Example 5.14 creates an alias type zip based on the standard data type CHAR(5). This user-defined data type can now be used as a data type of a table column, as shown in Example 5.15.
USE sample;
CREATE TABLE customer
(cust_no INT NOT NULL,
cust_name CHAR(20) NOT NULL,
city CHAR(20),
zip_code ZIP,
CHECK (zip_code BETWEEN 601 AND 99950));
Code language: PHP (php)
Example 5.15 uses the new zip data type to specify a column of the customer table. The values of this column have to be constrained to the region between 601 and 99950. As can be seen from Example 5.15, this can be done using the CHECK clause.
USE sample;
CREATE TYPE person_table_t AS TABLE
( name VARCHAR(30), salary DECIMAL(8,2));
Code language: PHP (php)
The user-defined table type called person_table_t has two columns: name and salary. The main syntactical difference in relation to alias data types is the existence of the AS TABLE clause, as can be seen in Example 5.16. User-defined table types are usually used in relation to table-valued parameters (see Chapter “Stored Procedures and User-Defined Functions”).
CLR Data Types
The CREATE TYPE statement can also be applied to create a user-defined data type using .NET. In this case, the implementation of a user-defined data type is defined in a class of an assembly in the Common Language Runtime (CLR). This means that you can use one of the .NET languages like C# or Visual Basic to implement the new data type. Further description of the user-defined data types is outside the scope of this tutorial.