SPS Home    >   Dgreath    >   RDBMS    >   Datatypes

RELATIONAL DATABASE DATATYPES

The following table presents the various datatypes, minimum and maximum values, and physical size in bytes:

Relational Database System Datatypes

CATEGORY SYSTEM
DATATYPE		 MIN VALUE MAX VALUE SIZE
BYTES		 DESCRIPTION
EXACT
NUMERIC		 TINYINT 0 255 1 Unsigned integer
SMALLINT -32,768 +32,768 2 Signed integer
INT -2,147,483,648 +2,147,483,647 4 Signed integer
BIGINT -2^63 +2^63 - 1 8 Signed integer
NUMERIC(n,m)
DECIMAL(n,m)		 -10^38 +10^38 - 1 5 - 17 Fixed precision and scale numeric value
MONEY -2^63 +2^63 - 1 8 Monetary value
SMALLMONEY -214,768.3648 +214,748.3647 4 Monetary value
BIT 0 1 1 Boolean value
APPROX.
NUMERIC		 FLOAT -1.79^308 +1.79^308 8 Large floating point numbers
REAL -3.40^38 +3.40^38 4 Small floating point numbers
DATE
   &
TIME		 DATETIME 1/1/1753 12/31/9999 8 Long dates
SMALLDATETIME 1/1/1900 6/6/2079 4 Short dates
CHARACTER
STRINGS		 CHAR(s) 1 8,000 characters Note 1 Fixed length character
VARCHAR(s) 1 8,000 characters Notes 1,4 Variable length character
NCHAR(s) 1 4,000 characters Notes 1, 3 Fixed length unicode character
NVARCHAR(s) 1 4,000 characters Notes 1,3,4 Variable length unicode character
TEXT 1 2GB Note 2 Large block character
NTEXT 1 1GB Notes 2, 3 Large block unicode character
BINARY
STRINGS		 BINARY(t) 1 8,000 bytes Note 1 Fixed length binary data
VARBINARY(t) 1 8,000 bytes Note 1, 4 Variable length binary data
IMAGE 1 2 GB Note 2 Binary Large Object (BLOB)
SPECIAL SQL_VARIANT Note 5 The universal datatype
TABLE Note 6 Temporary storage of a result set
ROWVERSION
TIMESTAMP		 8 Binary data to ensure row uniqueness
UNIQUEIDENTIFIER 16 Globally Unique Identifier (GUID)
Notes:
  1. 8,000 bytes maximum physical size.
  2. 2^30 -1 bytes maximum physical size.
  3. Unicode characters require two bytes each.
  4. Variable length fields are the actual length plus two bytes to hold the size. Variable fields are ideally suited for attributes where the actual length (such as a name) is unknown. Use fixed length fields for attributes where the exact size is known (such as a zipcode).
  5. This variable length field can contain any mix of other datatypes except text, ntext, image, and timestamp. It's physical size is the largest of the contained datatypes.
  6. The table datatype is used for temporary result storage. It may not be used to specify a column and its size is variable.
  7. Datatypes decimal, numeric, char, varchar, nchar, nvarchar, binary, and varbinary must include a size specification as shown.

n = number of  total digits (min 1, max 38)
m = number of digits right of decimal point (min 0, max 37 )
s = number of characters
t = number of bytes

Definition and deletion of user datatypes:
To avoid the possibility of data inconsistency, users can create standardized datatypes for things like zipcodes, phone numbers, social security numbers, and the like. To do so, use the stored procedure "sp_addtype" and specify the identity, datatype and length, and nullability (NULL or NOT NULL) as follows:

USE dbname
sp_addtype ZIPCODE, 'CHAR(5)', NULL
 

Note: the single quotes around the datatype specification are required.

Once a datatype has been created, it can be used in the same way as the system datatypes.

User datatypes can be deleted using the "sp_droptype" stored procedure as follows:

USE dbname
sp_droptype ZIPCODE