"Acceptable values that can be returned or stored"
Key points:
- Regulates input to ensure it’s acceptable
- The data type is included for each returned column in the Query Tool
- Helps to optimise the execution plan by assigning resources appropriately
- e.g. Limiting to a smallint over an int saves 2 bytes per row
- Over a 1 billion row table, that would save ~1GB in storage and memory to query
| Common Types | Input Accepted | Bytes Reserved |
|---|---|---|
| ... | ... | ... |
| Text | ||
| VARCHAR(x) | Text, number or other ascii character up to the number in brackets. e.g. (varchar(10) would accept up to 10 characters) |
chars+1 |
| NVARCHAR(x) | As above but also accepts foreign ascii characters (e.g. ä, é, ø, ü) | 2*chars+1 |
| ... | ... | ... |
| Integers | (Whole Numbers) | |
| BIT | Either 0 or 1 (used for yes/no or true/false flags) | 1 |
| SMALLINT | Any whole number between -32,767 and 32,767 | 2 |
| INT | Any whole number between -2,147,483,647 and 2,147,483,647 | 4 |
| BIGINT | -9,223,372,036,854,775,807 to 9,223,372,036,854,775,807 | 8 |
| ... | ... | ... |
| Numeric | (also known as Decimal) | |
| NUMERIC(p, s) | Any decimal up to the number of digits (precision) and decimal places (scale) e.g. (numeric(5,2) would accept a number up to 999.99) |
5-17 |
| ... | ... | ... |
| Dates & Times | ||
| DATE | A date based on the current locale in YYYY-MM-DD format | 3 |
| TIMESTAMP | A date based on the current locale in YYYY-MM-DD HH:MM:SS.MS format | 5-8 |
