User defined data type concepts, syntax and an example

User defined data type concepts, syntax and an example.

User defined data types are based on system data types. It should be used when multiple tables need to store same type of data in a column and the user needs to ensure that all these columns have exactly same data type, length and nullability.

E.g.: Postal_code can be created based on char data type. One needs to provide Name, system data type, and nullability parameters while creating a user defined data type.

Syntax
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]

User defined data type concepts, syntax and an example.

It extends base type datatypes functionality.

It is create when particular datatype format has to be used across many tables in the database.
If a column called EmpId appears in many tables of int type, create EmpId of int and use it across all your tables.

User defined data type concepts, syntax and an example.

User defined data types are most commonly used when consistency among several tables storing the same type of data is desired. User defined data types can also be used when several tables must store the same type of data in a column .Consistency in terms of same data type, length, and null ability is required. The user defined data type in SQL is based on system data type. When a user defined data type is created it must have a name, a system data type, whether or nor it can accept NULL values.

Syntax:
Sp_addtype is used to create a user defined data type in transact SQL.

Example:
To create a user defined data type for postal code.
Sp_addtype postal_Code, ‘varchar(10)’ , ‘NOT NULL’
Concepts behind placing indexes with filegroups
Indexes can be placed on different file groups to increase performance. By default, indexes are created on the same file group as the base table........
Explain how to make views updatable in SQL Server 2000
INSTEAD OF Triggers: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.......
Query optimization tips
Use indexes. Specify only the columns necessary while using select query.......
Post your comment