Software Engineer
asked on
TSQL: Creating a Clustered Index on a View
Hi:
A query that I composed and am currently running is doing so very slowly.
The query contains references to two views. I'm thinking about adding a clustered index to each view to improve performance and ultimately get the query to run faster.
But, if I add an index to a view, does it also add that index to the underlying tables of the view and cause issues for those tables?
Incidentally, I tried to add a clustered index to one of the views. But, SQL would not do it because the view is not schema bound. How do I make it schema bound? If I do this, will doing so negatively affect its underlying tables?
Are there other ways of speeding up the run of a query other than adding indexes and removing references to unnecessary tables, as I just did?
Thanks!
Software Engineer
A query that I composed and am currently running is doing so very slowly.
The query contains references to two views. I'm thinking about adding a clustered index to each view to improve performance and ultimately get the query to run faster.
But, if I add an index to a view, does it also add that index to the underlying tables of the view and cause issues for those tables?
Incidentally, I tried to add a clustered index to one of the views. But, SQL would not do it because the view is not schema bound. How do I make it schema bound? If I do this, will doing so negatively affect its underlying tables?
Are there other ways of speeding up the run of a query other than adding indexes and removing references to unnecessary tables, as I just did?
Thanks!
Software Engineer
ASKER
Are there other ways of speeding up the run of a query other than adding indexes and removing references to unnecessary tables, as I just did?
Not all views can be indexed. There are a lot of restriction to do that.
First is to create the view as schema bound:
One more thing: if a view is schemabound means that you cannot make schema changes (ALTER, DROP) on underlying table of the view. If you want to alter that table then you have to drop the view first (and index).
First is to create the view as schema bound:
CREATE VIEW YourView
WITH SCHEMABINDING
AS
SELECT ...
One more thing: if a view is schemabound means that you cannot make schema changes (ALTER, DROP) on underlying table of the view. If you want to alter that table then you have to drop the view first (and index).
ASKER
How do I alter the current views to be schema bound?
Script it as ALTER VIEW by SSMS then just extend the script by WITH SCHEMABINDING (as I show before).
Caution should be taken before altering vendor provided views.
Use the create view to see ...
Use the create view to see ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
See if the following doc helps you
https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15
The existing views do not includethe schemaound option.
Often vendors provide views for their own purposes.
You like many, tried to use the existing options to achieve what you need.
Look at the create view option to see on what it relies in the event you need to get to a subset of the data...