In my quest to love indexed views more, I’m always trying new things with them to solve problems.
Occasionally, I am pleasantly surprised by what can be accomplished with them. Occasionally.
Today was not an occasion. Let’s take an unfortunate look.
CREATE TABLE dbo.IndexedViewMe ( id int PRIMARY KEY CLUSTERED ); GO CREATE VIEW dbo.TheIndexedView WITH SCHEMABINDING AS SELECT ivm.id FROM dbo.IndexedViewMe AS ivm; GO CREATE UNIQUE CLUSTERED INDEX uqi ON dbo.TheIndexedView (id); INSERT dbo.IndexedViewMe ( id ) SELECT x.c FROM ( SELECT 1 UNION ALL SELECT 2 ) AS x(c);
This gives us a tiny little table and indexed view. If we try to do either of these things, it doesn’t go well:
CREATE INDEX i ON dbo.TheIndexedView (id) WHERE id = 2;
Msg 10610, Level 16, State 1, Line 40
Filtered index ‘i’ cannot be created on object ‘dbo.TheIndexedView’ because it is not a user table. Filtered indexes are only supported on tables.
If you are trying to create a filtered index on a view, consider creating an indexed view with the filter expression incorporated in the view definition.
CREATE STATISTICS s ON dbo.TheIndexedView (id) WHERE id = 2;
Msg 10623, Level 16, State 1, Line 47
Filtered statistics ‘s’ cannot be created on object ‘dbo.TheIndexedView’ because it is not a user table. Filtered statistics are only supported on user tables.
Sort of a bummer, that. And it strikes me that it’s an odd limitation — especially for the statistics — but what can you do?
Indexed views haven’t changed aside from bug fixes in forever and a day. I doubt there’ll be any real investment in enhancing them anytime soon.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.