Data type: Text vs VarChar (10000)

[Zirak]

I have a table that is used to archive communications.
One of the fields is ComText, I've used the "Text" data type for this field.
Now my question is if I use a varchar data type with the length of (10000) would it improve performance in any way (specialy for reporting purposes)?

 

[swampBoogie]

Using varchar is more efficient than using text. The maximum length for a varchar column is 8000.

 

[SQLBill]

I believe the maximum VARCHAR can be set to is 8000.

-SQLBill

 

[Zirak]

Why is Varchar more efficient and in what situations is it preferred over Text when you have large text data.

Thank you

 

[swampBoogie]

If you have a text column, the actual data is not stored in the table. It is only an adress so the server needs to do one or more additional read to get the contents of the column.

If your data is less than 8000 characters, use varchar.

 

[Zirak]

Thanks a lot

 

[SemperFiDownUnda]

background. The limit to the size of a field (char, varchar, etc) is do to the fact that the width of a row can not exceed the size of a datapage or more exactly that a row can not span datapages. In SQL 7 this was 4k in 2000 it is 8k. Image and Text column are excluded because they are not stored in the datapage with the rest of the row but elsewhere and a pointer, as swampBoogie points out, is stored. Because of this to read that row more data pages will have to loaded. Also any columns that are TEXT and IMAGE are NOT logged.