EXPLAIN EXTENDED

SQL Server 2005 introduced new datatype: VARCHAR(MAX).

This is a replacement of old TEXT that can do anything that TEXT could, and something that TEXT could not.

Something includes possibility of being queried and compared like a plain VARCHAR. A really nice feature.

SELECT  *
FROM    [20090501_max].t_bigdata
WHERE   value LIKE 'lorem ipsum dolor sit%'

Unfortunately, VARCHAR(MAX) is still far from a plain VARCHAR. For instance, it cannot serve as an index key. It can be a part of an index (the column is included to the index leaves to avoid table lookups when using the index), but not the key of an index (the index leaves are not sorted on this column).

This means that the queries using LIKE predicate on this column will be slow.

Let's create a sample table:
Read the rest of this entry »