SQL Server BIGINT 轉 INT 溢位錯誤解決方案：符號位處理自訂函數

位元運算儲存多狀態時，常使用 0x80000000 (最高位) 作為旗標，導致 BIGINT 轉 INT 時發生算術溢位錯誤 (Msg 8115)。本文提供自訂函數 BigintToInt 正確處理符號位轉換，保留原始位元模式。

問題重現

-- 狀態碼使用了 0x80000000 (最高位 = 1)
DECLARE @val BIGINT = 0x80000000;  -- 2147483648
SELECT CAST(@val AS INT);  -- 錯誤: 算術溢位，INT 最大值為 2147483647

根因分析

• INT 為有號 32 位元整數，範圍 -2,147,483,648 ~ 2,147,483,647。
• BIGINT 數值 > 2,147,483,647 直接 CAST 會溢位。
• 但位元模式 0x80000000 在 INT 中代表 -2,147,483,648 (最小值)，位元模式相同，僅解讀方式不同。

解決方案：BigintToInt 函數

CREATE FUNCTION [dbo].[BigintToInt] (@Value BIGINT)
RETURNS INT
AS
BEGIN
    -- 檢測 INT 符號位 (第 31 位，0-based)
    IF @Value & 0x80000000 <> 0
        -- 有符號位：擴展符號位至 64 位再截取低 32 位
        RETURN @Value & 0xFFFFFFFF | 0xFFFFFFFF00000000;
    -- 無符號位：直接取低 32 位
    RETURN @Value & 0xFFFFFFFF;
END
GO

驗證測試

-- 測試案例
SELECT dbo.BigintToInt(0x7FFFFFFF);  -- 2147483647 (INT MAX)
SELECT dbo.BigintToInt(0x80000000);  -- -2147483648 (INT MIN)
SELECT dbo.BigintToInt(0xFFFFFFFF);  -- -1
SELECT dbo.BigintToInt(0x12345678);  -- 305419896 (正常正數)
SELECT dbo.BigintToInt(0x00000000);  -- 0

邏輯解析

應用場景

• 位元旗標儲存多狀態 (正常/警告/異常) 的 INT 欄位。
• 大量資料聚合時需 BIGINT 運算避免溢位，結果需回存 INT。
• 與舊版系統/其他語言 (C/C++/Java) 交換 32 位有號整數。

替代方案 (SQL Server 2012+)

-- 使用 TRY_CAST 避免錯誤，但會回傳 NULL 而非正確負數
SELECT TRY_CAST(0x80000000 AS INT);  -- NULL (轉換失敗)

-- 正確做法仍需自訂函數或應用層處理

此函數精確處理二補數符號位轉換，確保位元運算邏輯在 BIGINT 運算與 INT 儲存間無損傳遞。