List of Datatypes
Published: Sep 09, 2013
A datatype is exactly how it sounds... it defines the type of data. We use these all the time in our normal lives but may not even realise it.

14, for example, is a number. We know that "bored" is a word made up of 5 characters. Those are effectively datatypes in action, just that we call them slightly different names in SQL Server.

Using the above, 14 is a number, but in SQL it would be an integer, therefore of the type "int". This can be broken down further as it is also a smallint and a tinyint, but they will be clarified below.

In the same way, "bored" is a word of 5 characters, therefore we would refer to it as char(5). But a word can be any length, therefore we could use a variable length character, a varchar.

These characterisations allow us to work with data and manipulate it accordingly, for example you can put two varchars together "cow" & "girl" to get "cowgirl", yet you cannot add or multiply them. Likewise you can add or subtract two ints, but you would not normally say "1 & 1 = 11".

I have provided a list of the most common types below, but if you wish to get a full list, then please refer to Microsoft Books Online.

List of Common Datatypes


bit This can be either 0 or 1 1 Byte
tinyint Whole number between 0 and 255 1 Byte
smallint Whole number between -32,768 and 32,767 2 Bytes
int Whole number between -2,147,483,648 and 2,147,483,647 4 Bytes
bigint Whole number between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 Bytes
smallmoney -214,748.3648 to 214,748.3647 4 Bytes
money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 Bytes
numeric numeric(p, s), p is the number of decimal digits, s is the number of decimal places Variable
float -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 Variable
date 0001-01-01 to 9999-12-31 3 Bytes
time 00:00:00.0000000 to 23:59:59.9999999 5 Bytes
smalldatetime 1900-01-01 00:00 to 2079-06-06 23:59 4 Bytes
datetime 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997 8 Bytes
char char(n), (non-unicode) where n is fixed length from 1 to 8000 n Bytes
varchar varchar(n), (non-unicode) where n is a fixed length from 1 to 8000. (max) = 2GB size n Bytes + 2
nchar nchar(n), (unicode) where n is a fixed length from 1 to 4000. 2*n Bytes
nvarchar nvarchar(n), (unicode) where n is a fixed length from 1 to 4000. (max) = 2GB size 2*n Bytes + 2
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron