SQL Server - BIT data type Issue

Question

I have a table in my SQL Server 2012 as follows,

CREATE TABLE TEST (ID INT,NAME VARCHAR(100),STATUS BIT);

I inserted the data like this, by mistake,

INSERT INTO TEST SELECT 1,'A',2;

When I queried the result, I found the column "STATUS" storing "1". Whatever value I insert instead of "0", it stores "1". I felt if I define a column as BIT then it should not allow inserting any value apart from 0 and 1.

Is there any trace flag that restricts such insertion? I'm using SQL Server 2012 SP3 now.

Answer 1

Using the Bit Data type.

"The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

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."

MSDN