SQL Server 2008 - Editing Tables: Column Bit Requires "True" or "False"

Question

Not so much a question as an observation ...

I just upgrade to SQL Server 2008 on my development machine while waiting for the update of my real applications. I did not expect any problems since [I think] I usually use standard T-SQL and probably not too far from standard ANSI SQL. So far so good, but I was really thrown by a very simple change:

I created a simple little lookup table to store a list of codes and included a bit column to indicate the current code by default. But when I used the new / modified option "Edit Top 200 Rows" and entered my 0 and 1 in the bit column, I got an error:

'Invalid value for cell. The string was not recognized as a valid boolean '

After a little scratching my head, I tried True and False - and they worked.

So, it seems that for this new editing function, you need to enter 4 or 5 characters, not the previous one.

Further, we can use ' ...where bitval = 1 ', but now we can also use ' ...where bitval = 'true' '. But any results return these columns of bits as 0 or 1.

Everything sounds half a step back. Not the end of the world, but also unnecessary irritation.

Does anyone have an understanding of this problem? Or are there other new Gotchas with SQL Server 2008?

2012 Update: Just by installing SQL Server 2012, I noticed that this behavior was “fixed”. As before, now I can enter 1/0, and not True / False directly into the lines, using the "Edit Top 200 Rows" parameter. This is a small thing, but I'm happy.

Answer 1

Except that they blew it up and only half-forcedly followed the rule. Try using ... = True in the update instructions and see what happens. Either go completely, or not at all. Sorry, Microsoft cannot give you any ratings on this.

Answer 2

You could always use "True" and "False" to compare with BIT columns ... I think this change in the "power" state is good, so that it is clear that the field is BIT, not INT.

Personally, I like that C # does not support "if (0) ..." I assume this is just a preference for clarity.

Answer 3

Just found it (after preparing something else) Built by design