SQL Server 2008 - Editing Tables: Bit columns require 'True' or 'False'

By : CJM
Source: Stackoverflow.com
Question!

Not so much a question as an observation...

I'm just upgrading to SQL Server 2008 on my development machine in anticipation of upgrading my live applications. I didn't anticipate any problems since [I think] I generally use standard T-SQL, and probably not too far from ANSI standard SQL. So far so good, but I was really thrown by a very simple change:

I was creating a simple, small look-up table to store a list of codes and including a bit column to indicate the current default code. But when I used the new/modified 'Edit Top 200 Rows' option, and entered my 0s and 1s in the the bit column I got an error:

'Invalid value for cell - String was not recognised as a valid boolean'

After a bit of head-scratching, I tried True and False - and they worked.

So it seems this new Edit feature requires 4 or 5 characters to be typed, rather than the previous 1.

Checking further, we can still use '...where bitval = 1' but can now also use '...where bitval = 'true''. But any results returned render these bit columns as 0 or 1 still.

It all sounds like half a step backwards. Not the end of the world, but and unnecessary annoyance.

Does anybody have any insight on this issue? Or there any other new Gotchas with SQL Server 2008?

2012 Update: Having just installed SQL Server 2012, I notice that this behaviour has been 'corrected' - Just as I could before, I can now enter 1/0 rather than True/False directly into rows, using the 'Edit Top 200 Rows' option. It's a small thing, but I'm happy.

By : CJM


Answers

Except they blew it and only half enforced the rule Try using ...=True in an update statement and see what happens. Either go all the way or not at all. Sorry Microsoft, can't give you any marks on that one

By : DOD


Just found this (after preparing soemthing else) Built in by design

By : gbn


You've always been able to use 'True' and 'False' to compare against BIT columns... I think this change to "force" it is a good one to be clear that the field is a BIT, and not an INT.

Personally, I like that C# doesn't support "if (0) ..." I guess it's just a preference for clarity.



This video can help you solving your question :)
By: admin