bit type : default to '0' instead of NULL

alter column bit default 0
sql bit true/false
bit sql
sql server bit flag
sql set bit value
sql false bit
bit 0 and 1
sql convert bit

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.

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

set default value to bit column, set null able=no ,now i want to set its default value to 1 by a SQL query ,please not down is active column data type is bit , how to set defa The BIT type that allows you to store bit values. Here is the syntax: BIT(n) The BIT(n) can store up to n-bit values. The n can range from 1 to 64. The default value of n is 1 if you skip it. Therefore the following statements are equivalent: column_name BIT(1); and. column_name BIT; To specify a bit value literal, you use b'val' or 0bval notation, which val is a binary value that contains only 0 and 1. The leading b can be written as B, for example: b01 B11 are the valid bit literals.

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

bit (Transact-SQL), 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  11.1.5 Bit-Value Type - BIT. The BITdata type is used to store bit values. A type of BIT(M)enables storage of M-bit values. Mcan range from 1 to 64. To specify bit values, b'value'notation can be used. valueis a binary value written using zeros and ones.

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

adding a new not null bit column with default 0 – SQLServerCentral, I need to add a new bit column to a table with around 6 million rows.I know the table will be locked during this operation. My question is: Is there  A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted. Bit values can be inserted with b'value' notation, where value is the bit value in 0's and 1's.

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

An Essential Guide To SQL Server BIT Data Type, This tutorial shows you how to use the SQL Server BIT data type to store bit data in the database. An integer data type that can take a value of 1, 0, or NULL. Remarks. The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer 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.

bit type column with default value : bit « Data Types « SQL Server / T , bit type column with default value : bit « Data Types « SQL Server / T-SQL Tutorial. Any nullable value type. An instance for which the HasValue property is false and the Value property is undefined. That default value is also known as the null value of a nullable value type. Use the default operator to produce the default value of a type, as the following example shows: C#. int a = default(int);

MySQL 5.7 Reference Manual :: 11.1.5 Bit-Value Type - BIT, The BIT data type is used to store bit values. A type of BIT( M ) enables storage of M -bit values. M can range from 1 to 64. To specify bit values, b' value ' notation  A variable of type bit can be either 0 or 1 which represents a single bit. A range from MSB to LSB should be provided to make it represent and store multiple bits A range from MSB to LSB should be provided to make it represent and store multiple bits

Default value for bit datatype, Global_field bit DEFAULT 0 NOT NULL , ). I get the message: ERROR: Attribute '​global_field' is of type 'bit' but default expression is of type 'int4' You will need to  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

Comments
  • can you show your table definition?
  • 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.
  • Do you want to allow explicit NULL values? If not, it would seem trivial to mark the column as not null.
  • 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
  • @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.
  • 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
  • @Alexey Varentsov I did that in managment studio but it still reverts to null on new records.
  • 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, 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.
  • @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 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 ?
  • 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.