Why does TEXT take more storage than VARCHAR?

[Stedo]

hi,

When designing databases I normally use VARCHAR for short fields and TEXT for very large text fields. Can anyone explain to me at a detailed level why VARCHAR is more effective that TEXT?

Thanks
Steve

 

[SQLDenis]

Text is not stored in the table but separate that's why you can store more than the maximum row size (8060 bytes) in a text field
There are certain things that you can do with a varchar field but not with a text field like using ORDER BY on a text field

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[DotNetGnat]

My commentealing with text fields in the SQL Server is a pain.

But sometimes we have no other alternatives and we have to use them...

we have to use different functions to deal with text fields...for example let me give you a simple example...

to update a varchar field we can simply say...

Update mytable set myfield='whatever'

here myfield is of varchar type....if lets say it is of type text...then the above query will fail...we need to use a binary pointer variable and the writetext method to update the field instead of a simple update statement...

-DNG

 

[gmmastros]

Text data is not stored in the same record as the rest of your data. A 16 byte pointer is stored instead. This pointer points to the location where the text data is stored.

SQL Server has a maximum of 8060 bytes stored in a row. If you try to store more info that this, the data will get truncated.

Up until this point, in my reply, you may be thinking, "Sounds like I should use Text". Let me be clear about this, Text datatype should be used as a last resort.

Many of the string manipulation functions do not work with the Text data type. Many of these string manipulation functions come in handy.

I'll give you a perfect example. My online friend, DotNetGnat recently had a problem with data stored in a text field. I think he was storing resumes, which could easily exceeed the 8000 character limit of a varchar, so he correctly decided to store the data in a text field. He later discovered a problem with the data. There were some characters in the data that were causing display problems (apparently Human Resources couldn't view it properly). He wanted to replace the bad characters with spaces. It proved to be very difficult. If the data was in a varchar field, he could simply have done a replace. The whole project would have taken him 5 minutes instead of days.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetGnat]

oh yeah buddy... George and Vongrunt really know the pain i went through dealing with the text fields...

BUT having these two great experts to spare i am not afraid to create more text fields

-DNG