Latest tutorial: Premium Flash Files | Ask Tutorial5! | Subscribe to RSS Register Login Find Hobbies
Advertisement

Get tutorials on EMail




Categories

Navigation

Do you need more help? You can now Ask Tutorial5! and get free support - Ask a question now!

Working with alias data types in SQL Server 2005 using T-SQL
(6 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

Do you need more help? Ask now!
 

busy
Last Updated ( Thursday, 06 September 2007 )