Max And Min Decimal Values

Robert Davis gives us the formula for the max and min decimal values given a scale and precision:

Unfortunately, the mathematical approach has flaws. First of all, Power(10, 38) exceeds the range of any numerical data type in SQL Server. There is no way to store or work with this value in calculations. Secondly, once you try to raise 10 to a power greater than 30, you start seeing floating point calculations (the values are approximate). For example, select Power(Cast(10 as decimal(38, 0)), 31) — casting as decimal(38, 0) because it exceeds int or bigint — yields 9999999999999999600000000000000. That’s clearly an approximated value and is not going to work for calculations where we’re expecting a precise value. So, that leaves the hacky version I didn’t want to do because I just cannot count on the mathematical approach working. Here is the base query using the hacky calculation

Read on for the hacky version.

Related Posts

Thoughts On UTF-8 Encoding In SQL Server 2019

Solomon Rutzky digs into UTF-8 support in SQL Server 2019 and has found a few bugs: Let’s start with what we are told about this new feature. According to the documentation, the new UTF-8 Collations: can be used … as a database-level default Collation as a column-level Collation by appending “_UTF8” to the end of […]

Read More

Parsing Numeric Values From Multiple Cultures

Bert Wagner shows us a good way of converting strings to numbers when multiple cultures are in play: Why are the salaries stored as nvarchar and formatted with commas, spaces, and periods? Great question!  Someone wanted to make sure these amounts would look good in the UI so storing the formatted values in the database […]

Read More


June 2016
« May Jul »