Latest tutorial: Making a Movieclip face another Movieclip or point on the stage | Ask Tutorial5!
 

Working with alias data types in SQL Server 2005 using T-SQL
(13 votes)
Written by MoN   
Alias data types are data types based on the system data types (see Data types in Microsoft SQL Server 2005) . They are used to refine the system supplied data  types.

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



Subscribe now via RSS feed and get all the new tutorials

written by John Fillafa , February 22, 2008

The article reference article "Working with alias data types in SQL Server 2005 using SQL Server Management Studio" -- but search for this referenced article turns up no hits. Did they forget to write it, or misspell the title, or is the site search broken?
written by Madhavi Rajgure , March 03, 2008

Hi
Please tell me how to retrieve the data from sql database to the html table in asp.net/
Thank you
written by brejk , July 31, 2008

A method for "altering" alias type:

1. Get the names of all procedures and functions that use the alias type you want to "alter". It's a matter of a simple query to catalog views.
2. Script all objects from point 1. (use OBJECT_DEFINITION function).
3. Get the names of all tables that use the alias type you want to "alter".
3. Drop all objects from point 1.
4. Perform sp_rename on the alias type.
5. Add a new alias type named just as the original alias type but with changed properties (size, precision, scale).
6. Alter all tables from point 2. and recreate all objects from point 1.
7. Drop the renamed alias type (from point 4.).

Of course you should do all this on a database in SINGLE_USER mode.
written by rocksy , August 13, 2008

How to add image data type in sql server 2005..I work with asp.net.
written by asif , November 11, 2008

how to create varbinary datatype in sqlserver2005 and insert data into it.

Do you need more help? Ask now!
 

busy
Last Updated ( Thursday, 06 September 2007 )