2007/10/4, Davide Alberani <davide.alberani@...>:
> Does MSSQL Server have an unlimited (or reasonably big) TEXT type?
> I've never used it directly, I'll have to ask for the details
Done that. Steven Ovits (who I wish to thank), says:
SQL Server 6.5 (and 7.0) supported text columns, but they were
"out-of-line", meaning they were stored in a separate data area, not on the
main pages. You did a normal select using a pointer type, then you passed
the pointer to another other api to get the actual data. It was a pain, and
not portable at all, although... This is exactly how binary data was handled
by a lot of databases. Apparently, this was "fixed" in the 2000 release, and
the data is now stored inline (or it's an option or something like that).
The tradeoff is you have to scan several pages for each row when you do a
query that's not based on an index--when you need to actually scan the data
itself. Since noone really puts an index on large text columns, and it's not
needed in every query, it makes sense to store those large data objects
separately. I have no idea which databases do it which way for varchar.
Also, v6.5 was limited to varchar(255) and in 7.0 it was maybe
varchar(4000). Here's the same info online.
I hope this helps.
Davide Alberani <davide.alberani@...> [PGP KeyID: 0x465BFD47]