Overview
Do you have lots of tables that reference each other using ID fields? Are they of different types, such as nvarchar(10) and int? Do you sometimes find that it can be hard to remember how many characters should be in the type? And what about when you decide that you need a bigger type, such as nvarchar(30)? How can you ensure that your database design remains consistent?
Alias Data Types and the CREATE TYPE statement
An Alias Data Type is a type defined by you in SQL Server that refers to an internal data type. You can use it anywhere you would use a normal SQL type, except in table variables. For example, the following command declares adtCustomerID as a non-NULL nvarchar(10):
CREATE TYPE [dbo].[adtCustomerID]
FROM nvarchar(10)
NOT NULL
You can then use the type adtCustomerID in your table definitions and stored procedures, never worrying about how many characters are in the type:
CREATE TABLE [dbo].[customers] (
customer_id adtCustomerID PRIMARY KEY,
customer_name nvarchar(100),
... )
CREATE TABLE [dbo].[orders] (
order_id adtOrderID PRIMARY KEY,
customer_id adtCustomerID,
... )
Caveat
You cannot change a type while it is in use. This means that you will have to change stored procedures (etc.) and tables to use the underlying data type before changing it, so type definitions are most useful when they are not going to change, such as in key fields.
References
Versions
Metadata
- Categories: SQL Server, IT Management, Software Development, Alias Data Types
- Additional keywords: simplicity, CREATE TYPE, user defined type, best practice, primer
- Technorati Tags:
SQL,
SQL Server,
software development,
architecture,
database design,
CREATE TYPE