Posted by Erland Sommarskog on 10/01/20 11:42

ing42 (Inga.Korczowska@gmail.com) writes:
> Do the insert into aTable (also through aView).
>
> INSERT INTO dbo.aTable VALUES (1, 'a')
> INSERT INTO dbo.aView VALUES (2, 'b')
>
> Still do not have any problem. But when I need index on view
>
> CREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])
> GO
>
> I get following error while inserting record into aTable:
>
> -- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4
> -- Only text pointers are allowed in work tables, never text, ntext, or
> image columns. The query processor produced a query plan that required
> a text, ntext, or image column in a work table.
>
> Does anyone know what causes the error?

Did you notice the warning when you created the index:

Warning: The optimizer cannot use the index because the select list of
the view contains a non-aggregate expression.

So the index is not of much use. I guess you have hit a restriction
in SQL Server, which does not report as such in a nice way. When I
run your code in SQL 2005, I get:

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

Which is a more resolute message.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]