Most efficient way to store number 11.111 in SQL Server

Question

What datatype is the most efficient to store a number such as 11.111.

The numbers will have up 2 digits before the point and up to three after.

Currently the column is a 'bigint', I am guessing that this will need to be changed to a decimal or a float.

Any help much appreciated.

thanks

Answer 1

I would think that you have two options: Multiply by 1000 and store as an int (and divide on the way out), or just store as a double. With memory and drive space being so cheap, it's probably not worth the hassle of optimizing any further.

Answer 2

If you need exact representation, the smallmoney datatype uses 4 bytes.

This is equivalent to using an int (4 bytes), and performing multiplication/division to take care of the fractional digits.

Answer 3

Use the decimal data type to set exact precision:

decimal(5,3)

Answer 4

You should use the DECIMAL data type. It stores exact floating point numbers

DECLARE @d decimal(5, 3)
SET @d=12.671
SELECT @d, DATALENGTH(@d)

As @Mitch pointed out, this would be 5 bytes in size. You pay an extra byte for easier calculations with the value.

Answer 5

Bigint is an integer datatype, so yeah, that won't work without some preprocessing footwork. Do you need to store the values precisely, or only approximately? The binary floating point types (float, real) are efficient and compact, but don't represent many decimal values exactly, and so comparisons won't always give you what you expect, and errors accumulate. decimal is a good bet. A decimal of precision 5 and scale 3 (meeting your requirements) consumes five bytes.

See documentation on the decimal datatype here, and datatypes in general here.