Numeric Datatype Decisions

Total article views: 12930 | Views in the last 30 days: 72

 Rate this |   Join the discussion |  Briefcase

Last week we discussed character datatypes and their performance effects on your database. This week,we will dive into numeric datatypes. We will discuss how SQL Server numeric datatypes are stored and the pros and cons of each.

The first category we will discuss are exact numeric datatypes. This group gets its name from its ability to set the precision and scale. Precision is the total amount of digits to the right and left of the decimal where scale is how many places to the right of the decimal SQL Server will store. SQL Server uses rounding to estimate any decimal spots past the scale. There are two types of exact datatypes:

• Decimal - can store values between -10³⁸ and 10³⁸. Uses between 2 and 17 bytes depending on the precision.
• Numeric - can store values between -10³⁸ and 10³⁸. Uses between 2 and 17 bytes depending on the precision.

You may notice that the decimal and numeric datatype are exactly the same. Numeric is kept in SQL Server mostly for backward capatability. You may want to consider stopping the use of numeric datatypes so you are prepared when the datatype is retired.

The next category is absolute or fixed numeric datatypes. These datatypes can not be adjusted using a precision. There are three types of fixed datatypes:

• Int - Can stored between -2,147,483,648 to 2,147,483,647. Uses 4 bytes of storage.
• SmallInt - Can store between -32,768 to 32,767. Uses 2 bytes of storage.
• TinyInt - Can store values between 0 and 255. Uses 1 bytes of storage.

Since Intel processors use 4 byte chunks of data to work with at a time, the int datatype will run most optimal.

You also have more specialized datatypes like money and small money. Money can store between -$922,337,203,685,477.5808 to $922,337,203,685,477.5807 and uses 8 bytes of storage. Small money on the otherhand stores between -$214,748.3648 to $214,748.3647 and uses 4 bytes of storage. Both of these datatypes stores a the value 20.5 as $20.5. As a general rule, use exact datatypes to represent these values and perform the data manipulation at the application level.

Total article views: 12930 | Views in the last 30 days: 72