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 ?

  • 1
    "I think about replace the with a constant calculated column" - this line does not make sense to me and maybe your question did not make it into SO as you intended. Could you elaborate? – Thronk Aug 28 '13 at 12:56
  • I think he means "I'm thinking about replacing them with ..." – ypercubeᵀᴹ Aug 28 '13 at 13:07
  • 2
    @a_horse_with_no_name: a computed column is not stored - it's computed every time you access it - unless you specify PERSISTED, then it's persisted like a normal column (and thus doesn't save any space) – marc_s Aug 28 '13 at 14:16
  • 1
    And SQL-Server 2000 does not have PERSISTED columns, only virtual ones. – ypercubeᵀᴹ Aug 28 '13 at 14:22
  • 1
    Can you clarify the business goal? I wonder why you'd really always have the same value for every row. The same few values, sure, even one of two for each row, but the same? – Jon of All Trades Aug 28 '13 at 14:24

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.


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:


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


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.

  • The problem is that I cannot freely redesign the tables, because the application using them is not so easy to modify. The database is really poorly designed, but I have to keep it as it is, seen from the application's perspective. – Lorenz Meyer Aug 28 '13 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.