SQL Server 2008 Data Types
Defining Field Types in SQL Server 2008
Contents
Data Types
Top Bottom| Data Type | Size | Range |
|---|---|---|
| TINYINT(n) | 1 byte | 0 to 255 (unsigned) |
| SMALLINT(n) | 2 bytes | -32,768 to 32,767 |
| INT(n) | 4 bytes | Approximately plus or minus 2 billion (-2,147,483,648 to 2,147,483,647) |
| BIGINT(n) | 8 bytes | Approximately plus or minus 10 billion billion or 10 million trillion or 10 thousand quadrillion or 10 quintillion (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807) |
| FLOAT(n) | 4 bytes if n < 25 or 8 bytes if 24 < n < 54 | n decimal places |
| REAL | 4 bytes | |
| DECIMAL(n,d) | 5 to 17 bytes | n digits before and after the decimal point, d decimal places |
| SMALLMONEY | 4 bytes | -214,748.3648 to 214,748.3647 |
| MONEY | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| SMALLDATETIME | 4 bytes | 01/01/1900 00:00 to 06/06/2079 23:59 |
| DATETIME | 8 bytes | 01/01/1753 to 31/12/9999 |
| DATETIME2 | 6 to 8 bytes | 01/01/0001 to 31/12/9999. Intended as a replacement for SMALLDATETIME and DATETIME |
| DATETIMEOFFSET | 8 to 10 bytes | 01/01/0001 to 31/12/9999, includes space for timezone offset |
| DATE | 3 bytes | 01/01/0001 to 31/12/9999 |
| TIME | 3 to 5 bytes | Accurate to 100 nanoseconds |
| CHAR(n) | fixed length, 1 byte per character (n), up to 8000 | Consumes n bytes of storage |
| NCHAR(n) | 2 bytes per field length (n) | Stores unicode characters |
| VARCHAR(n) | variable length, 1 byte per character (n), up to 8000 | Consumes 1 byte per character stored, up to n bytes |
| VARCHAR(MAX) | 2 bytes per character stored | Maximum 2GB. Replacement for TEXT datatype |
| NVARCHAR(n) | 2 bytes per character stored (n), up to 4000 | Stores unicode characters |
| NVARCHAR(MAX) | 2 byte per character stored | Maximum 2GB. Replacement for NTEXT datatype |
| BIT | 1 bit | Null, 0 or 1 |
| BINARY | Fixed width binary data | Up to 8000 bytes |
| VARBINARY | Variable width binary data | Up to 8000 bytes |
| VARBINARY(MAX) | Variable width binary data | Up to 2GB |
| GEOMETRY | ||
| GEOGRAPHY | ||
| XML | Stores XML documents | XML Schema collection can be defined to verify documents |
Field Attributes
Top Bottom- NULL
- Field may contain NULL value
- NOT NULL
- Field may not contain NULL value
- IDENTITY(x,y)
- Autoincrement field starting at x and incrementing by y
- SPARSE
- Use as little space as possible for field storage. Used mainly when fields will frequently hold NULL values
Constraints
Top BottomConstraints apply structure and enforce consistency on data. A variety of constraints can be specified:
- PRIMARY KEY
- Defines column as primary key. Values must be unique and not NULL
- UNIQUE
- Column values must be unique. More than one unique constraint can be defined on a table, allows unique NULL values
- CHECK
- Conditional rule that column values must satisfy
- DEFAULT 'xxx'
- Specifies a default value for a field when none supplied during INSERT
- FOREIGN KEY
- Column values must be in range of values of field from another table
