Compreendendo a precisão e a escala no contexto das operações aritméticas

Vamos detalhar isso e dar uma olhada nos detalhes do operador aritmético de divisão . Isto é o que o MSDN tem a dizer sobre os tipos de resultado do operador de divisão :

Tipos de resultados

Retorna o tipo de dados do argumento com maior precedência. Para obter mais informações, consulte Precedência de tipo de dados (Transact-SQL) .

Se um dividendo inteiro for dividido por um divisor inteiro, o resultado será um inteiro com qualquer parte fracionária do resultado truncada.

Sabemos que @big_numberé um DECIMAL. Em que tipo de dados o SQL Server é convertido 1? Ele o lança em um arquivo INT. Podemos confirmar isso com a ajuda de SQL_VARIANT_PROPERTY():

SELECT
      SQL_VARIANT_PROPERTY(1, 'BaseType')   AS [BaseType]  -- int
    , SQL_VARIANT_PROPERTY(1, 'Precision')  AS [Precision] -- 10
    , SQL_VARIANT_PROPERTY(1, 'Scale')      AS [Scale]     -- 0
;

Para começar, também podemos substituir o 1no bloco de código original por um valor digitado explicitamente como DECLARE @one INT = 1;e confirmar que obtemos os mesmos resultados.

Então nós temos um DECIMALe um INT. Como DECIMALtem uma precedência de tipo de dados maior que INT, sabemos que a saída de nossa divisão será convertida em DECIMAL.

Então, onde está o problema?

O problema é com a escala do DECIMALna saída. Aqui está uma tabela de regras sobre como o SQL Server determina a precisão e a escala dos resultados obtidos em operações aritméticas:

Operation                              Result precision                       Result scale *
-------------------------------------------------------------------------------------------------
e1 + e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 - e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 * e2                                p1 + p2 + 1                            s1 + s2
e1 / e2                                p1 - s1 + s2 + max(6, s1 + p2 + 1)     max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2   max(s1, s2) + max(p1-s1, p2-s2)        max(s1, s2)
e1 % e2                                min(p1-s1, p2 -s2) + max( s1,s2 )      max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result 
  precision is greater than 38, the corresponding scale is reduced to prevent the 
  integral part of a result from being truncated.

E aqui está o que temos para as variáveis ​​nesta tabela:

e1: @big_number, a DECIMAL(38, 0)
-> p1: 38
-> s1: 0

e2: 1, an INT
-> p2: 10
-> s2: 0

e1 / e2
-> Result precision: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 + max(6, 11) = 49
-> Result scale:                    max(6, s1 + p2 + 1) =      max(6, 11) = 11

De acordo com o comentário do asterisco na tabela acima, a precisão máxima que um DECIMALpode ter é 38 . Portanto, nossa precisão de resultado é reduzida de 49 para 38 e "a escala correspondente é reduzida para evitar que a parte integral de um resultado seja truncada". Não está claro neste comentário como a escala é reduzida, mas sabemos disso:

De acordo com a fórmula na tabela, a escala mínima possível que você pode ter depois de dividir dois DECIMALs é 6.

Assim, chegamos aos seguintes resultados:

e1 / e2
-> Result precision: 49 -> reduced to 38
-> Result scale:     11 -> reduced to 6  

Note that 6 is the minimum possible scale it can be reduced to. 
It may be between 6 and 11 inclusive.

Como isso explica o estouro aritmético

Agora a resposta é óbvia:

A saída da nossa divisão é convertida para DECIMAL(38, 6), e DECIMAL(38, 6)não pode conter 10 ³⁷ .

Com isso, podemos construir outra divisão que seja bem-sucedida, garantindo que o resultado caiba em DECIMAL(38, 6):

DECLARE @big_number    DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million   INT           = '1' + REPLICATE(0, 6);

PRINT @big_number / @one_million;

O resultado é:

10000000000000000000000000000000.000000

Observe os 6 zeros após a vírgula. Podemos confirmar que o tipo de dados do resultado é DECIMAL(38, 6)usando SQL_VARIANT_PROPERTY()como acima:

DECLARE @big_number   DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million  INT           = '1' + REPLICATE(0, 6);

SELECT
      SQL_VARIANT_PROPERTY(@big_number / @one_million, 'BaseType')  AS [BaseType]  -- decimal
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Precision') AS [Precision] -- 38
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Scale')     AS [Scale]     -- 6
;

Uma Solução Perigosa

Então, como contornar essa limitação?

Bem, isso certamente depende de para que você está fazendo esses cálculos. Uma solução para a qual você pode pular imediatamente é converter seus números FLOATpara os cálculos e, em seguida, convertê-los novamente DECIMALquando terminar.

Isso pode funcionar em algumas circunstâncias, mas você deve ter cuidado para entender quais são essas circunstâncias. Como todos sabemos, converter números de e para FLOATé perigoso e pode fornecer resultados inesperados ou incorretos.

No nosso caso, converter 10 ³⁷ de e para FLOATobtém um resultado que está simplesmente errado :

DECLARE @big_number     DECIMAL(38,0)  = '1' + REPLICATE(0, 37);
DECLARE @big_number_f   FLOAT          = CAST(@big_number AS FLOAT);

SELECT
      @big_number                           AS big_number      -- 10^37
    , @big_number_f                         AS big_number_f    -- 10^37
    , CAST(@big_number_f AS DECIMAL(38, 0)) AS big_number_f_d  -- 9999999999999999.5 * 10^21
;

E aí está. Dividam com cuidado, meus filhos.