bit type : default to '0' instead of NULL

Question

By default sql server assigns boolean fields a NULL value. How can I tell it to use '0' as default? I tried setting the default value to ((0)) but it still persists on the NULL.

Answer 1

Here's a sample with a non nullable bit column with the default specified, just run the below in Management Studio:

CREATE TABLE #temp     (       id INT ,       myBit BIT NOT NULL DEFAULT 0  -- not null with default of false     );  INSERT  INTO #temp         ( id )  -- only insert to id col, the default will set itself VALUES  ( 123 );  INSERT INTO #temp         ( id, myBit ) VALUES  ( 456, 1 )  -- this insert adds a true value to override the default  SELECT  * FROM    #temp;  DROP TABLE #temp; 

Produces:

id  myBit 123 0 456 1 

Answer 2

ALTER TABLE [table name] ADD [column name] BIT NOT NULL DEFAULT 0;