I was sent a question today that seems like it could be something that many people get confused about – why can’t an NTEXT column be specified in the INCLUDE list of a CREATE INDEX statement in SQL 2005?
(In one of my internals posts from last year there’s a long explanation of included columns – its basically a way to have true covering indexes to save IOs by not having to go back to the base table once an index row has been found).
The answer to the question is that columns of the legacy large-value data types (TEXT, NTEXT, and IMAGE) cannot be used in the INCLUDE list. You’ll need to change the schema to use one of the new large-value data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML).
The new types are stored in almost exactly the same way as the legacy types, but the internals of how they are processed by the server are different so they behave like regular data types (in that they work with most T-SQL intrinsic functions and can be used as included columns).
For a more detailed list of possibilities and restrictions on included columns, see the BOL topic Index with Included Columns (look for ‘included columns’ in the BOL Index).
