Reduce Storage Space

[pgthyagu]

Hi All

I am currently trying to reduce the storage space of a 80 GB SQL Server 7.0 database.

One Major work i am trying to do is to convert all the datetime datatype columns to smalldatetime and all numeric datatype columns to int.

Since 30% of the columns in the database have columns of this datatype. This should reduce the storage space.

After i changed the column type of all the tables. I don't see any change in storage space per table. I got the info using sp_spaceused.

Even after i issue dbcc dbreindex, and then sp_spaceused , these is not difference in the storage allocation.

Is there any other way of doing this.

Thanks in advance

 

[tlbroadbent]

You're making 1 or 2 byte changes in column sizes. The differences may not be significant unless there are millions of rows.

I recommend examing the following.

1) look for columns defined with nvarchar or nchar data type. If unicode is not needed, convert these to varchar or char. Unicode uses 2 bytes of storage for each character.

2) Look for columns defined with char data type. Char data type is generally space filled. If a column is char(200) but the average length of data is 50 characters, 150 characters (average) will be wasted space used per row.

After changing char to varchar, you'll need to update the table to remove trailing spaces.

Update table set colname=rtrim(colname)

3) Note that as you address the issues above you will also be reducing index sizes.

4) Examine the indexes being used. Sometimes index space is greater than the data space used. One huge problem area is defining multiple column clustered indexes. SQL users often don't know or forget that all non-clustered indexes carry the clustered index columns. If the clustered index is 100 bytes long, every index will be 100 bytes plus length of columns in the index. SQL Performance experts recommend small clustered indexes.

In a recent Tek-Tips thread, someone described a 5.5 million row table with a clustered index on every column including a description column of 200 characters. By removing the description column from the clustered, the index space used was reduced by 3GB!

5) Remove any redundant or unnecessary indexes. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.