Understanding SQL Server Numeric Data Types

numbers2 (1)After completing my first stint as a Trainer at LGIT training the Course 40364 : Database Administration Fundamentals, some few topics stood out from my class which I would like to weigh on via an upcoming series of blog posts which I hope will assist to give clarity pertaining to certain SQL Server concepts. In this particular blog post I would like to deal with the concept of Numeric Data Types in SQL Server. SQL Server provides a number of numeric data types, hence there is often confusion at decision time as to what data type should I use for the data I would like to store in a particular column.

If you refer back to your primary school Mathematics, you would remember that there are essentially two types of numbers, from which all other specialized and complex number types in daily use, are derived from, these are namely Whole Numbers (these are typically your 1, 2, 3, 4, 5. . .) and Decimal Numbers (these are your numbers with decimal points e.g 0.35, 0.948814, 12.49, 491.94). Hence the numeric data types available in SQL Server belong to one of these two groups. Let me begin by dealing with whole numbers.

Whole Numbers

SQL Server gives you the ability to store whole numbers. The data types available are as  follows:

Data Type Description
 bit  Integer with either 1 or 0 value
 tinyint  Integer data from 0 to 255
 smallint  Integer data from – 32,768 to 32,767
 int  Integer data from -2, 147, 483, 648 to 2, 147, 483, 647
 bigint  Integer data from -9, 223,372, 036, 854, 775, 808 to -9, 223,372, 036, 854, 775, 807

So in most cases you will do just fine by using int as choice data type in order to store integer or whole number data types.

Decimal Numbers / Floating Point Numbers 

SQL Server affords you a wide selection of data types that you can use in order to store numbers with floating points. These data types are namely:

Data Type Description
 numeric  Valid values range from -10^38+1 tp -10^38 -1
 decimal  Valid values range from -10^38+1 tp -10^38 -1
 smallmoney  Monetary or currency values from -214,748.3648 to 214,748.3647
 money  Monetary or currency values from -922, 337,203,685,477.508 to 922, 337,203,685,477.5807
 float  Valid values range: – 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

 

So from the table above you can see see that there is a suitable decimal that can be used for the purpose intended. If you want to store monetary values, the money data type is the best data type to use. Using the smallmoney data type would prevent you from storing large values like R250,000.

In my next post I will deal with string or text-based data types.

Have you had issues in the past in deciding what would  be the best data type to use in your database design? Share your story!

2 thoughts on “Understanding SQL Server Numeric Data Types

  1. Pingback: SQL Server : Understanding String Data Types | lgit smartblog

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