Home > ASCII, Insert, SQL Queries, SQL Server, Storage Space, T-SQL > Save storage disk space by replacing “space” with “horizontal tabs”

Save storage disk space by replacing “space” with “horizontal tabs”

This was a simple experiment that I performed to find, how much difference it makes by replacing some of the characters in a field with others. The basic requirement was to have a varchar / nvarchar field with huge data :). I created a sample table with a field of datatype  nvarchar(max). The table used no disk space

name                  rows    reserved    data    index_size    unused
TESTTABLE    0              0 KB          0 KB     0 KB               0 KB

Then inserted data into this field. Also didn’t forget to add lot of  “space” in the data. I inserted around 17 records in the table and then ran the stored proc to find the space used by the table.

the result was

name                 rows    reserved     data            index_size    unused
TESTTABLE     17         984 KB       968 KB 8 KB                  8 KB

I replaced the 3 consecutive spaces with a “horizontal tab” by using the query

UPDATE TESTTABLE
SET CHARS = REPLACE(CHARS,’   ‘,CHAR(9))

then i checked the space used by the table

name                 rows          reserved    data           index_size    unused
TESTTABLE      17             968 KB        696 KB 8 KB                 264 KB

you might have already noticed that, there is a huge differnce in the data size. The size was reduced to 696KB from 968KB. This was for a small table with only 17 records, imagine for a table with millions of records :) ,  it can make a huge difference. Theoratically speaking 3 charchters were replace with a single character with out affecting the way the data is displayed in my UI . This saved me 4 bytes for each replace ( 1 characters = 2 byte for nvarchar, therefore 3-1 = 2 characters removed for each replace ). This does not mean that you can replace all characters with someting else.

One of my record had  23345 characters, the replace reduced it to  14761 characters

The unused space can be regained by using some techniques like shrinking the database.

Now the question in your mind might be, does the same work for char / nchar datatypes.  I leave that to you to find out. May be,  you already have the answer :).

Please use the content at your own risk. In no event shall the
authors be liable for any claim, damages/issues that may be caused
by using the content.
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: