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.

No, SQL Server *doesn't* use NULL as a default unless the column is nullable. If you insert a NULL in a nullable column, you'll get a null, not a default value anyway. If you *don't* explicitly insert a NULL, you'll ALWAYS get the default value. — Panagiotis Kanavos, May 16 '17 at 08:54
Do you want to *allow* explicit `NULL` values? If not, it would seem trivial to mark the column as `not null`. — Damien_The_Unbeliever, May 16 '17 at 08:54
Post your table definition *AND* the code you use to insert data. If you get a NULL, either you *didn't* specify a default value or you explicitly stored a NULL value — Panagiotis Kanavos, May 16 '17 at 08:55
@Damien_The_Unbeliever if you do 'NOT NULL' then the table expects a value which then must be inserted. That's out of the point. I want a default value to be '0' when a record gets inserted. — user763539, May 16 '17 at 09:27
@ Panagiotis Kanavos Yes it does. All the values have the NULL value when adding a new row. — user763539, May 16 '17 at 09:28
@user763539 - use Tanner's answer but change the column definition to allow nulls and it still outputs `0` and `1`. So, whatever you're doing, it's not *the most obvious thing to do*, so you need to create a small code sample that *demonstrates* a table with a default and an insert that causes `null` to be inserted rather than the default. — Damien_The_Unbeliever, May 16 '17 at 09:30

score 40 · Answer 1 · answered May 16 '17 at 09:21

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

score 22 · Answer 2 · edited Mar 21 '22 at 17:35

22

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

edited Mar 21 '22 at 17:35

SHR

7,940
9
38
57

answered May 16 '17 at 08:51

Alexey Varentsov

376
1
2

The OP mentions that a default *was* specified but "didn't work". The OP probably has a different problem in the table definition or INSERT statement – Panagiotis Kanavos May 16 '17 at 08:57
@Alexey Varentsov I did that in managment studio but it still reverts to null on new records. – user763539 May 16 '17 at 09:15

Pawel Czapski · Answer 3 · 2017-05-16T10:29:39.297

6

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

edited May 16 '17 at 10:29

answered May 16 '17 at 09:30

Pawel Czapski

1,856
2
16
26

Unfortunately even this : ALTER TABLE mytable ADD CONSTRAINT No_Nulls DEFAULT 0 FOR myfield; will not work. SQL Server insists on assigning NULL to all boolean fields (bit). It wont let me assign my own value. When I go to a new row, all the fields still show NULL as default value. – user763539 May 16 '17 at 10:19
1

@user763539, not sure what you are expecting. Setting default vaule will not change existing rows value, it will assign default value only to new rows. For existing values you need to do update. See my edited answer. – Pawel Czapski May 16 '17 at 10:26
@user763539 "When I go to a new row, all the fields still show NULL as default value.". Are you just talking about the inline table editor in SSMS? If so, then simply ignore the field in question. Once you actually insert the row it'll get the default value. – ADyson May 16 '17 at 10:38
@ ADyson The problem is that the sql server sets all values to NULL in the beginning.Only after you add the record it alters them to desired value and then posts. My program picks up the existing value before post (which reads null of course). Then I encounter conversion error. How can you move to the new row in managment studio so you can already see the default value displayed ? – user763539 May 16 '17 at 11:23
you can't AFAIK. It's just a detail of the GUI, unrelated to the underlying data. What program are you writing that is interacting with the SSMS GUI in this way? It sounds like a very brittle way to do things. Surely it would be much easier to interact with the data itself in some other way, via a SQL trigger or somesuch, to find out the actual insert values? What is the purpose of your program? – ADyson May 16 '17 at 12:27
1

_My program picks up the existing value before post (which reads null of course)._ Then the problem is with your application logic. When your app "adds" a new row for the user to edit, it is your app that is "overriding" any default values because the row does not yet exist in the table. So when the user saves this new row, it simply inserts the row using all of the values in your form - some of which the user probably never touches. So the default values of the columns are never used. – SMor May 16 '17 at 13:34
And there is no easy workaround for this problem. You can retrieve the table meta-data and attempt to interpret and populate your form with the appropriate defaults - which might not be easy or appropriate for some values. Or your "save" logic can attempt to ignore any columns that the user did not touch during insertion. – SMor May 16 '17 at 13:39

score 6 · Answer 4 · edited Jun 04 '20 at 20:24

6

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

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

edited Jun 04 '20 at 20:24

Jeremy Caney

7,102
69
48
77

answered Jun 04 '20 at 06:56

Ming Xiao

71
1
2

1

That seems like the right solution, but I guess you mean 0 as the default not 1 (see question)? – kayakpim Jun 04 '20 at 12:35
Using 'WITH VALUES' is the actual solution here, but surprisingly this solution is rated negative :D – theLearner Aug 25 '21 at 07:44

score 0 · Answer 5 · edited Jun 21 '22 at 08:42

0

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

edited Jun 21 '22 at 08:42

RF1991

2,037
4
8
17

answered Nov 18 '18 at 13:08

soheila sadeghian

91
1
4

bit type : default to '0' instead of NULL

5 Answers5