Constant values in columns

Question

I have a SQL Server 2000 database used by our ERP system.

There are many columns that always have the same value. A table row is really huge, potentially exceeding the maximum row size in SQL Server 2000 (8kB).

I'm thinking about replacing these columns with constant calculated columns. This should decrease the size of the table. I expect better performance, because there is less data to read from disk, when the server needs to load the table in memory.

Is this a good idea ? Will I obtain the expected performance gain ?

Answer 1

If you use a normal (non-persisted) computed column - then yes, you might save a bit on disk space - BUT always constantly re-computing those values every time you access the row will likely cost you more in terms of processing time than you save by loading less data.

There's really no easy way to predict this - how much performance will you save by loading less data vs. how much performance is needed for constantly re-computing those column values. You'll need to run some performance tests to find out.

If you use persisted computed columns, then those values are stored just the same as with a normal column - so you won't have the negative performance impact from constantly re-computing the values, but you also won't be saving anything on disk space.

PS: I'm not entirely sure that SQL Server 2000 even had the PERSISTED keyword for computed columns (that SQL Server version is just too old for me to really remember...). I believe however that PERSISTED was a new feature in SQL Server 2005, so it wouldn't apply to you.

Answer 2

If the columns you are talking about could be viewed as describing a single logical entity, you could (and perhaps should) move them all to a (new) separate table and establish a primary key/foreign key relationship between the two tables.

For instance, if your table was like this:

some columns       Col1  Col2  Col3
------------  ...  ----  ----  ----
...                A     M     X
...                A     M     X
...                B     N     Y

where Col1, Col2, Col3 were the columns in the need of "compression", you could re-organise your data like this:

NewTable
========

ID  Col1  Col2  Col3
--  ---   ----  ----
1   A     M     X
2   B     N     Y

OriginalTable
=============

some columns       NewTableID
------------  ...  ----------
...                1
...                1
...                2

where NewTableID column in OriginalTable would reference the ID column in NewTable.

When reading data from the two tables, you would first read a set of rows from the original table with references to the new table. When necessary, you would look up the details from the new table with a separate query.