CODEWITHSUNDEEP
sqlsql-server
discosammy
discosammy

Reputation: 341

Measuring the impact of empty spaces in a SQL Server database

I've been taught to never ever store unneeded empty spaces in a database.

I thought it was because of all the wasted space.

So I found a table with 3 million plus rows. The columns were twice as long in some cases due to extra spaces. Wrote a script to trim off the leading and trailing spaces and convert empty strings to nulls.

I used sp_spaceused to measure how much impact this had on the amount of space the table was using and I'm seeing almost none.

The only size change is index_size... and that changed by 1mb, maybe not even.

The data column is literally unchanged.

Is it the fact most of the columns are varchar that I'm seeing no impact? Or am I not correctly interpreting what the sp_spaceused is measuring?

Upvotes: 1

Views: 471

Answers (1)

Dave.Gugg
Dave.Gugg

Reputation: 6781

It depends on the data types of the fields in the table. Fixed length data type, such as integer and char(), use the same amount of space regardless of what's in them (with a few exceptions such as sparse columns). Variable length columns such as varchar() only use the space they need. If you have fifty empty spaces being held in a varchar field and you replace that with a NULL, now you are saving space.

One other thing to consider (I'm sure there are more) is how many rows fit on a page. If there are four rows on a page and they are taking up the entire page, reducing each row by 10% isn't going to open up enough free space for another row to fit on the page. SQL Server does not split rows between pages (again with a few exceptions).

As was mentioned, try defragmenting all the indexes and see if that helps.

Upvotes: 3

Related Questions