SQL Server's bit data type

SQL Server's bit data type

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 7/1/2016
-- bit data type

-- odd implicit conversion
-- bit data type 
-- character data type

-- saw something odd today, and thought I would cover it

USE Northwind

-- consider this from the Products database

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
ORDER BY ProductID

-- discontinued is a bit data type
-- of course productID is an integer data type
-- which is generally considered a very small integer in its treatment
-- https://msdn.microsoft.com/en-us/library/ms187752.aspx 
-- bits are stored differently internally, but ostensibly behave like integers

-- this fails
UPDATE Products
SET    discontinued = NULL
WHERE  ProductID = 1
-- this is clearly not integer-like

-- so consider these WHERE clauses using the productID field
SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  productID = '1' -- implicit conversion to integer  
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  productID = 'a' -- implicit conversion fails  
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  productID = '12' -- implicit conversion succeeds  
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  productID = '1222222222' -- implicit conversion succeeds 
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  productID = '1.2' -- implicit conversion fails 
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  productID = '-1' -- implicit conversion works, no records 
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  productID = '' -- implicit conversion succeeds, no records 
ORDER BY ProductID

-- but what does it convert to?

SELECT CAST('' AS integer) -- it converts to integer value of zero

-- so what if we are using a bit field
-- and trying to use it like a boolean value
-- but its really treated as a very small integer (2 values)

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '1' -- implicit conversion to bit  
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '0' -- implicit conversion  
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = 'a' -- implicit conversion fails 
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '12' -- implicit conversion with truncation? 
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '122222222' -- implicit conversion with truncation
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '022222222' -- implicit conversion with truncation? not really?
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '0.2' -- implicit conversion fails
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '-1' -- implicit conversion fails - not like an integer
ORDER BY ProductID

SELECT ProductID,
       ProductName,
       discontinued
FROM   Products
WHERE  discontinued = '' -- implicit conversion works
ORDER BY ProductID

-- so what does '' convert to?

SELECT CAST('' as bit) -- a zero

-- so, integer and bit seems inconsistent with how they treat negatives
-- what about tinyint?

SELECT CAST('-1' AS tinyint) -- gives overflow
SELECT CAST('-1' AS bit) -- gives conversion failure
SELECT CAST(-1 AS tinyint) -- gives overflow
SELECT CAST(-1 AS bit) -- succeeds, gives one

-- ok, that's strange

-- so, it appears that bit is neither a boolean nor an "exact numeric" 
-- in the way it is treated by SQL Server

-- just need to know how it behaves

-- in my experience, it is always *used* like it's a boolean/logical
-- personally, I would prefer it to behave consistently as a boolean/logical

-- Database by Doug
-- Douglas Kline
-- 7/1/2016
-- bit data type