SQL Server Performance

INT vs CHAR

Discussion in 'T-SQL Performance Tuning for Developers' started by Glowball, Mar 1, 2004.

  1. Glowball New Member

    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!
  2. bradmcgehee New Member

    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
  3. x002548 New Member

    Really?<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><b>int</b><br /><br />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.<br /><br /><br /><b>char[(n)]</b><br /><br />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.<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br /><br /><br /><br /><br />Brett<br /><br />:cool:
  4. x002548 New Member

    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:
  5. x002548 New Member

    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:
  6. Glowball New Member

    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!
  7. satya Moderator

    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.
  8. FrankKalis Moderator

    Same question as on SQL Server Central? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Soma answer from me: Go for the INT data type. As this is not proprietary as the BIT type, which might be not portable.<br /><br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  9. Glowball New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Same question as on SQL Server Central? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Yep, same question. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] I'm new to both boards.
  10. satya Moderator

  11. x002548 New Member

    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:
  12. FrankKalis Moderator

    Just a funny side-note (don't take it too seriously, as always [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] ):<br /><br />As I am very lazy on typing and SQL Server is not too smart on that topic you can do something like<br /><br />create table test_char(col1 char default('a'))<br />go <br />insert into test_char default values<br />select col1 from test_char<br />drop table test_char<br /><br />col1 <br />---- <br />a<br /><br />(1 row(s) affected)<br /><br />So, there is no need for char(1).<br /><br />FWIW<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  13. bradmcgehee New Member

    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

Share This Page