Menu
Is there any performance difference on retrieving a bit or a char(1) ?
Just for curiosity =]
UPDATE: Suposing i'm using SQL Server 2008!
409
In MS SQL VARCHAR(1) uses three bytes of storage and CHAR(1) uses one byte of storage. CHAR(1) is more efficient than VARCHAR(1) in processing and storage. The breakeven point on VARCHAR would be anything greater than 3 characters if using variable length data.
Because of the fixed field lengths, data is pulled straight from the column without doing any data manipulation and index lookups against varchar are slower than that of char fields. CHAR is better than VARCHAR performance wise, however, it takes unnecessary memory space when the data does not have a fixed-length.
We should use the CHAR datatype when we expect the data values in a column are of the same length. We should use the VARCHAR datatype when we expect the data values in a column are of variable length.
CHAR takes up 1 byte per character. So, a CHAR(100) field (or variable) takes up 100 bytes on disk, regardless of the string it holds. VARCHAR is a variable length string data type, so it holds only the characters you assign to it.
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:
BIT
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.....
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
