SQL Server Performance

ZIP code data type

Discussion in 'General DBA Questions' started by CanadaDBA, Jul 23, 2004.

  1. CanadaDBA New Member

    What is the best suggestion for ZIP code data type in USA? USA has different formats for ZIP code. It is as either ##### or #####-####. Am I right?

    Our current DB has Numeric 5(9,0) and Numeric 9(18,0) for ZIP code. If I choose VarChar(10), then do you think I may lose data during the conversion? Do you suggest me any code for this conversion or just do it in EM?

    CanadaDBA
  2. tdong New Member

    can't think of anything better than VarChar since you want to store the dash in there too (#####-####). I have a few varchar in my database and never have any error coverting some number from varchar.

    May the best cheaters win
  3. CanadaDBA New Member

    The format (#####-####) is a suggestion only. In fact I am asking about USA Zip codes and
    the best idea to handle it.


    quote:Originally posted by tdong

    can't think of anything better than VarChar since you want to store the dash in there too (#####-####). I have a few varchar in my database and never have any error coverting some number from varchar.

    May the best cheaters win

    CanadaDBA
  4. tdong New Member

    The answer is still Varchar for USA Zip code hehhehe <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br /><br />the thing is if the ZipCode start with a Zero <img src='/community/emoticons/emotion-1.gif' alt=':)' /> then Varchar save you<br />however number search is faster <img src='/community/emoticons/emotion-1.gif' alt=':)' /> no need to convert.<br /><br />Still I would use Varchar for Zip Code since I live in Canada and the Zip is number and Characters<br /><br />May the best cheaters win
  5. CanadaDBA New Member

    In Canada we have only 6 characters but in US they have different Zip code versions. Some of them are 5 char a new version 9 and I am not sure the latest version is 10 or 11 char.<br /><br />I think VarChar is the answer. If I don't want to use EM, should ALTER TABLE sovle my problem? I want to write a script to do the table change. If so, does it automatically convert data? <br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by tdong</i><br /><br />The answer is still Varchar for USA Zip code hehhehe <img src='/community/emoticons/emotion-1.gif' alt=':)' /> <br />...<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />CanadaDBA
  6. tdong New Member

    Number to Varchar it should be ok and yes it will convert data automatically <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />May the best cheaters win
  7. ykchakri New Member

    Number to varchar conversion does convert data automatically, but you have to manually (or through a script) fill the zeros for the numbers that should start with 'zero'.

Share This Page