SQL Server Performance

Data Types

Discussion in 'Getting Started' started by madduri, May 17, 2007.

  1. madduri New Member

    Hi,

    I have a small confusion with the datatypes. I want to create a column for phone numbers and would like to restrict the length of the column to 10 digits and want to allow only numeric data and not any other characters.

    With what data type should I define the column? I can not mention the length of the column for int and dont want to use varchar as it allows character data to enter. What is the best datatype that satisfies my requirement above. In Oracle we have numeric. Do we have something similar in MS SQL too.

    Thanks in advance.

    Madduri

  2. Jack Vamvas Member

    If you are strictly only going to have numbers , you could try decimal. Normally , I store them as varchar , handling the formatting issues on the front end

    ___________________________________
    Need an IT job? -http://www.ITjobfeed.com

  3. madduri New Member

    Hi,

    Right now I have defined the column as varchar only. But I was wondering if it allows any character data to be entered. As you said, this should not be an issue if this can be handled by the front end application,

    Thank you for your response.

    Regards,

    Madduri
  4. BulentGucuk New Member

    It's VarChar in our database but the front end app is checking the the lenght and only the numbers are used for the phone number field.

    Good day,
    Bulent
  5. satya Moderator

    Using varchar is nothing but you are wasting space on the database, you could use decimal as per Jack's reference.

    http://www.sql-server-performance.com/database_design.asp
    quote:
    When designing columns to store formatted data, such as Social Security numbers or phone numbers, you have two choices. You can choose to store the data with no formatting or with formatting. Each choice has its pros and cons. If you store the data with formatting, then your disk space requirements will be slightly higher than if you don't store the data with formatting. If you store the data without formatting, then you will have to add or remove formatting each time you INSERT or UPDATE a record, which adds to CPU and memory overhead on the server. In other words, the choice you make affects your server's load.


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. FrankKalis Moderator

    quote:Originally posted by madduri

    Hi,

    I have a small confusion with the datatypes. I want to create a column for phone numbers and would like to restrict the length of the column to 10 digits and want to allow only numeric data and not any other characters.

    With what data type should I define the column? I can not mention the length of the column for int and dont want to use varchar as it allows character data to enter. What is the best datatype that satisfies my requirement above. In Oracle we have numeric. Do we have something similar in MS SQL too.

    Thanks in advance.

    Madduri


    I would go for VARCHAR(10) since it can happen that you have leading 0s that would be removed when it is a numeric data type. To avoid any characters in the data, you could use a CHECK constraint like


    CHECK (column LIKE [0-9][0-9]....[0-9])


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de

Share This Page