Sql-server – Constant values in columns

database-designsql serversql-server-2000virtual-columns

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 ?

Best Answer

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.

Related Question