{"id":712,"date":"2018-06-24T18:29:42","date_gmt":"2018-06-24T11:29:42","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=712"},"modified":"2020-07-11T03:23:53","modified_gmt":"2020-07-10T20:23:53","slug":"sql-server-bit","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-bit\/","title":{"rendered":"SQL Server BIT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server BIT<\/code> data type to store bit data in the database.<\/p>\n\n\n\n

Overview of BIT data type #<\/a><\/h2>\n\n\n\n

SQL Server BIT<\/code> data type is an integer data type<\/a> that can take a value of 0, 1, or NULL<\/code>.<\/p>\n\n\n\n

The following illustrates the syntax of the BIT<\/code> data type:<\/p>\n\n\n

BIT\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server optimizes storage of BIT<\/code> columns. If a table has 8 or fewer bit columns, SQL Server stores them as 1 byte. If a table has 9 up to 16 bit columns, SQL Server stores them as 2 bytes, and so on.<\/p>\n\n\n\n
SQL Server converts a string value TRUE<\/code> to 1 and FALSE<\/code> to 0. It also converts any nonzero value to 1.<\/p>\n\n\n\n
SQL Server BIT examples #<\/a><\/h2>\n\n\n\n
The following statement creates a new table<\/a> with one BIT<\/code> column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> test.sql_server_bit (\n    bit_col BIT<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_bit (bit_col)\nOUTPUT<\/span> inserted.bit_col\nVALUES<\/span>(1<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n
bit_col\n-------\n1\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\n
To insert a bit 0 into the bit column, you use the following statement:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_bit (bit_col)\nOUTPUT<\/span> inserted.bit_col\nVALUES<\/span>(0<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
bit_col\n-------\n0\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\n
If you insert a string value of True<\/code> into the bit column, SQL server converts it to bit 1:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_bit (bit_col)\nOUTPUT<\/span> inserted.bit_col\nVALUES<\/span>\n    ('True'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output:<\/p>\n\n\n
bit_col\n-------\n1\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\n
Similarly, SQL Server converts a string value of false<\/code> to bit 0:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_bit (bit_col)\nOUTPUT<\/span> inserted.bit_col\nVALUES<\/span>\n    ('False'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following is the output:<\/p>\n\n\n
bit_col\n-------\n0\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\n
SQL Server converts any nonzero value to bit 1. For example:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> test.sql_server_bit (bit_col)\nOUTPUT<\/span> inserted.bit_col\nVALUES<\/span>\n    (0.5<\/span>); \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following is the output:<\/p>\n\n\n
bit_col\n-------\n1\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQL Server BIT<\/code> data type to store bit data in a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
To insert<\/a> a bit 1 into the bit column, you use the following statement:<\/p>\n\n\n