CHAR vs INT

Question

CHAR vs INT

SSC-Addicted

Points: 412

March 1, 2004 at 10:48 am

#162055

Hi all -- I am using MS SQL 2000 and cannot use the BIT data type because the software accessing the data will not recognize it. I will be storing a 1 or a 0 in the column and basing queries on the contents of that column in tables that will be large but not extremely large.

I also cannot use any other variation of integer other than INT (no TINYINT, for example).

What would be best to use, CHAR(1) or INT? I'm looking for the best performance.

Thank you!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 1

I don't have anything to back this up, but I'd say that it's a wash. The comparison will be 1 v 1 (or 00000001 v 00000001) or it will be 49 to 49 (ASCII 1 is 49). Unless SQL does some type of character comparison function that is more complex. Id' pick int because there's a chance that it is simpler, but it's likely a wash.

Glowball SSC-Addicted Points: 412 More actions · Answer 2

I think INT sounds good too. CHAR will have to go through the code pages so INT is probably a little faster too (though the storage space is bigger).

THANKS!

noeld SSC Guru Points: 96590 More actions · Answer 3

It is a little hard to answer this one right without more knowledge of the queries that are going to be run against it but here is some food for thought:

1. Char(1) is 1 byte and int is 4 (space used is less but you said that tables are NOT extremely large then it really doesn't matter)

2. if you are planing to use some arithmetic with it int is very convenient

3. if at any time you need another Flag int is still right

4. if you are going to use different collations char is succeptible to that int is not

so far usability has been a factor for me instead of speed ( there could be more)

I don't think I still can give you the right answer but hey you can always test

* Noel

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

What is actually the difference when storing 1 and 0 in a BIT or an INT column? Why would the software not recognize BIT, but INT?

Anyway, I'd second Steve, there should be no big difference other than storage as noeld mentioned.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Glowball SSC-Addicted Points: 412 More actions · Answer 5

I went with INT -- space not really being an issue it just seemed like the best idea. As for the software, it works with a ton of different databases so it only supports the very basic data types. Inconvenient...

Thanks everyone!

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 6

What about using datatype: Tinyint.

/rockmoose

You must unlearn what You have learnt

Glowball SSC-Addicted Points: 412 More actions · Answer 7

I also cannot use any other variation of integer other than INT (no TINYINT, for example).

I can't use TINYINT either...

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 8

Well its always a personal choice beyond anything in this issue, as Bit most of the times is basically used to toggle between two values so char(1) would be optimal over int to replace bit because of its space usage .

Prasad Bhogadi
www.inforaise.com

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 9

Ok, sorry did not read the post very accurately 🙂

I would go for INT and maybe put a constraint on the column only allowing 0 and 1 as values. Performancewise I place my bets on INT without having any evidence to back it up, very likely it's a draw anyway.

Using CHAR there could be collation issues when comparing CHAR columns if columns have different collations.

/rockmoose

You must unlearn what You have learnt