Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Size of varchar

Posted on 2004-09-21
10
Medium Priority
?
1,043 Views
Last Modified: 2012-08-14
I'm currently auditing my tabledefinitions to try to enhance the responsivness of the server in terms of memoryconsumption and IO.

In that respect I have the following question.

Taken the same dataset for a character field who's possible values are evenly distributed in length between 1 and 10 but that could contain larger lengths as well.

What is the difference in impact on the server (and why) between defining this field as

varchar(20)
varchar(100)

where the second definition buys me more breathing space if a larger value has to be inserted in that field.

to put it extreem a varchar(8000) definition would bring me even more breathingspace... (or - not to exceed the possible 8000 bytes/page limit - varchar(x) where all varchar fields have a size x=(8000-size of nonvarcharfields) / nr of varcharfields

0
Comment
Question by:esger
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Expert Comment

by:Sleepyhead_NO
ID: 12111587
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7msw.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_7tpu.asp

Fields should be as small as possible. From a performance view SQL server has less to read, which means quicker getting the data and quicker sorts. Naturally you want to keep them as short as possible, but also have enough space for what you shall store.

If you are asking whether it's worth it to change varchar(5000) to varchar(1000) just because of performance even though the extra space is needed, then I would say no.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12111641
Hi esger,

mostly agree with Sleepyhead_NO, but you don't want to have to amend the db schema to accomaodate larger data at a later time best to plan for it beforehand.

I believe that the defined size of a varchar is irrelevant (once a table contains a var* column every row is defined as variable length), the space reserved in a record is the same, this is because the record will have a marker declaring that the field is a varchar and then the contents of the field are placed at the end of the record.  As such you could redefine every varchar as VARCHAR(8000) and no more disk space would be consumed since you have added no data (you are of course still limited to 8000 bytes per record in total).
0
 
LVL 1

Author Comment

by:esger
ID: 12111705
@mcmonap: quoto:once a table contains a var* column every row is defined as variable length

kan you point to an article on this?
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 3

Expert Comment

by:Sleepyhead_NO
ID: 12111886
http://www.sql-server-performance.com/datatypes.asp

"Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data. In addition, if any sorts need to be performed on the column, the narrower the column, the faster the sort will be."

When using var* it is true that it only saves the length of the string. Unlike char which always saves the length specified.
According to: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_7tpu.asp
"Storage size is the actual length in bytes of the data entered, not n bytes"
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12112074
Hi esger,

'fraid not, but I have it documented in course notes from "Developing High Performance  SQL Server Databases" with LearningTree.  I think I may be incorrect above in that if the maximum row size is less than 4060 you can fit more than one record per database page (and this would increase as the row size gets smaller) since SQL reads chunks in 8k pages you would be pulling back 2 or more records per read as opposed to a single one which is more efficient.

I think I am getting a bit slow typing, this is pretty much what Sleepyhead_NO says above.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12112185
>> @mcmonap: quoto:once a table contains a var* column every row is defined as variable length

kan you point to an article on this? <<

No, but you don't really need one.  Deductive reasoning tells you that if one column of a row is variable length, the entire row *must* be variable length also, since every row contains a column(s) whose length could vary.  That is, say the other columns in the row total up to a length of 50, and the final column is:

colLast VARCHAR(100)

Since there could be anything from zero to 100 bytes, the final *data* length of the row will be somewhere between 50 and 150 bytes, and this will be true for every row.
0
 
LVL 3

Expert Comment

by:Sleepyhead_NO
ID: 12113712
" @mcmonap: quoto:once a table contains a var* column every row is defined as variable length
kan you point to an article on this?"

According to: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_7tpu.asp
"Storage size is the actual length in bytes of the data entered, not n bytes"

So if you have varchar(4000) but enter 10 char's, it will save 10 characters not 4000. If you used char(4000) it would save 4000 characters no matter how many you enter.
0
 
LVL 1

Author Comment

by:esger
ID: 12113838
I know the 'physical' differnce between char an varchar

the thing is wether there is a SIGNIFICANT differnce in choosing varchar(20) over varchar(100) (or even bigger) in terms of memory and IO

As far as I can see there is none and thus for varchar the 'Always specify the narrowest columns you can'-rule doesn't apply.

UNLESS sql server will somehow convert a varchar to char when using it internally in its sort/search/key code/implementation and this has to use the actual varchar size for that, then it becomes a significant difference between 20 and 100 (and up...)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12113881
I think for sorts SQL does have to create a buffer/sort key of the maximum possible length, but don't know if it has to pad every column to that full length or only to take it into account when calc. total key size, etc..
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 1000 total points
ID: 12115439
The physical read time is also, if you have a record that has a maximum 900 bytes size you should get 4 records per data page, in this instance you would retrieve four records as opposed to just one if the maximum record length was over 4031.  The 'narrowest column' does apply in this way, accesing the disk is most likely going to be the slowest part of getting data in SQL server.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question