SQL Server decimal data type performance

Question

I have a few tables that store quite large numbers, so I've chosen decimal(17, 2) as the data type for all the columns.

Example of schema:
Table1 (value1, value2, value3, value4, value5)
Table2 (value1, value2, value3, value4, value5)

For argument sake, if I have 100,000 rows in each table, and each value is 100.00, how will the query performance be compared to if the data type was decimal(5, 2)? Will it be negligible? Will the main difference be the storage space taken up?

there are so many dependancies -- maybe you should try building some test tables with both data types and executing the same queries on both tables, and see what the results are. — BWS
– BWS, Commented Dec 4, 2013 at 14:43
A decimal(17,2) will take up 9 bytes. a decimal(5,2) will take up 5. So the storage difference with 2 tables, 4 columns, and 50,000 rows will be 1.6MB. — D Stanley
– D Stanley, Commented Dec 4, 2013 at 14:56
For 100k rows.. does the performance gain really outweigh the accuracy lost with float? I choose accuracy over small perf gain any day. Stay away from floats as much as you can. — sam yi
– sam yi, Commented Dec 4, 2013 at 15:00

Randy Minder · Accepted Answer · 2013-12-04 14:45:26Z

1

If you are using a Decimal type strictly because you are working with 'quite large numbers' then you are using the Decimal type for the wrong reason. The decimal type is used when rounding accuracy is the major concern, not because the numbers are large. Actually, for really large numbers, a float would be a better choice, since it can hold larger numbers. As far as performance is concerned, your best bet is to simply test it for yourself. Fill a table with 100,000 rows of decimals, floats reals etc. and test query performance.

answered Dec 4, 2013 at 14:45

Randy Minder

48.8k56 gold badges222 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

notAnonymousAnymore Over a year ago

Ok, thanks for that. It's financial data so I'm using decimal for good reason.

f470071 Over a year ago

You are using DECIMAL(17,2) for financial data? Are you calculating gross national debt?

S'pht'Kr Over a year ago

@f470071 ... For argument's sake... normal inflation is 3% ... abnormal inflation is greater. Think about the developers in the 70's that gave us the Y2K nonsense and how many digits they thought they would need to represent financial data...hmm? Oh, and non-USD currencies? Hmm? Lots of reasons to think harder about a question like that.

AlexK · Accepted Answer · 2013-12-04 15:18:06Z

0

MSDN decimal and numeric

Precision    Storage bytes
--------------------------
1 - 9        5
10-19        9

So, It decimal(5, 2) takes 45% less than decimal(17, 2) in the storage.

The difference in execution time depends on the tasks and configuration of your server. If the bottleneck is the load on the I / O system and then increase likely will be ~40%.

answered Dec 4, 2013 at 15:18

AlexK

10.1k2 gold badges25 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Server decimal data type performance

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related