VARCHAR vs DECIMAL Performance

Question

Our database is SQL Server 2008. For some reason, the DBA (now long gone) made all of our primary keys DECIMAL(38,0). These columns are not specified as identity columns--rather, there is a formula in the application code generates the unique identifier (random number) for the column.

We're obviously not going to get away from having a 38 digit number as the primary key for our data any time soon. And really, the concept is just fine. The number should be as statistically random (if not even more random) than the uniqueidentifer type, we just don't have the convenience of generating the number without the application code.

My question is: If I were to convert these DECIMAL(38,0) columns to VARCHAR(38), would there be any advantages in index size, index speed, data retrieval speed, storage requirements, etc...? We don't need to do any math on these columns, so it really seems like a waste to have them be a DECIMAL(38,0).

I'm more of an application developer than a DBA, so forgive me if this sounds like an ignorant question.

Answer 1

Short answer: no.

You are not likely to see any storage improvement. A decimal(38,0) takes 17 bytes each. A varchar(38) can take up to 40 bytes (actual data length + 2 bytes overhead). Performance is harder to measure but the FPGA on modern CPUs are made to calculate floating point numbers so I naturally expect a big boost for decimal.

One last thing:

The number should be as statistically random (if not even more random) than the uniqueidentifer type

Most random number generators are pseudo-random. They are OK but not great. If your application lives or dies by the quality of the random numbers, try a crypto-level random number generator.