Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DataType confusion Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 5:44 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:04 AM
Points: 147, Visits: 301

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
Post #1357917
Posted Wednesday, September 12, 2012 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201

...
"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

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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1357924
Posted Wednesday, September 12, 2012 6:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1357933
Posted Wednesday, September 12, 2012 7:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:04 AM
Points: 147, Visits: 301
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
Post #1357972
Posted Wednesday, September 12, 2012 7:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1357978
Posted Sunday, February 17, 2013 2:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 17, 2013 1:56 AM
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).

Post #1420950
Posted Sunday, February 17, 2013 3:25 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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'.

 
Post #1421010
Posted Monday, February 18, 2013 10:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 12:28 PM
Points: 14, Visits: 387
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
Post #1421430
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse