SQL Server Performance Forum – Threads Archive
Data Types
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. MadduriIf 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
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
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
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.
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.
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
]]>