SQL Server and MS BI With a Geek Touch

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(TESTFIELD,’   ‘,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.

There are times when you need to insert some special characters such as “newline”,”tab” etc as values into a field. Usually every one tries with ‘\n’ or ‘\t’, in vain. we can do this by using the ASCII value of the character. The steps are very simple. if you want to insert a special character find the ASCII value of that character like ‘ 9 for tab’, ‘ 12 for new line’ etc. you can find the lists of characters with its ASCII values at http://awesomesql.wordpress.com/2009/08/10/ascii-character-set-table. Once this is done, concatenate your string with the character of that particular ASCII value as

‘my string and’+ char(12)+’someting’

this will be

my string and

something

an examle is as shown below

INSERT INTO TABLES( FIELDS)

SELECT ‘hello’+char(9)+’:'+char(12)+’this is a new line’

this will be stored in the table as

hello    :

this is a new line

in the above example I inserted a tab after hello and a new line after ‘:’

you can similarly use

new line = char(12)

space = char(32)

horizontal tab = char(9)

carriage return = char(15)

vertical tab = char(13)

end of text = char(3)

for more look at the ASCII Table mentioned above.

ASCII character set and values in decimal,hexadecimal,oct

Special Characters

Prinatable characters