MS SQL – Preferred data types

By | February 4, 2015
0 Comment

Decimal Numbers…

DECIMAL(18,0) will allow 0 digits after the decimal point.

Use something like DECIMAL(18,4) instead that should do just fine!

That gives you a total of 18 digits, 4 of which after the decimal point (and 14 before the decimal point).

Question

Sum’ing data of type REAL resulted in a total with about 10 decical places, even though all the rows had at most one decimal place. Why?

Answer

REAL’s and FLOAT’s store numbers in a binary format.  Because of the way they are stored, most decimal values (to the right of the decimal point) cannot be stored exactly.  They can only be stored exactly if the decimal part is a multiple of 1/(some power of 2).  So, you can store 10.5 (1/2) or 33.25 (1/2 squared) or 7.625 (3 times 1/2 cubed).  But you can’t store exactly most decimal values like 1.7 or 2.31, etc.  Instead what gets stored is the nearest binary number to the value you enter that can be saved in the number of bits available in a REAL or FLOAT).  This is analogous to the problem you have storing 1/3 in a decimal fraction.  You get something like 0.333333333.  So when you store something like 13.2 in a REAL, the number actually stored is very, very close to, but not equal to 13.2.  So the number actually stored might be something like 13.2000017.  Now of you do a select of this number, SQL actually returns the 13.2000017, but many front ends are nice enough to round that to 13.2, but that’s not what is really stored.  But if you do enough arithmetic manipulation of many values, you make get a value far enough away, from a simple short it no longer rounds the number.

The moral of the story is, if you care about decimal precision, do not use float or real data types.  Use decimal or numeric.  There are very few business solutions where real or float are the best choice.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b9eb2810-c988-4694-b14c-bbfcb3474204/sum-returning-too-many-decimal-places

Strings

  • nchar and nvarchar can store Unicode characters.
  • char and varchar cannot store Unicode characters.
  • char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don’t use up all that space.
  • varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar.