Data Types

Data in SQL Server is stored in tables. Each table has a fixed number of columns and each column has a data type. This data type can be either system defined or user defined. The data type of a column specifies the type of data the column can hold and the storage space required to hold that value which is specified as the length of the column.

System Data Types:

The SQL Server by default provides mostly used data types which are available during the installation. The following are the system data types.

DATE TYPE	DESCRIPTION	STORAGE SIZE
char(n)	fixed length character data	n bytes
varchar(n)	variable length character data	0 - n bytes
integer	used to store just numbers	4 bytes (
smallint	used to store numbers	2 bytes (-32,768 to +32,768)
tinyint	used to store positive numbers	1 byte (0 and 255)
float	used to store the decimal numbers
binary
varbinary
bit	store binary digit (0 or 1)
money	used to store currency	8 bytes
datetime	date with time	8 bytes
text	large text
image	to store the image data
timestamp

User Defined Data types:
User defined data types provide column integrity. This feature provides a higher level of consistency and makes database maintenance easier. Take the case of PART_NO. The column PART_NO may occur at various places in a table. PART_NO has been defined as char(10). If the length of the PART_NO needs to be increased, it has to be now done across all tables where PART_NO field exists. To enforce consistency across all tables for a specific column definition, a User defined data type can be created. The user defined datatype can allow NULL or NOT NULL values. Now this data type can be bound to the PART_No column.

Creating User Defined Data types
You can create User Defined data types using the SP_ADDTYPE system stored procedure.

Syntax
SP_ADDTYPE < type > < phytype(length), < NULL/NOT NULL>

Example
In the customer table assume that phone column has to be included with the type phone_type
sp_addtype phone_type, 'char(10)', NULL

Now how will we use this?. For example, if you are creating the table called customer with phone, as a column to be associated with the phone_type data type.

create table customer
( customer_no integer,
customer_name varchar(40),
phone phone_type)

After try sp_help customer you can see the data type of phone column as phone_type

Dropping a User Defined data type:
To drop a user defined data type we should use the SP_DROPTYPE system stored procedure.

Syntax
SP_DROPTYPE <type>

Example
To drop a data type,first of all there should not be any column which is associated to this data type.

sp_droptype phone_type.

It can be dropped only if it is not bound to any column in a table.