Transact SQL – Error – “Arithmetic overflow error converting numeric to data type numeric”

Background

Revising a SQL Code snippet for finding Bookmark Lookups and ran into an error stating:

Msg 8115, Level 16, State 1
Arithmetic overflow error converting numeric to data type numeric

Code

Original Code

Here is the original code …


set NUMERIC_ROUNDABORT ON;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as decimal(30, 0)
										--as float
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as decimal(30, 0)
												--as numeric(30, 1)
												--as float
										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc


 

Error Message


Msg 8115, Level 16, State 1, Line 13
Arithmetic overflow error converting numeric to data type numeric.

Remediation

To remediate please try one of the options stated below:

  1. Set “Numeric Abort off”
    • set NUMERIC_ROUNDABORT OFF
  2. Cast as float

Set Numeric Abort Off

Code


--set NUMERIC_ROUNDABORT ON;
set NUMERIC_ROUNDABORT OFF;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as decimal(30, 0)
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as decimal(30, 0)

										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc




 

Output

SetNumericRunAbortOff

 

Cast as float

Code



set NUMERIC_ROUNDABORT ON;
--set NUMERIC_ROUNDABORT OFF;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as float
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as float
										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc



 

Output

CastAsFloat

 

 

Summary

Of the two corrective choices, casting to float is the path that localizes the problem to the specific arithmetic that is failing and so I will suggest you choose it over the “set option” that works more at module level.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s