Blog Post

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 TypeLengthStorage SizeMax CharactersUnicode
charFixedAlways n bytes8,000No; each character requires 1 byte
varcharVariableActual length of entry in bytes8,000No; each character requires 1 byte
ncharFixedTwice n bytes4,000Yes; each character requires 2 bytes
nvarcharVariableTwice actual length of entry in bytes4,000Yes; each character requires 2 bytes

Table B: Integer data types

Data typeMinimum valueMaximum valueStorage size
tinyint02551 byte
smallint-32,76832,7672 bytes
int-2,147,483,6482,147,483,6744 bytes
bigint-9,223,372,036,854,775,8089,223,372,036,854,775,8078 bytes

Table C: Precision storage requirements

Total characters (precision)Storage size
1 – 95 bytes
10 – 199 bytes
20 – 2813 bytes
29 – 3817 bytes

Table D: Float and real data type restrictions

Data typenMinimum ValueMaximum valuePrecisionStorage size
float(n)1 – 24-1.79E + 3081.79 + 3087 digits4 bytes
25 – 53-1.79E + 3081.79E + 30815 digits8 bytes
realn/a-3.40E + 383.40E + 387 digits4 bytes

Table E: Smalldatetime and datetime restrictions

Data typeMinimum valueMaximum valueAccuracyStorage size
smalldatetimeJanuary 1, 1900June 6, 2079Up to a minute4 bytes (the first 2 bytes store the date; the second 2 bytes store the time)
datetimeJanuary 1, 1753December 31, 9999One three-hundredth of a second8 bytes (the first 4 bytes store the date; the second 4 bytes store the time)
dateJanuary 1, 1900December 31, 9999Only date4 bytes ?
time00:00:00.000000023:59:59.9999999Only time4 bytes ?
datetime2larger year and second range8 bytes ?
datetimeoffset

Table F: Smallmoney and money restrictions

Data typeMinimum valueMaximum valueStorage size
smallmoney-214,748.3648214,748,36474 bytes
money-922,337,203,685,477.5808922,337,203.685,477.58078 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.

Rate

Share

Share

Rate