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
Trying to qualify shrinking varchar field (sql 2008 sp2)
-
- 4StarLounger
- Posts: 453
- Joined: 04 Feb 2010, 03:40
Trying to qualify shrinking varchar field (sql 2008 sp2)
When one cat leaves, another mysteriously shows up.
-
- 5StarLounger
- Posts: 847
- Joined: 24 Jan 2010, 06:35
- Location: A Magic Forest in Deepest, Darkest, Kent
Re: Trying to qualify shrinking varchar field (sql 2008 sp2)
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?
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
I’ll be more enthusiastic about encouraging thinking outside the box when there’s evidence of any thinking going on inside it
-
- SilverLounger
- Posts: 2143
- Joined: 25 Jan 2010, 02:12
Re: Trying to qualify shrinking varchar field (sql 2008 sp2)
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
"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
-
- 5StarLounger
- Posts: 847
- Joined: 24 Jan 2010, 06:35
- Location: A Magic Forest in Deepest, Darkest, Kent
Re: Trying to qualify shrinking varchar field (sql 2008 sp2)
Hi Joe
I read it as
I read it as
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...The data can only be 64 characters long...
Jerry
I’ll be more enthusiastic about encouraging thinking outside the box when there’s evidence of any thinking going on inside it
I’ll be more enthusiastic about encouraging thinking outside the box when there’s evidence of any thinking going on inside it
-
- 4StarLounger
- Posts: 453
- Joined: 04 Feb 2010, 03:40
Re: Trying to qualify shrinking varchar field (sql 2008 sp2)
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".
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".
When one cat leaves, another mysteriously shows up.