CODEWITHSUNDEEP
sql-server
user763539
user763539

Reputation: 3699

bit type : default to '0' instead of NULL

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.

Upvotes: 51

Views: 161539

Answers (5)

soheila sadeghian
soheila sadeghian

Reputation: 91

ALTER TABLE (table name) ADD (column name) BIT NOT NULL DEFAULT 0;

Upvotes: 0

Alexey Varentsov
Alexey Varentsov

Reputation: 386

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

Upvotes: 23

Ming Xiao
Ming Xiao

Reputation: 101

Please add suffix WITH VALUES, like the following. It works well:

ALTER TABLE (your table) 
ADD (your column) [bit] NULL DEFAULT 1 WITH VALUES;

Upvotes: 9

Pawel Czapski
Pawel Czapski

Reputation: 1864

I would recommend to specify default name, otherwise generated name is not telling you anything about created constraint, see sample below:

CREATE TABLE TBL_SAMPLE
(
    ID INT NOT NULL CONSTRAINT [PK_ID] PRIMARY KEY,
    BIT_COLUMN BIT NOT NULL CONSTRAINT [DF_BIT_COLUMN] DEFAULT (0)
)
GO


INSERT INTO TBL_SAMPLE (ID)
VALUES (1)
GO

SELECT * FROM TBL_SAMPLE
GO

DROP TABLE TBL_SAMPLE
GO

EDIT:

CREATE TABLE #TBL_SAMPLE 
(
    ID INT NOT NULL CONSTRAINT [PK_ID] PRIMARY KEY,
    BIT_COLUMN BIT NULL --CONSTRAINT [DF_BIT_COLUMN] DEFAULT (0) 
) 
GO


INSERT INTO #TBL_SAMPLE (ID) VALUES (1) 
GO

SELECT * FROM #TBL_SAMPLE 
GO

ALTER TABLE #TBL_SAMPLE ADD CONSTRAINT [DF_BIT_COLUMN] DEFAULT (0) FOR BIT_COLUMN 
GO


INSERT INTO #TBL_SAMPLE (ID) VALUES (2) 
INSERT INTO #TBL_SAMPLE (ID) VALUES (3) 
GO

SELECT * FROM #TBL_SAMPLE 
GO

UPDATE #TBL_SAMPLE 
SET BIT_COLUMN = 0 
WHERE BIT_COLUMN IS NULL

SELECT * FROM #TBL_SAMPLE 
GO

DROP TABLE #TBL_SAMPLE 
GO

Upvotes: 6

Tanner
Tanner

Reputation: 22733

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

Upvotes: 43

Related Questions