Data Types (Database Engine)
Objects that contain data have an associated data type that defines the kind of data; for example, character, integer, or binary, 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.
- The length or size of the stored value.
- The precision of the number (numeric data types only).
- The scale of the number (numeric data types only).
For more information about data type precision, scale and length see Precision, Scale, and Length (Transact-SQL).
Transact-SQL has these system data types.
bigint |
binary |
bit |
char |
cursor |
datetime |
decimal |
float |
image |
int |
money |
nchar |
ntext |
numeric |
nvarchar |
real |
smalldatetime |
smallint |
smallmoney |
sql_variant |
table |
text |
timestamp |
tinyint |
varbinary |
varchar |
uniqueidentifier |
xml |
|
|
All data stored in Microsoft SQL Server 2005 must be compatible with one of these base data types. The cursor data type is the only system data type that cannot be assigned to a table column. It can be used only with variables and stored procedure parameters.
Several base data types have synonyms (for example, rowversion is a synonym for timestamp, and national character varying is a synonym for nvarchar). For more information about the behavior of synonyms, see Data Type Synonyms (Transact-SQL).
Two kinds of user-defined data types can also be created:
Alias data types are created from base data types. 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. For example:
-- Create a birthday datetype that allows nulls. CREATE TYPE birthday FROM 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)For more information, see Working with Alias Data Types.
CLR user-defined data types are based on data types created in managed code and uploaded in a SQL Server assembly. For more information, see Working with CLR User-defined Types.
See Also
Concepts
Using Binary Data
Using char and varchar Data
Using Date and Time Data
Using Integer Data
Using decimal, float, and real Data
Using Monetary Data
Using Large-Value Data Types
Using text and image Data
Using uniqueidentifier Data
Using Special Data
Using Unicode Data
Data Type Conversion (Database Engine)
Other Resources
CREATE TABLE (Transact-SQL)
CREATE TYPE (Transact-SQL)
Data Types (Transact-SQL)
Designing Tables
sp_addtype (Transact-SQL)