What is the max/min size of a decimal data type?

Recently, I wrote a maintenance script to check every table in every database on our servers at work nightly and email a report of identity columns that are approaching the limits of their data type. The minimum and maximum values for most numerical data types are documented in Books online, but for decimal/numeric data types it is not documented and varies based on the values provided for the precision and scale.

Most people know that precision is the total number of digits and scale is the number of those digits that appear after the decimal point. And obviously no matter how many digits or decimal places there are, the highest value would be all 9s. For example, a decimal(5, 2) would be 5 total 9s (99999) with 2 decimal places (999.99). But how do you determine that programmatically?

Well, I suppose you could use the REPLICATE() function to repeat the number 9 as a text string and then STUFF() a decimal point into the string at the appropriate place, but that kind of a hacky way to do it. The BOL page refers to the size limits of the max precision with scale of 0 — decimal(38, 0) — as -10^38+1 to 10^38-1. We can easily get the precision part by raising 10 to the power of

and then subtract one (then just make it negative for the minimum). We can do pretty much the same for determining where the decimal point goes by raising 10 to the power of and then dividing the first number by the second number.

Min = -(Power(10, precision) – 1)/Power(10, scale)
Max = (Power(10, precision) – 1)/Power(10, scale)

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:


WITH indexcols
AS (SELECT object_id,
		column_id,
		Min(key_ordinal) AS KeyOrdinal
	FROM   sys.index_columns
	WHERE  key_ordinal > 0
	GROUP  BY object_id,
		column_id)
SELECT TableName = Object_schema_name(C.object_id) + N'.'
		+ Object_name(C.object_id),
	ColumnName = C.NAME,
	DataType = T.NAME,
	MaxSize = CASE C.user_type_id
			WHEN 48 THEN 255
			WHEN 52 THEN 32767
			WHEN 56 THEN 2147483647
			WHEN 106 THEN Replicate(9, C.PRECISION - C.scale) + '.' + Replicate(9, C.scale)
			WHEN 108 THEN Replicate(9, C.PRECISION - C.scale) + '.' + Replicate(9, C.scale)
			WHEN 127 THEN 9223372036854775807
	END,
	  MinSize = -(CASE C.user_type_id
			WHEN 48 THEN 0
			WHEN 52 THEN 32767
			WHEN 56 THEN 2147483647
			WHEN 106 THEN Replicate(9, C.PRECISION - C.scale) + '.' + Replicate(9, C.scale)
			WHEN 108 THEN Replicate(9, C.PRECISION - C.scale) + '.' + Replicate(9, C.scale)
			WHEN 127 THEN 9223372036854775807
		END ),
	  CurrentIdentity = Ident_current(Object_schema_name(C.object_id) + N'.' + Object_name(C.object_id)),
	  IdentityIncrement = Ident_incr(Object_schema_name(C.object_id) + N'.' + Object_name(C.object_id)),
	  IndexPosition = CASE
			WHEN IC.keyordinal = 1 THEN 'Primary'
			WHEN IC.keyordinal > 1 THEN 'Non-primary'
			WHEN IC.keyordinal IS NULL THEN 'Not indexed'
		END
FROM   sys.columns AS C
INNER JOIN sys.types T
	ON T.user_type_id = C.user_type_id
LEFT JOIN indexcols IC
	ON IC.object_id = C.object_id
	AND IC.column_id = C.column_id
WHERE  C.is_identity = 1
AND Objectpropertyex(C.object_id, 'IsUserTable') = 1;