Can anyone tell me if there is any performance degradation in using a non fixed length datatype as a key field?
I've heard it thrown around by some database developers that we should be using char fields instead of varchar, which to me makes no sense at all. I would have thought the less bytes you store the better. But apparently index performance is much poorer on non fixed length fields i.e varchar.Can someone please confirm or correct this?
A simple practical experiment
No execute the following query and observe the page reads
Query against my VarCharIndexTable table took a logical read of 91 and for the CharIndexTable it took 124
The reason behind this is that the index key on VarCharIndexTable is a varchar, is a variable-length data type, the storage size of the varchar value is the actual length of the data entered ie in our case its 1 byte.
In the case of CharIndexTable the index key is of CHAR, its a fixed-length character data type. In our case it will consume 10 byte key.
The index page of VarCharIndexTable can accommodate more number of rows than that of CharIndexTable Index. ie for complete Index scan operation index on VarCharIndexTable needs to process 91 pages only, but in the case of CharIndexTable it is 124 page.
answered Sep 29, 2010 at 06:43 AM
You can do some really rudimentary testing to see the difference between a varchar and char column:
My example uses a [Tally Table] to generate the test data.
Using my Tally table (11000 rows), I get the following results:
As you can see TableB (varchar) has 48KB more "data" than TableA (fixed length).
This can be "levelled-out" if you have SQL Server Enterprise Edition and use data compression:
This reduces the space used for both and effectively making the varchar column the same as a char:
answered Sep 29, 2010 at 06:51 AM