Trying to qualify shrinking varchar field (sql 2008 sp2)

User avatar
garbsmj
4StarLounger
Posts: 453
Joined: 04 Feb 2010, 03:40

Trying to qualify shrinking varchar field (sql 2008 sp2)

Post by garbsmj »

I'm going through someone else's programming and on one table, they allow for a varchar(500) for four separate fields (one which I doubt will ever have data but you know, hope springs eternal). The data can only be 64 characters long as it's coming from a system that has a limit. So I'm thinking of proposing shrinking the field to match the data and I'm unable to figure out how much this would really save. Or should I bother?


Peggy
When one cat leaves, another mysteriously shows up.

User avatar
Jezza
5StarLounger
Posts: 842
Joined: 24 Jan 2010, 06:35
Location: A Magic Forest in Deepest, Darkest, Kent

Re: Trying to qualify shrinking varchar field (sql 2008 sp2)

Post by Jezza »

Hi Peggy, long time no chat.

The varchar(500) can hold a maximun of 501 bytes of information assuming the column is holding single byte character set such as latin1. The rule of thumb is that the column field will hold the ((string length) + 1 byte)

If you are using fixed length Char(64) then each field is 64 bytes still assuming a single byte character set.

There are many benefits of using fixed char datatypes in so much that this can be indexed and therefore speed up data retrieval.

The only way to see how much space you are saving is to use the pseudo code :

sum(((string length) + 1 byte)) * count(*)

It all depends on the number of records you have and what you propose to do with the data in this field. I have known of a bank who had a data field that stored 1 or 0 (logical status) and they changed the field from varchar(1) to char(1) and saved over a 100 megabyte in database footprint but only because of the enormous amount of transactional data they had.

Ask yourself, do you need to do it?
Jerry
I’ll be more enthusiastic about encouraging thinking outside the box when there’s evidence of any thinking going on inside it

JoeP
BronzeLounger
Posts: 1565
Joined: 25 Jan 2010, 02:12

Re: Trying to qualify shrinking varchar field (sql 2008 sp2)

Post by JoeP »

I did not take from the original post that the data was always 64 characters long but that it could be a maximum of 64 characters. If the data is not fixed in length then you can't really tell if there would be any savings without examining all the data. From Microsoft SQL Server 2008 Step by Step, page 70:

"When data is stored in either a charor nchar data type, the amount of storage consumed equal the storage definition of the data type, regardless of the number of characters placed in the column. Any space that is not consumed be data is padded with spaces.

When data is stored in either an nvarchar or varchar data type, the amount of storage consumed is equal to the number of characters actually stored."

Joe
Joe

User avatar
Jezza
5StarLounger
Posts: 842
Joined: 24 Jan 2010, 06:35
Location: A Magic Forest in Deepest, Darkest, Kent

Re: Trying to qualify shrinking varchar field (sql 2008 sp2)

Post by Jezza »

Hi Joe

I read it as
...The data can only be 64 characters long...
and so assumed it was always going to be 64 bytes in length per record field as per your SQL 2008 quote so I think we agree
Jerry
I’ll be more enthusiastic about encouraging thinking outside the box when there’s evidence of any thinking going on inside it

User avatar
garbsmj
4StarLounger
Posts: 453
Joined: 04 Feb 2010, 03:40

Re: Trying to qualify shrinking varchar field (sql 2008 sp2)

Post by garbsmj »

Hi Jerry and Joe!

So I guess it's more of a cosmetic change then? I'd certainly hope that the other system's requirements don't change from the 64 characters because users are typing in any old thing and it's an ongoing issue.

So I'll just change it on my test system to an even 100 and leave it for next go-round of the continuing saga of "As The New Field Is Desperately Needed for My SQL Report". :innocent:
When one cat leaves, another mysteriously shows up.