Home > SQL Challenge > SQL #55 – Data Types and Storage Reference

SQL #55 – Data Types and Storage Reference

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.
Categories: SQL Challenge Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: