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

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.

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

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 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

Use the decimal data type to set exact precision:

decimal(5,3)

友情链接