comparion/performance of database field as int vs char(1)

Question

does one perform better over the other in terms of indexing/quering etc ?

e.g. declaring a column as active Char(1) with 'Y' / 'N' values vs is_active tinyint with values 1 / 0

Anecdotally speaking most places it seems people prefer to use char(1) whereas my tendency is to declare them as tinyint/int not the least because it's easy to use/type

Any real performance comparisons out there ? I recall something like a binary value index in oracle ( bitmapped index or some such ) - is there something similar in sql2005/sql2008/...?

UPDATE - in response to some of the comments below, performance would be defined in terms of index query / updates, storage is irrelevant considering it's cheap enough

semantics too are not relevant !

Answer 1

Any column with char/varchar has the potential of sending you through all of the extra internal code for the semantics of letter translation / collation, etc. during indexing, equality tests, etc.

So, a number is better. And bit is the best solution for the case you describe--it stores only a 0 or 1 (and is nullable of course).

SQL Server does collapse bit fields internally to store up to 8 bit fields in each byte, so it is more efficient than tinyint if you have multiple bit fields:

http://msdn.microsoft.com/en-us/library/ms177603.aspx

Answer 2

There are no integer or logical data types in Oracle. A bitmapped index is something different that is derived from column values of other datatypes.

Answer 3

It would be nice if all the RDBMS products supported a Boolean datatype. Then we could kill off this question. As it is, it comes down to personal taste. I find it easier to understand ...

if is_active = 'Y' then

... rather than ...

if is_active = 1 then

But if I spoke French instead of English I would want it to be O/N, whereas the 1/0 implementation is universal. The key thing is to use a single convention throughout the system.

With regards to performance, if your biggest bottleneck is the difference between char(1) and integer then I salute your tuning prowess.