June 20, 2006 at 7:30 am
>Bigint is 8 byte, not 16.
ah yes, good point. I thought those mushrooms were a funny shape.
>Varchar can NEVER be more effective than numeric. It uses only 26 combinations out of 128. Ratio is 5:1. 100:20 in basketball terms.
Not sure where your numbers come from. Don't you mean 255:10? (4-0 in football). But then, not using negatives (as people don't) halves the capacity of bigint for a start. And the fixed size of bigint is not so good for the little values...
datalength(cast(99999999999999 as bigint)))/2 roughmeansize_bigint
(datalength(cast('1' as varchar(200)))
datalength(cast('99999999999999' as varchar(200))))/2 roughmeansize_varchar
Still as you point out, storage size is not the main issue. And for all I know, the values get padded before being compared. It's certainly not a straight binary comparison:
http://qa.sqlservercentral.com/forums/shwssage.aspx?forumid=65&messageid=286568#bm288447
Those mushrooms are still making the walls writhe a bit, so before I regain my faculties, perhaps varbinary might be of interest...?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 20, 2006 at 10:36 am
Another reason many of us use varchars over ints (or bigints) for non-calculated numeric values is zero-padded left hand side strings, such as '0000362136236'. While we can often control our own in-house data, we can't always control what the data looks like that comes from the outside. Aside from that, tomorrow I'm sure I'll be asked to merge a new table with an existing one, and that new table will probably have alpha characters in the InvoiceNumber, for example. I've seen a ton of applications fall apart that originally stored zip codes as integer data types and suddenly their companies started doing business with Canada and wanted the zip code field to handle postal codes. Oops.
In house, we use the rule that if you're not going to use it in a calculation and if it's not self-generating, it's not a numeric, unless the developer can come up with a really good business case for it to be so. If storage space ever becomes so expensive that every byte counts, then we'll revisit it, but we're not expecting that in my lifetime.
June 20, 2006 at 10:43 am
Yep.
>If leading zeros were permitted, or if alphabetic (left-to-right regardless of length) sorting/searching on leading characters made sense, then the 'numbers' would clearly be numeric strings and should (presumptively but defeasibly) be stored as such.
I think the more pressing issue is using wide fields for joining on. In that case, every byte (per row) could make a major difference.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply