SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DataType confusion


DataType confusion

Author
Message
MothInTheMachine
MothInTheMachine
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 319
Greetz!

I was speaking with a client a few days ago and they said something that contrasted with what I had learned about choosing datatypes.

The comment was "I learned that if you are not doing math with a number then it should be stored as character data." I didn't say anything because I had never heard that and wanted to be certain that the way I had learned was the preferred way. My understanding is that SQL Server is optimized to use numerical values and to store numbers, even though they aren't used in mathematical equations, adds overhead due to storage and conversion requirements. The numbers in question appear to be key values.

I'm hoping that someone here who works with SQL Server everyday would be able to resolve this for me. If you know of an authoritative source that talks about why it should be done one way or the other I would love to read that as well.

Thank you!

Love them all ... regardless.
- Buddha
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5024 Visits: 5478

...
"I learned that if you are not doing math with a number then it should be stored as character data."
...


The man was in a wrong school Hehe

There are examples where numbers have nothing to do with math, but they still better to be stored as numbers.
What about numeric sequences? You are no going to apply math to it, as they are mainly for order.
Integers, in this case will take less space and perform much better in ORDER BY then characters, not saying that the order for numbers saved as character datatype will be very different to order based on the numeric value...
I would say if the number you store is a number then use numeric datatype.
If a number is in reality sequence of digits, which very often is subject of some formatting (eg. phone numbers), - then you better to use character datatype. It has nothing to do with math.
There is a blog about choosing wrong ones: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/bad-habits-to-kick-using-the-wrong-data-type.aspx

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23703 Visits: 9730
It depends on what they mean by "numbers with no math".

Storage-wise, character data is more expensive. TinyInt can store numbers 0-255 in a single byte of data, but 1 byte of storage for strings, char(1), only allows storing 0-9. Similar for larger numeric types. That also means more RAM, more I/O, etc., when the data is live.

If they're storing bar-code values, or anything similar to that where the position in the number determines the meaning of the data, then char datatypes will be better because you won't lose leading zeroes, can use substring and patindex, and things like that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
MothInTheMachine
MothInTheMachine
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 319
Thanks for the feedback. Good Stuff! I should have been more detailed about the numerics being stored. Firstly, the data in question originates in other systems and is brought into this table via a SSIS job. It is then being used for reporting. I have been asked to build a cube from it.

The numerics appear to be key values. In fact some of the column names have 'Code' in the name. Their values? 578, 99 , 2010, 43417620, 000, 63, 9, 4. Nothing too large. There are also several columns that represent a count for a given fiscal year so values like 20, 4 and zero are common.

Another obvious numeric would be an auto-incrementing identity field, which the majority of these appeared to be. I don't have much background on the data yet.

I once heard that SQL is optimized and works most efficiently with numerical data. I'm not sure of the rationale for this unless the way it is stored in the indexes, or that there isn't as hefty a conversion required, or what. If that is the case then it would make sense to store the numbers in the right sized integer fields..especially if the original tables had them as integers to begin with.

Is there any truth to SQL Server performs optimally with numeric data? What about numeric vs char indexes? does one provide a benefit over another?

Love them all ... regardless.
- Buddha
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88068 Visits: 45277
Main point... Are leading zeros important?

If this is something like a postal code where the code is 0001, not 1, or employee numbers, etc, then that must be stored as character data. If the leading zeroes aren't relevant (it's a quantity, a value, a count, a code where it's 2, not 02, etc), then store it as numeric data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


adelali
adelali
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
I use SQL server to generate barcode.
Barcode Generate CRI SDK for Reporting Service is designed to generate, create various linear & 2D barcode into Microsoft SQL Server Reporting Service (SSRS) reports with a Custom Report Item (CRI).
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)SSC Eights! (841 reputation)

Group: General Forum Members
Points: 841 Visits: 1721
Just to get in my 2 cents. If it's a number, store it as a number. If it just LOOKS like a number (zip codes, phone numbers, govt ID numbers, etc) then store it as a char value of some sort.

I think what your client may have been trying to say, for example, is that you wouldn't want to (and can't) add one zip code to another. That would indeed make no sense. So a zip code isn't really a 'number' even though it uses 'numeric' values and looks like a number.

If you have ever seen zip codes entered as integers you've probably experienced the dropped leading zeroes problem or seen a zip like '34567-1234' come out like '33333'. Crazy

 
SQL Fool
SQL Fool
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 407
Another possible reason to store human readable/searchable codes, keys, identifiers, etc. as character data might be full text indexing. FTI will not index integer columns.

Steve Pirazzi
ONEWARE, Inc.
http://www.ONEWARE.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search