Datatypes specify the data characteristics of columns, stored-procedure parameters, and local variables.
SQL Server supplies various system datatypes, which can be categorized by the type of data stored within the datatype. In addition to system-supplied datatypes, SQL Server allows user-defined datatypes, which are based on the system datatypes. For details about user-defined datatypes, see the sp_addtype system stored procedure.
In SQL Server 6.0, all system datatype names are case-insensitive. This change allows table definitions, variable declarations, and procedure parameters to include uppercase or lowercase datatype definitions. Because datatypes are case-insensitive, identical user-defined datatype names with only differing capitalization are not allowed for user-defined datatypes.
Important An existing user-defined datatype whose name conflicts with that of another user-defined datatype or system-supplied datatype will no longer be referenced. If an existing user-defined datatype is found within a table, view, or procedure definition and has a conflict, the user-defined datatype will be mapped to the system datatype and no longer reference the user-defined datatype.
These are the system datatypes:
| Type of data | System-supplied datatype |
|---|---|
| Binary | binary[(n)] varbinary[(n)] |
| Character | char[(n)] varchar[(n)] |
| Date and time | datetime smalldatetime |
| Exact numeric | decimal[(p[, s])] numeric[(p[, s])] |
| Approximate numeric | float[(n)] real |
| Integer | int smallint tinyint |
| Monetary | money smallmoney |
| Special | bit timestamp user-defined datatypes |
| Text and image | text image |
| Synonyms | binary varying for varbinary character for char character for char (1) character (n) for char (n) character varying (n) for varchar (n) dec for decimal integer for int double precision for float float [(n)] for n = 1–7 for real float [(n)] for n = 8–15 for float |