May 2, 2007 at 3:02 am
All
Very easy question I hope.... I was told that when ever you store a number that you are not doing any math against you should store the value as a char not a number.
So the question is, is the above correct or not, and why?
Thank you for your time in advance.
May 2, 2007 at 4:09 am
god help us! I don't know who told you this but I'd suggest you don't take any further advice < grin >
Strong data typing is an essential key foundation stone of any development, we used to do things like this back in the days of 8bit home computers when a single digit number might take less space as a single char than as an int , when you only had 8k to write your code this could help, but this should not happen today and in SQL mixing your data types can lead to poor performance due to conversion issues.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 3, 2007 at 2:50 am
Perhaps the reference to a number that you are not doing any math against means that the "advisor" was thinking of the need to distinguish mathematical values from numeric strings such as phone numbers. Sound advice, poorly delivered!
May 3, 2007 at 6:39 am
Agree with the others here that you should store data in the appropriate datatype, for a number of reasons. One thing you should do also is to pick the CORRECT numeric datatype for what you are storing. Choose the smallest datatype that will contain the largest value the table will ever have. For example, if you have a set of numbers that goes from 1 to 32, you can use a tinyint, which is only 1 byte of storage. Smallint is 2, int 4. See BOL for details on datatypes, their allowable values and their storage sizes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2007 at 9:16 am
int.
Save space, makes more sense.
A phone number isn't really a number. It's a string of numerical characters.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 4, 2007 at 7:16 am
If you store numbers as a string, then you'll never be able to order (sort) them properly. E.g., the numbers 1 through 10 will sort as:
1
10
2
3
4
5
6
7
8
9
However, certain items commonly having digits should be stored as a string (with proper editing and formatting). E.g., the "postal code". Which in the US is 5 (or nine) digits (always with the leading zero). In other countries, letters are often used. So if you create a column of ZIP code being an int, you're immediately in trouble as you're system is not global.
May 4, 2007 at 7:58 am
Good to also distinguish between char and varchar. Chars are fine if the data is really fixed width.
May 8, 2007 at 9:39 am
If you are importing data from an outside source and a field that should be numeric has bad data in it, you might need to import it into a char or varchar field, clean up the data and then convert it back to int.
Steve
May 8, 2007 at 9:47 am
Better would be to use SSIS and let it handle data cleansing/rejecting for you! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply