The thing to understand with databases like SQL Server is that as soon as you start comparing and/or joining values, you really hope that they are integers (or bigints). If I had a 12 digit number that was a customer number, I’d just be using a bigint.
As soon as you start comparing character values, things get slow. It might seem simple to write a statement like WHERE Somecolumn = ‘A’ But unfortunately, that’s not a simple comparison. SQL Server has to apply all the rules for the specified collation, and then do that column by column. It’s a relatively big job, certainly nothing like “are these two integers the same value?”. I was at a site recently where they were changing all the IDs from bigint to uniqueidentifier (GUID), not for any real GUID-related reason, but because they were concerned about the possibility of running out of bigint values. (Clearly it’s a pity more maths isn’t taught at schools). Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: www.sqldownunder.com<http://www.sqldownunder.com/> From: [email protected] [mailto:[email protected]] On Behalf Of Greg Keogh Sent: Thursday, 10 September 2015 10:12 PM To: ozDotNet <[email protected]> Subject: Re: Odd text encoding i think ive read it somewhere, a rule of thumb is if you dont need to do calculations with your "number", make it a string In 2006 where I was working they created a new SQL DB and the natural key of the most important table was a 12 digit number (customer number I think) and there were arguments about if the PK should be a string or a number. For some reason they chose a DECIMAL(12,0) which vaguely worried me, but I didn't get involved and I still wonder if a 64-bit integer would have been better, or even a CHAR(12) ... still dunno. GK
