Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jusvistin
Yak Posting Veteran
81 Posts |
 Posted - 2003-08-09 : 06:15:36
|
| I understand VarChar will probably use less disk space, but is there a significant difference in read/write/processing speed between the two data types ?Thanks,Kevin |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-09 : 06:20:53
|
| If the field is always full then char will use less disk space.The server has to do a little more work to get a varchar.see http://www.nigelrivett.net/PageStructure.htmlThe big difference comes if you update a varchar field to a larger size and it causes a page split. This will slow things down and use up more space due to fragmentataion.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 07:03:18
|
| Nigel,I'm at a disadvantage here because I'm not thoroughly versed on how SQL Server utilizies storage space.Let's say I've got a page that's almost full, and I simply add some data to a couple of columns (not changing the varchar field length), increasing their actual content length from 20 bytes to let's say 500 bytes.Could this also generate a page split ? Thanks,Kevin |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-09 : 07:13:46
|
| Yes. The variable length data is held at the end of the record and concatenated. It is referenced via the col offset array which gives the end location of each field in order. The next row will start on the page after this one (see fill factor though). If you increase size of the variable length data (and there is no room on the page) then it will cause a page split which will slow down the update, require updates to the clustered index pointers and use up space.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 07:19:14
|
| Well, if the way a record is stored in the page is not directly dependent upon the maximum declared varchar length, there doesn't seem to be much I can do to eliminate page splits when adding data, is there ?In that case, should I really worry about it ?Am I missing something here ?Kevin |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-09 : 07:27:03
|
| You can minimise page splits by not increasing the size of variable length data. If you have a field which is empty when inserted but always gets updated to 100 chars later then it may be better to insert it with 100 spaces.You may want to put volatile data into another table with the PK so that the base data is fixed and you only get splitting on the other table.There are a lot of changes you may want to make to your design due to the way sql server manipulates data - depends on how efficient you need things to be and how much time you are willing to spend.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-09 : 07:34:03
|
quote:
Originally posted by nr
You can minimise page splits by not increasing the size of variable length data. If you have a field which is empty when inserted but always gets updated to 100 chars later then it may be better to insert it with 100 spaces.You may want to put volatile data into another table with the PK so that the base data is fixed and you only get splitting on the other table.
Hey, nice tips - I'll definitely keep those in mind.Interesting - all the things you need to keep in mind that most users will never ever think about.KevinThanks,Kevin |
|
|
|
|
|
|
|
|
VarChar or Char - Which is better ?