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?
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.
15 People are following this question.
