Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
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

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
2  
You are right, persisted computed columns were added in 2005: Table Transact-SQL Enhancements –  ypercube Aug 28 '13 at 14:33

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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