SQL Server Database Engine What happens when I extend a value in a VARCHAR field?
Question:

Hi folks,
 
Just a quick question around the performance implications when using the VARCHAR data type. If I have a field type of VARCHAR(50) and on the initial insert I populate this field with 10 chars. Now I run an update statement at some time in the future that replaces the 10 chars with 40 chars. Between when the insert and update run, a number of other inserts could have run inserting rows after the initial insert.
 
Based on this scenario, does the SQL Server storage engine need to "move" the data row to another location in the page or to a new page to find the required additional space? My understanding is that VARCHAR only uses as much space as what is required for the initial insert i.e. 10 chars. If more rows are added, and then this field is updated to 40 chars, where does the storage engine find the additional space?
 
As an aside, if a field is nullable and the value is null on an initial insert and then set to a valid value on a subsequent update, is there the same issue re finding additional space at the time of the update.
 
Thanks for your help!

Answer1:

For fast tables you need to use CHAR instead of VARCHAR.  You can update in place that way, also the engine can operate faster with predictive reads.
 
Also dynamic tables should be reindexed periodically (every night, every other night...) wigh FILLFACTOR 70.  This leaves room for new inserts.
 
 
 
 
 



Kalman Toth, SQL 2005 Business Intelligence Class NYC APR 21-24: www.SQLUSA.com