Solved

Size of varchar

Posted on 2004-09-21
10
1,030 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
 
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 69

Expert Comment

by:ScottPletcher
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 69

Expert Comment

by:ScottPletcher
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 250 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now