Aktualizuję mój IDENTITYskrypt kontroli przepełnienia do konta DECIMALi NUMERIC IDENTITYkolumn .

W ramach kontroli obliczam rozmiar zakresu typu danych dla każdej IDENTITYkolumny; Używam tego do obliczenia, jaki procent tego zakresu został wyczerpany. Dla DECIMALi NUMERIC wielkość tego zakresu jest2 * 10^p - 2 gdzie pjest precyzja.

I stworzył kilka tabel z testów DECIMALi NUMERIC IDENTITYkolumnach i próbował obliczyć ich zakresów w następujący sposób:

SELECT POWER(10.0, precision)
FROM sys.columns
WHERE 
       is_identity = 1
   AND type_is_decimal_or_numeric
;

To spowodowało następujący błąd:

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

Zawęziłem go do IDENTITYkolumn typu DECIMAL(38, 0)(tj. Z maksymalną precyzją), więc następnie spróbowałem POWER()obliczeń bezpośrednio na tej wartości.

Wszystkie następujące zapytania

SELECT POWER(10.0, 38.0);
SELECT CONVERT(FLOAT, (POWER(10.0, 38.0)));
SELECT CAST(POWER(10.0, 38.0) AS FLOAT);

również spowodował ten sam błąd.

• Dlaczego SQL Server próbują przekształcić moc POWER(), która jest od rodzaju FLOAT, aby NUMERIC(zwłaszcza gdy FLOATma wyższy priorytet )?
• Jak mogę dynamicznie obliczyć zakres a DECIMALlub NUMERICkolumny dla wszystkich możliwych dokładności (w tym p = 38oczywiście)?

Od POWER dokumentacji :

Składnia

POWER ( float_expression , y )

Argumenty

float_expression
Jest wyrażeniem typu float lub typu, na które można niejawnie przekonwertować float .

y
Jest siłą, do której można podnieść wyrażenie_przestawne . y może być wyrażeniem dokładnej liczbowej lub przybliżonej kategorii liczbowej typu danych, z wyjątkiem typu danych bitowych .

Rodzaje zwrotów

Zwraca ten sam typ, co przesłany w float_expression . Na przykład, jeśli liczba dziesiętna (2,0) zostanie przesłana jako wyrażenie_przestawne, zwracany wynik jest dziesiętny (2,0).

Pierwsze dane wejściowe są domyślnie przekazywane w floatrazie potrzeby.

Wewnętrzne obliczenia są wykonywane przy użyciu floatarytmetyki za pomocą standardowej funkcji C Runtime Library (CRT) pow.

Dane floatwyjściowe powsą następnie rzutowane z powrotem na typ operandu po lewej stronie (implikowane jest to, numeric(3,1)gdy używasz wartości dosłownej 10.0).

Użycie jawnego floatdziała dobrze w twoim przypadku:

SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);

Dokładny wynik dla 10 ³⁸ nie może być zapisany w SQL Server, decimal/numericponieważ wymagałby 39 cyfr dokładności (1, po której następuje 38 zer). Maksymalna precyzja wynosi 38.

Zamiast wtrącać się w odpowiedź Martina, dodam resztę moich ustaleń dotyczących POWER() tutaj.

Trzymaj się majtek.

Preambuła

Najpierw przedstawiam wam dokumentacjęPOWER() A, dokumentację MSDN dla :

Składnia

POWER ( float_expression , y )

Argumenty

float_expression Jest wyrażeniem typu float lub typu, który można niejawnie przekonwertować na float.

Rodzaje zwrotów

Tak samo jak float_expression.

Możesz wyciągnąć wniosek z przeczytania tego ostatniego wiersza, który POWER()jest typem zwracanym FLOAT, ale przeczytaj ponownie. float_expressionto „typu float lub typu, który można niejawnie przekonwertować na float”. Tak więc, pomimo nazwy, float_expressionmoże w rzeczywistości być a FLOAT, a DECIMALlub an INT. Ponieważ dane wyjściowe POWER()są takie same, jak dane wyjściowe float_expression, może również być jednym z tych typów.

Mamy więc funkcję skalarną z typami zwracanymi zależnymi od danych wejściowych. Czy to możliwe?

Spostrzeżenia

Przedstawiam wam wykaz B, test wykazujący, że POWER()przekazuje on dane wyjściowe do różnych typów danych w zależności od danych wejściowych .

SELECT 
    POWER(10, 3)             AS int
  , POWER(1000000000000, 3)  AS numeric0     -- one trillion
  , POWER(10.0, 3)           AS numeric1
  , POWER(10.12305, 3)       AS numeric5
  , POWER(1e1, 3)            AS float
INTO power_test;

EXECUTE sp_help power_test;

DROP TABLE power_test;

Odpowiednie wyniki to:

Column_name    Type      Length    Prec     Scale
-------------------------------------------------
int            int       4         10       0
numeric0       numeric   17        38       0
numeric1       numeric   17        38       1
numeric5       numeric   17        38       5
float          float     8         53       NULL

Wydaje się, że dzieje się tak, że POWER()rzuca float_expressionsię na najmniejszy typ, który do niego pasuje, bez uwzględnienia BIGINT.

Dlatego SELECT POWER(10.0, 38);kończy się niepowodzeniem z błędem przepełnienia, ponieważ 10.0zostaje rzucony, do NUMERIC(38, 1)którego nie jest wystarczająco duży, aby pomieścić wynik 10 ³⁸ . Wynika to z faktu, że 10 ³⁸ rozwija się do 39 cyfr przed przecinkiem, natomiastNUMERIC(38, 1) może przechowywać 37 cyfr przed przecinkiem plus jedną po nim. Dlatego maksymalna wartość, jaką NUMERIC(38, 1)można utrzymać, to 10 ³⁷ - 0,1.

Uzbrojony w to zrozumienie, mogę wymyślić kolejną awarię przepełnienia w następujący sposób.

SELECT POWER(1000000000, 3);    -- one billion

Miliard (w przeciwieństwie do jednego biliona z pierwszego przykładu, do którego trafiono NUMERIC(38, 0) ) jest wystarczająco mały, aby zmieścić się w INT. Jednak miliard podniesiony do trzeciej potęgi jest na to za dużyINT , dlatego wystąpił błąd przepełnienia.

Kilka innych funkcji wykazuje podobne zachowanie, przy czym ich typ wyjściowy zależy od ich danych wejściowych:

• Funkcje matematyczne : POWER(), CEILING(), FLOOR(), RADIANS(),DEGREES() , iABS()
• Funkcje systemowe i wyrażenia : NULLIF(), ISNULL(), COALESCE(), IIF(), CHOOSE(), iCASE wyrażenia
• Operatory arytmetyczne : obaSELECT 2 * @MAX_INT; i SELECT @MAX_SMALLINT + @MAX_SMALLINT;na przykład powodują przepełnienie arytmetyczne, gdy zmienne mają określony typ danych.

Wniosek

W tym konkretnym przypadku rozwiązaniem jest użycie SELECT POWER(1e1, precision).... Będzie to działać dla wszystkich możliwych precyzji, od kiedy 1e1zostanie obsadzony FLOAT, co może pomieścić absurdalnie duże liczby .

Ponieważ te funkcje są tak powszechne, ważne jest, aby zrozumieć, że wyniki mogą być zaokrąglone lub powodować błędy przepełnienia z powodu ich zachowania. Jeśli oczekujesz lub zależysz od określonego typu danych dla danych wyjściowych, jawnie oddaj odpowiednie dane wejściowe, jeśli to konieczne.

Więc dzieci, skoro już to wiesz, możesz iść naprzód i prosperować.