8

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'): enter image description here

exec insert_testdata @kind = 'noninteger', @data = 'noninteger': enter image description here

6
  • Any notable differences between the ad hoc and cached stored proc plan per chance? – Ali Razeghi Oct 20 '15 at 20:42
  • yes, when you execute ad hoc, you don't get any operators against the indexed view... I think sql is smart enough to see there is a filter on the view and eliminate it from consideration (this elimination does not happen in the proc) – cocogorilla Oct 20 '15 at 21:03
  • 4
    Not in a position to test but does adding option (recompile) help? – Martin Smith Oct 20 '15 at 22:31
  • 2
    Just out of curiosity, what problem are you trying to solve. This smells like an X-Y problem. – Hannah Vernon Oct 21 '15 at 0:25
  • 1
    @MaxVernon 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. – cocogorilla Oct 21 '15 at 21:31
6
+100

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
3
  • I'm not sure the filtered index will work well because the column width is 4000 (well above the limit of 900). I had not thought of using a query hint option recompile... I was applying recompile to the entire procedure. Your suggestion works for all my test cases! Thank you. – cocogorilla Oct 26 '15 at 17:55
  • 1
    Yes, filtered index on original column may be not very useful. I added another variant with persisted computed column. – Vladimir Baranov Oct 26 '15 at 22:01
  • I love the persisted computed column option... that strikes me as the correct solution – cocogorilla Oct 28 '15 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.