If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. It contains text, ntext, image, FILESTREAM or xml columns” in SQL Server, it appears that you’re trying to create an index on a view that uses an invalid data type.

Not all data types are supported when indexing views.

To fix this issue, change the unsupported data type to another type, or remove the column altogether.

Example of Error

Suppose we create the following table and schema-bound view that references that table:

-- Create a table
CREATE TABLE dbo.t1
  (
    c1 int,
    c2 text
  );
GO

-- Create a schema-bound view
CREATE VIEW v1 WITH SCHEMABINDING AS 
    SELECT c1, c2 FROM dbo.t1;

And now let’s try to add an index to the view:

CREATE UNIQUE CLUSTERED INDEX IDX_v1 ON v1 (c1);

Output:

Msg 1942, Level 16, State 1, Line 1
Cannot create index on view 'demo.dbo.v1'. It contains text, ntext, image, FILESTREAM or xml columns.

The error message provides us a hint; we can’t use those data types in the view/base table. In my case, the c2 column uses text, which isn’t supported.

Indexed views don’t support the following data types:

• text
• ntext
• image
• FILESTREAM
• xml
• float in the clustered key (this type is supported, but it can’t be included in the clustered key)
• Sparse column sets.

Solution

The solution may depend on the actual data type that’s causing the error. Some can be migrated to other types. Others can’t.

The text, ntext, and image types can be migrated to varchar(max), nvarchar(max), and varbinary(max) respectively. The other types don’t have an equivalent. The float type is allowed in indexed views, but only if it’s not in the clustered key.

So the following solution will work when the offending data type is text, ntext, or image.

In my case, I had a text column, and so here’s how I can fix the issue.

Remove SCHEMABINDING from the view:

-- Remove schemabinding from view
ALTER VIEW v1 AS 
    SELECT c1, c2 FROM dbo.t1;

We can’t change the definition of the base table if there’s a schema-bound object referencing it. So that’s why I removed WITH SCHEMABINDING from the view.

Now we can change the definition of the column to a supported data type:

ALTER TABLE dbo.t1
ALTER COLUMN c2 varchar(max);

And now we can put WITH SCHEMABINDING back on the view:

ALTER VIEW v1 WITH SCHEMABINDING AS 
    SELECT c1, c2 FROM dbo.t1;

And now we can go ahead and create the index that led to the initial error:

CREATE UNIQUE CLUSTERED INDEX IDX_v1 ON v1 (c1);

Output:

Commands completed successfully.

Done.

If your view uses one of the unsupported data types that doesn’t have an equivalent supported type, then I guess you’ll need to either remove those columns from the view, or review whether or not the view should be indexed at all.