Cannot add allow null with a bit data type

[humbletechie]

Help! I created a table with a bunch of bit data types and they all are set to allow null. I am now trying to add another bit field and it gives me the following error
'TableName' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'ColumnName', table 'blabla.dbo.Tmp_Table'; column does not allow nulls. INSERT fails.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
It allows me to add other data types just not bits. Can anyone help?

 

[tlbroadbent]

Which version of SQL Server do you have? Terry

http://members.home.net/tlbroadbent/index.htm

 

[humbletechie]

7.0

 

[tlbroadbent]

Does the new bit column have a default value? Set the default value to zero and then alter the table. Let us know if that works. Terry

http://members.home.net/tlbroadbent/index.htm

 

[humbletechie]

It let me save the bit field with the default value set to 0 but it's still not letting me check allow null.

Thanks for your fast responses!

 

[sdavison]

Try this:
add the new column as int, save with default = zero, then change it to bit and save.

 

[Ruairi]

I tried crating a table with a bit column set to allow nulls in SQL Server 6.5 and SQL Server 2000 (i don't have access to a version 7 server). In 6.5 you cannot create a table with a bit column that allows nulls. It gives a message like 'Selected column's datatype cannot allow nulls'. This makes perfect sense, since a bit is either a 1 or a 0, there is no other possible value. In SQL Server 2000 i was able to create the table with a nullable bit column and to subsequently insert to that table leaving the bit column out of the list of values. It inserted as NULL. This absolutely does not make sense with a datatype of bit, but MS must have decided to add that capability making bits not really bits in 2000. Maybe in version 7 they tried to implement this nullable bit data type but didn't get it quite right and fixed the bug before 2000. I still don't understand why you would be using a bit data type for columns that have more than 2 possible values. If you want to be able to enter 1, 0, and NULL why not use an integer column? Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Cagliostro]

lackiem, try to open table in design mode and check the box allow null on the column name. I also have SQL7, and it allows nulls for bit values. John Fill

ivfmd@mail.md

 

[link9]

That's wierd -- I also use SQL Server 7, and mine will not allow me to have NULL for bit data type --

My fix has been as tlbroadbent suggested -- give it a default value, and then modify the values as needed with a query --

Besides -- if it's really a bit data type you want (i.e. on or off), then why would you want to allow NULL ???

If you need a NULL for that field, then I submit that you need some other type of data than bit.

 

[Cagliostro]

Try to run this query on your servers. After it look at resulted table on enterprise manager. On my server it runs ok.
CREATE TABLE [dbo].[NullBits] (
[RecordID] [int] identity,
[BF] [bit] NULL ,
[EF] [bit] NULL ,
[ManyB] [bit] NOT NULL ,
[ManyE] [bit] NOT NULL ,
) ON [PRIMARY]


John Fill

ivfmd@mail.md

 

[humbletechie]

Thanks everyone for all your help and advise on this.

 

[foxdev]

if it's really a bit data type you want (i.e. on or off), then why would you want to allow NULL

For the same reason you might want to allow NULLs on any other column: to denote that no value has yet been assigned.

Take for example a survey results table: a series of yes/no questions make up the various rows/columns, and you record "yes" as 1 and "no" as 0. If a survey respondent did not answer a question, how should you mark it? Using 0 or 1 is incorrect, and can skew your results. NULL is the only logical value.

As to why use bit instead of int (a reasonable alternative), the reason is the same as why you'd use smalldatetime rather than datetime, or smallint rather than int: to save storage space (and, by extension, so save retrieval time, since in theory more rows would fit on a page).
Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[Ruairi]

Robert,
I'm not necessarily doubting you here, as a have seen your aswers to many threads and have always found them to be informative. I would wonder how you can assume that SQL Server is actually using less storage for a bit than a smallint. In version 6.5 when nulls were not allowed on bit data types (at least with the settings selected on my 6.5 server) it makes sense that bits only used one bit of storage space. After nulls were allowed on those columns i would submit that it is physically impossible for SQL Server to store a field with possible values 1, 0, and <NULL> in one bit of storage space. As i'm sure you are aware one bit of storage can have only 2 possible values, not 3. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[Cagliostro]

I do not know, if he need really a NULL bit or not. But the question was 'how to do it?'. John Fill

ivfmd@mail.md

 

[Ruairi]

I was only questioning robert's assertion that a bit data type is actually stored in a bit of storage, not disagreeing that anyones solution would indeed create a nullable 'bit' column. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[foxdev]

I would wonder how you can assume that SQL Server is actually using less storage for a bit than a smallint.

From BOL (SQLS 7):

If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte.

I meant to add that to my previous post, but work got in the way. Essentially, if there are only a couple of bit columns, they are indeed stored as 1 byte (the same storage used by tinyint). For the example I used of a survey response table, though, you'd expect to have quite a few bit columns.

But in the context of the OP, here's where it gets really interesting (keep in mind this is from SQLS 7 BOL):

Integer data type that can be 1, 0, or NULL. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com

 

[Cagliostro]

It begins to be a discution about nothing. John Fill

ivfmd@mail.md