prevent clustered index insert operator on unqualified indexed view

Does anyone know a workaround for this? Essentially, the stored procedure forces an insert operator against the indexed view, even though the rows don't qualify. As a result, there is a cast error. However, for ad hocs, sql correctly eliminates the view from consideration.

Consider the following schema:

create table testdata (
    testid int identity(1,1) primary key
  , kind varchar(50)
  , data nvarchar(4000))
go
create view integer_testdata with schemabinding
as
select cast(a.data as int) data, a.kind, a.testid
  from dbo.testdata a
 where a.kind = 'integer'
go
create unique clustered index cl_intdata on integer_testdata(data)
go
create procedure insert_testdata
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data) values (@kind, @data)
end
go

These all work:

insert into testdata (kind, data) values ('integer', '1234');
insert into testdata (kind, data) values ('integer', 12345);
insert into testdata (kind, data) values ('noninteger', 'noninteger');
exec insert_testdata @kind = 'integer', @data = '123456';
exec insert_testdata @kind = 'integer', @data = 1234567;

This fails:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

A comparison of the "estimated execution plans":

insert into testdata (kind, data) values ('noninteger', 'noninteger'):

exec insert_testdata @kind = 'noninteger', @data = 'noninteger':

Thank you for providing a full script to recreate the problem.

I tested it with SQL Server 2014 Express.

When I add OPTION(RECOMPILE) it works:

ALTER procedure [dbo].[insert_testdata]
(
    @kind varchar(50)
  , @data nvarchar(4000)
)
as
begin
  insert into testdata (kind, data) 
  values (@kind, @data)
  OPTION(RECOMPILE);
end

When I run this in SSMS:

exec insert_testdata @kind = 'noninteger', @data = 'noninteger';

I get this message:

(1 row(s) affected)

and a row is added to the table.

What version of SQL Server are you using? I vaguely remember that in versions before 2008 this OPTION(RECOMPILE) behaved a bit differently.


I'm working with an existing data structure and need a fast lookup on
unique integer values stored in subset of nvarchar(4000)'s, a filter
on another column defines that subset of rows.

In this case it may be better to use filtered index instead of indexed view:

CREATE UNIQUE NONCLUSTERED INDEX [IX_DataFiltered] ON [dbo].[testdata]
(
    [data] ASC
)
WHERE ([kind]='integer')

Optimizer should use this index when the WHERE filter of the query matches exactly the WHERE clause of the index.

Yes, here index is on nvarchar column which may be not the best thing, especially if you join this table with an int column of another table, or try to filter values in this column using int values.

Another variant that comes to mind is persisted computed column that converts nvarchar to int. In essence it is very similar to your view, but the persisted nvarchar values that are converted into int are stored with the same table, not in a separate object.

CREATE TABLE [dbo].[testdata](
    [testid] [int] IDENTITY(1,1) NOT NULL,
    [kind] [varchar](50) NULL,
    [data] [nvarchar](4000) NULL,
    [int_data]  AS (case when [kind]='integer' then CONVERT([int],[data]) end) PERSISTED,
PRIMARY KEY CLUSTERED 
(
    [testid] ASC
))

CREATE UNIQUE NONCLUSTERED INDEX [IX_int_data_filtered] ON [dbo].[testdata]
(
    [int_data] ASC
)
WHERE ([kind]='integer')

With this set up I tried to use your original stored procedure to insert rows and it worked even without OPTION(RECOMPILE).

Actually, it seems that the main reason why the above persisted column works is that I use CASE. If I add CASE to the definition of your view, the stored procedure works without OPTION(RECOMPILE).

create view integer_testdata2 with schemabinding
as
select 
    case when a.kind='integer' then CONVERT(int, a.data) end as data
    , a.kind, a.testid
from dbo.testdata a
where a.kind = 'integer'
go

StackExchange Database Administrators Q#118666, answer score: 6