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.
SQL Server gives you the ability to store whole numbers. The data types available are as follows:
|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:
|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!