SQL: What is better a Bit or a char(1)

Is there any performance difference on retrieving a bit or a char(1) 开发者_运维百科?

Just for curiosity =]

UPDATE: Suposing i'm using SQL Server 2008!

For SQL Server: up to 8 columns of type BIT can be stored inside a single byte, while each column of type CHAR(1) will take up one byte.

On the other hand: a BIT column can have two values (0 = false, 1 = true) or no value at all (NULL) - while a CHAR(1) can have any character value (much more possibilities)

So really, it comes down to:

• do you really need a true/false (yes/no) field? If so: use BIT
• do you need something with more than just two possible values - use CHAR(1)

I don't think it makes any significant difference, from a performance point of view - unless you have tens of thousands of columns. Then of course, using BIT which can store up to 8 columns in a single byte would be beneficial. But again: for your "normal" database case, where you have a few, a dozen of those columns, it really doesn't make a big difference. Pick the column type that suits your needs - don't over-worry about performance.....

a bit and a char(1) will both take a 1 byte to store,assuming you only have 1 bit column in the table, SQL Server will store up tp 8 bit columns in 1 byte. I don't think there is a difference in performance.

One thing to be aware of is that you can't do sum on a bit column

CREATE TABLE #test( a BIT)

INSERT #test VALUES (1)
INSERT #test VALUES (1)

SELECT sum(a) FROM #test

Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for sum operator.

you have to convert it first

SELECT sum(CONVERT(INT,a)) FROM #test

It's implementation dependent. One DBMS might have the same performance, while another might have differences.

As Adam says, it depends on the database implementing the data types properly, but in theory the following holds:

Bit:

Will store 1 or 0 or null. Only takes a Bit to store the value (by definition!). Usually used for true or false, and many programming languages will interpret a bit as a true or false field automatically.

Char[1]:

A char takes 8 bits, or one byte, so its 8 times larger when stored. You can store (pretty much) any character in there. Will probably be interpreted as a string by programming languages. I think Char[1] will always take the full byte, even when empty, unless you use varchar or nvarchar.

use a bit. ALWAYS use the smallest possible datatype. it is critical once you start getting large tables.