[SQL] 轉型為 bit

無意中在自家系統內看見下述 TSQL 語法，整數 2 轉型 bit 竟然不會出現轉型錯誤

SELECT CAST(2 as bit)

在官方文件 - bit 內有這段說明

The bit data type can be used to store Boolean values. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1, and FALSE is converted to 0.

Converting to bit promotes any nonzero value to 1.

The bit data type supports the COUNT function. However, other standard aggregate functions, like SUM, AVG, MIN, and MAX, don't directly support the bit data type.

基本上只要不是 0 就是轉型為 1 就是，測試範例如下

SELECT 
    CAST(source as bit) AS 轉型bit
FROM
    (
        SELECT 0 AS source UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL 
        SELECT 100 UNION ALL
        SELECT -1 
    ) AS T