When we design a database, we usually need to do an estimate on the size of the database based on the dimension and fact tables.
Keep the following information handy next time when you need to do so.
The lists are based on this article:
SQL Server 2012 Data Types Reference
What I like about this is that it lists the minimum, maximum, accuracy, length and storage size in the same table in an easy to read tabular format.
Table A: Character data types
| Data Type | Length | Storage Size | Max Characters | Unicode |
| char | Fixed | Always n bytes | 8,000 | No; each character requires 1 byte |
| varchar | Variable | Actual length of entry in bytes | 8,000 | No; each character requires 1 byte |
| nchar | Fixed | Twice n bytes | 4,000 | Yes; each character requires 2 bytes |
| nvarchar | Variable | Twice actual length of entry in bytes | 4,000 | Yes; each character requires 2 bytes |
Table B: Integer data types
| Data type | Minimum value | Maximum value | Storage size |
| tinyint | 0 | 255 | 1 byte |
| smallint | -32,768 | 32,767 | 2 bytes |
| int | -2,147,483,648 | 2,147,483,674 | 4 bytes |
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 | 8 bytes |
Table C: Precision storage requirements
| Total characters (precision) | Storage size |
| 1 – 9 | 5 bytes |
| 10 – 19 | 9 bytes |
| 20 – 28 | 13 bytes |
| 29 – 38 | 17 bytes |
Table D: Float and real data type restrictions
| Data type | n | Minimum Value | Maximum value | Precision | Storage size |
| float(n) | 1 – 24 | -1.79E + 308 | 1.79 + 308 | 7 digits | 4 bytes |
| 25 – 53 | -1.79E + 308 | 1.79E + 308 | 15 digits | 8 bytes | |
| real | n/a | -3.40E + 38 | 3.40E + 38 | 7 digits | 4 bytes |
Table E: Smalldatetime and datetime restrictions
| Data type | Minimum value | Maximum value | Accuracy | Storage size |
| smalldatetime | January 1, 1900 | June 6, 2079 | Up to a minute | 4 bytes (the first 2 bytes store the date; the second 2 bytes store the time) |
| datetime | January 1, 1753 | December 31, 9999 | One three-hundredth of a second | 8 bytes (the first 4 bytes store the date; the second 4 bytes store the time) |
| date | January 1, 1900 | December 31, 9999 | Only date | 4 bytes ? |
| time | 00:00:00.0000000 | 23:59:59.9999999 | Only time | 4 bytes ? |
| datetime2 | larger year and second range | 8 bytes ? | ||
| datetimeoffset |
Table F: Smallmoney and money restrictions
| Data type | Minimum value | Maximum value | Storage size |
| smallmoney | -214,748.3648 | 214,748,3647 | 4 bytes |
| money | -922,337,203,685,477.5808 | 922,337,203.685,477.5807 | 8 bytes |
Does SQL Server 2008 have any new data types?
SQL Server 2008 has several new data types:
- date stores only date values with a range of 0001-01-01 through 9999-12-31.
- time stores only time values with a range of 00:00:00.0000000 through 23:59:59.9999999.
- datetime2 has a larger year and second range.
- datetimeoffset lets you consider times in different zones.
- hierarchyid constructs relationships among data elements within a table, so you can represent a position in a hierarchy.
- spatial identifies geographical locations and shapes — landmarks, roads, and so on.
