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:

Table creation details

and see how it performs:

SELECT  id, SUBSTRING(value, 1, 50) AS value
FROM    [20090501_max].t_bigdata
WHERE   value LIKE 'lorem ipsum dolor sit%'

Table 't_bigdata'. Scan count 3, logical reads 55077, physical reads 95, read-ahead reads 31457, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

SQL Server Execution Times:
   CPU time = 531 ms,  elapsed time = 6854 ms. 

Since VARCHAR(MAX) cannot be indexed, and full table scan is very expensive on such a large table, the query runs for almost 7 seconds.

Fortunately, we can create a coarse filter for VARCHAR(MAX) by using a computed column.

Along the lines of the table creation script you can see:

CREATE SCHEMA [20090501_max]
CREATE TABLE t_bigdata (
id INT NOT NULL PRIMARY KEY,
value NVARCHAR(MAX),
value_index AS CAST(value AS NVARCHAR(450))
)
GO
CREATE INDEX IX_bigdata_value ON [20090501_max].t_bigdata(value_index)
GO

Here, value_index is a helper column. Its value is just a value truncated to NVARCHAR(450) (each NVARCHAR symbol takes 2 bytes, and 900 bytes is the maximum length of an index key in SQL Server).

This columpted column can be indexed as well, and an index is in fact created on this column, as you can see in the script above.

Now, let's try to use the index. To do this, we'll need to add one more predicate into the query:

SELECT  id, SUBSTRING(value, 1, 50) AS value
FROM    [20090501_max].t_bigdata
WHERE   value LIKE 'lorem ipsum dolor sit%'
AND value_index LIKE 'lorem ipsum dolor sit%'

What did we do here?

We added one more filtering condition into the query. This condition checks if the computed column is also LIKE the expression we are looking for.

The computed column is indexable and the index is created on it. That means that this extra condition, though logically redundant, can be used in an INDEX SEEK, efficiently filtering out those records that do not start with lorem ipsum dolor sit.

Let's see how it works:

Table 't_bigdata'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms. 

Same resultset, but so much faster.