NCHAR(1) vs BIT

Question

I'm working under refactoring of database (SQL Server 2008) scheme and gather arguments to change NCHAR(1) columns (which keep Y|N values) to BIT. Everybody understand this is necessary and don't know why does it take place but this change affects production database so weighty arguments are required. Table keeps address catalog (up to 1m of records).

First argument I found - each nchar fields take 2 bytes, each 8 bit fields - 1 byte (next 8 - additional 1 byte).

What's next? Maybe some indexes performance issues?

Answer 1

Create the bit field, add a computed column that emulates the nchar(1) for now.

What not to use nchar:

• Y vs y vs some unicode Y
• Overhead of checking Y or N
• Not natively "true" o "false" (eg won't map directly to .net boolean)
• Y and N are English. Ja/Nein, Oui/Non etc

You shouldn't index this anyway so it comes down to efficient storage and use. bit is

• smaller
• datatype safe (eg no CHECK needed)
• maps to client meaning directly
• independent of region

Saying that, we use a smalldatetime "WhenInactive" field as a substitute for "IsActive" field. NULL = active.

Answer 2

If you are using LINQ2SQL or Entity Framework a BIT column will translate into a bool, but NCHAR(1) will translate into a string.

Answer 3

Is the field used extensively in queries Where fld = 'Y'?

If so i would consider doing a test to see whether or not changing it to bit impacts performance.

Changing it now just because it ought to be a bit field since you're storing boolean values on a table of 1m+ records doesn't sound like a good idea to me either and i'd go with @Andrew's answer.

Answer 4

A bit field helps your logic by automatically enforcing what is currently an implicit business rule (i.e., this column can only contain 'Y' or 'N'). If you're enforcing that rule programmatically, you can save by eliminating that overhead. Indexing a bit column on its own has little value due to the low cardinality, but it could be useful as part of a composite index.

See also:

• Bit vs. Char(1) in SQL Server
• Should I index a bit field in SQL Server?

Answer 5

Use Bit:

• Logical representation / expressiveness of intent - since boolean states aren't always expressable consistently as Yes or No, which would then mean you would either need to be inconsistent in modelling bits, or non-intuitive, e.g. True/False (T/F), On/Off (?O/F), Open/Closed(O/C) etc.

• Referential integrity - non-nullable bit can be restricted to only 0 or 1. Unless you add constraints, your *char(1) could be Y,N, X or ☺.

• Bits can be packed, so could have smaller storage.

• Re: Performance : Indexing of bit (or few-state CHAR) columns is usually a waste, unless there is high selectivity of either 0 or 1 in the data. In this case, a filtered index on the selective value would be a good idea.

(Migrated from deleted answer here)

Answer 6

One common reason to find NCHAR(1) instead of bit is that Oracle did not support a bit type. If you had an Oracle or Oracle-trained developer, or a database that used to run on Oracle, you're gonna see this a lot. In Sql Server, there's really no need for this.

However, I've found that most places where I have a bit field (or NCHAR(1) in Oracle) what I really want is a datetime that indicates not as much the value of the flag but exactly when it became true. This isn't always that case, but when I think back about old code I've written I'd guess that 4 out of 5 times I used a bit field I should have used a datetime.