Store Text or Number?

[TKnight]

Hi - I hope this question isn't quite as noobie as it might at first appear!

I have to store a TimeStamp value which is used to determine the chronological order of records. If this was a date field no problem but I actually have to decode it from an XML Base64Binary field and it comes out like this:

Text: 000000203139
Number: 203139

I don't need to do any calculations on it just determine if one number is higher or lower than another which < and > should do whether it is stored as a text or number.

My first thought was to store it as text as it uses less memory than numeric fields but then I thought there will be a lot of essentially useless 0s which would probably outweigh those benefits. As this table is going to grow by about 35k records a week the size and query speed if I use this field is important.

As always your opinions are most valuable!

Thanks,

Tom

 

[Atomic Shrimp]

If you're adding records to the field in chronological order, it might be easier to use an autonumber to determine the sequence and ignore the other field.

If you do want to be able to sort by this number I don't think it makes any difference which way you store it...

string: 0020 is less than 0100
Number: 20 is less than 100

It would be bad to strip off the leading zeroes, then store as a string, because when evaluated as a string, 20 is greater than 100 (string comparison is performed from left to right)

 

[namliam]

It is always best to store data as the format that it is.... is it a number? Store as a number. Is it text? Store as text.

This is a formatted number, store as a number... Saves space and is faster/more reliable when sorting and doing things like > or <