question

abid2010 avatar image
abid2010 asked

text vs varchar(max)

microsft says 'text type will not support in future' and varchar(max) is alternative for text. in varchar(max) data is stored in the logical page as possible. in text type data is stored in lob page. if you read all fields in query ( ex title,body) varchar(max) is faster. but i think if you want to read some fields (just title) text may be faster. when data stored in logical page, sql server must be read more logical pages . What do you think?
varchar
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
It doesn't really matter which is faster. Text is on the deprecation list. While I wouldn't bother removing it from existing databases at this time, I would absolutely recommend against creating it on any new databases or tables that you create. And no, it's not something I've directly tested, but on a guess, I'd say it would be a wash on performance or maybe edging towards VARCHAR(MAX) being faster. Even if you're just looking at that single column, you first have to identify the appropriate row. This is going to be done by reading from the clustered index, there's a few page reads. Now, if the MAX column is stored with the rest of the data right there on the page, no additional reads are necessary, MAX wins. But, if it has overflowed to additional storage, then the same amount of reads will be necessary there as there would be from TEXT, so, a tie. But, that's speculation. Testing would be the only way to be sure.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.