Using Data Types

Objects that contain data have an associated data type that defines the kind of data (character, integer, binary, and so on) the object can contain. The following objects have data types:

Columns in tables and views.
Parameters in stored procedures.
Variables.
Transact-SQL functions that return one or more data values of a specific data type.
Stored procedures that have a return code, which always has an integer data type.

Assigning a data type to an object defines four attributes of the object:

The kind of data contained by the object, for example character, integer, or binary.
The length of the stored value, or its size.
The length of an image, binary, and varbinary data type is defined in bytes. The length of any of the numeric data types is the number of bytes required to hold the number of digits allowed for that data type. The length of the character string and Unicode data types is defined in characters.
The precision of the number (numeric data types only).
The precision is the number of digits the number can contain. For example, a smallint object can hold up to 5 digits, so it has a precision of 5.
The scale of the number (numeric data types only).
The scale is the number of digits that can be stored to the right of the decimal point. For example, an int object cannot accept a decimal point and has a scale of 0. A money object can have up to 4 digits to the right of the decimal point and has a scale of 4.

If an object is defined as money, it can contain up to 19 digits, 4 of which can be to the right of the decimal. The object uses 8 bytes to store the data. The money data type therefore has a precision of 19, a scale of 4, and a length of 8.

Transact-SQL has these base data types:

binary	bit	char	cursor	datetime
decimal	float	image	int	money
nchar	ntext	nvarchar	real	smalldatetime
smallint	smallmoney	text	timestamp	tinyint
varbinary	varchar	uniqueidentifier

All data stored in Microsoft® SQL Server™ must be compatible with one of these base data types. The cursor data type is the only base data type that cannot be assigned to a table column. It can be used only for variables and stored procedure parameters.

Several base data types have synonyms, for example numeric is a synonym for decimal and national character varying is a synonym for nvarchar.

User-defined data types can also be created, for example:

-- Create a birthday datetype that allows nulls.

EXEC sp_addtype birthday, datetime, 'NULL'

GO

-- Create a table using the new data type.

CREATE TABLE employee

`emp_id`	`char(5)`
`emp_first_name`	`char(30)`
`emp_last_name`	`char(40)`
`emp_birthday`	`birthday`

User-defined data types are always defined in terms of a base data type. They provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. This can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type.

SQL Server installations include a user-defined data type named sysname. sysname is used for table columns, variables, and stored procedure parameters that store object names. The exact definition of sysname is related to the rules for identifiers; therefore, it can vary between versions of SQL Server. In SQL Server version 7.0 sysname is defined as nvarchar(128). SQL Server versions up to 6.5 support only smaller identifiers; in these, sysname is defined as varchar(30).

Using Data Types

See Also