You can work with the alias data types using T-SQL or thru the SQL Server Management Studio. This article focus on using T-SQL. To see how to use the SQL Server Management Studio to work with the alias data types see Working with alias data types in SQL Server 2005 using SQL Server Management Studio.
As an exmaple for using alias data types is the definition of a new data type, named CountryCode, based on the char data type. The command used to create is CREATE TYPE.
The simple syntax for this command i s
CREATE TYPE [schema_name.]typename
FROM system_data_type_name [(precision,scale)] [NULL|NOT NULL]
For example
CREATE TYPE CountryCode
FROM char(2) NULL
This command will create an alias data type named CountryCode based on the char data type. The new type will be a char string with a length of 2.
The new data type will accept NULL values. If the NOT NULL attribte is specified the new data type cannot hold NULL values. If the parameter is not specified by default it will NULL
The new data type will be created in the current database and can be used only inside that database.
After you create an alias data type you can use it as you use any other data type.
For example declare as variable
DECLARE @cc as CountryCode
or create a table using the new data type
CREATE TABLE Countries
(CountryID int,
CC CountryCode)
WARNING ! Take care when creating alias data types as they cannot be modified. In order to modify an alias data type you have tofirst delete it and then recreate it. The problem is that you must also delete or modigy any table that use that data type before you can drop the data type itself.
If you are not using a data type anymore you can use the command DROP TYPE.
For example
DROP TYPE CountryCode
will delete the data type created above.
As mentioned above, data types cannot be deleted if any table references them. In order to find out what tables refenence an alias data type you can use the following command
select sys.objects.name as [table], sys.columns.name as [column] from sys.column_type_usages
join sys.systypes on sys.systypes.xusertype=sys.column_type_usages.user_type_id
join sys.columns on sys.columns.object_id=sys.column_type_usages.object_id and dys.columns.column_id=sys.column_type_usages.column_id
join sys.objects on sys.objects.object_id=sys.column_type_usages.object_id
where sys.systypes.name='data_type_name'
(replace data_type_name with the real name of the alais data type
discuss this topic to forum
