SQL Server Performance Forum – Threads Archive

INT vs CHAR

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!

INT should offer the best performance, as it uses less space to store the actual data. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Really? <blockquote id="quote">quote:<hr height="1" noshade id="quote"> int Integer (whole number) data from -2^31 (-2,147,483,64<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> through 2^31 – 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer. char[(n)] Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character. <hr height="1" noshade id="quote"></blockquote id="quote"> Brett :cool:

quote:Originally posted by Glowball 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.

What does large mean?
Brett :cool:

I thought with a "bit" value with a low cardinality wouldn’t use an index…. Apparantley I though wrong USE Northwind
GO CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1))
GO CREATE INDEX IX1 ON myTable99(Col2)
GO SET NOCOUNT OFF DECLARE @x int
SET @X = 1 WHILE @x < 10000
BEGIN
INSERT INTO myTable99(Col2) SELECT ‘1’
INSERT INTO myTable99(Col2) SELECT ‘0’
SELECT @x = @x + 1
END
GO SELECT COUNT(*) FROM myTable99 WHERE Col2 = ‘1’
GO DROP TABLE myTable99
GO Brett :cool:

Not large meaning probably no more than 5000 records or so. I went ahead with INT because we really don’t have space issues. Thanks!

IN any case use of INT is better than using CHAR in terms of performance/space issues etc. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Same question as on SQL Server Central? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Soma answer from me: Go for the INT data type. As this is not proprietary as the BIT type, which might be not portable. Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Same question as on SQL Server Central? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <hr height="1" noshade id="quote"></blockquote id="quote"> Yep, same question. [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] I’m new to both boards.

Whatsoever, just followup the suggestion. [8D] Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

So are we saying that int out performs char(1)? That a 4 byte to 1 byte difference… Also on 5k volume…you’ll never notice… Brett :cool:

Just a funny side-note (don’t take it too seriously, as always [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] ): As I am very lazy on typing and SQL Server is not too smart on that topic you can do something like create table test_char(col1 char default(‘a’)) go insert into test_char default values select col1 from test_char drop table test_char col1 —- a (1 row(s) affected) So, there is no need for char(1). FWIW Frank http://www.insidesql.de</a> http://www.familienzirkus.de</a>

I submitted this question to Microsoft, and here is there response: I would go with integer for the following reasons: 1) INT data types are faster when it come to comparisons especially if you character collation is anything other than binary. With binary collation the disparity is less but still noticable. 2) Certain optimizations done in the server works best for integer data types as compared to character data types. 3) A decrease in size by three bytes for this particular column will not necessarily result in fewer IO’s. One will have to analyze the table schema to really quantify this.
This posting is provided "AS IS" with no warranties, and confers no rights. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

]]>