Indexing in views
-
Comments posted to this topic are about the item Indexing in views
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Interesting question Kapil!
Even if someone is not aware that the index gets dropped (in Scenario-2); Scenario-3 was easy to attempt, because non-clustered index created in this scenario has the same name as that of Clustered index created in scenario-2 😀
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (7/23/2013)
Interesting question Kapil!Even if someone is not aware that the index gets dropped (in Scenario-2); Scenario-3 was easy to attempt, because non-clustered index created in this scenario has the same name as that of Clustered index created in scenario-2 😀
yes, I see that thing... actually question that I posted previously contains 6 scenarios but my question was not getting submitted due to excess of scenario, so while reducing that scenarios number I missed to change the name of index in Scenario 3 else this question can be more interesting for all..
I apologize for that........
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Good question kapil.
But if you execute this T-Sql in single batch, then it will give an error like "Procedure vw_customer, Line 9 Incorrect syntax near the keyword 'Create'."
In question, you don't mention that how to execute T-sql, in single batch or multiple batches.
you should place a "GO" statement between T-Sql.
Actually In past, Answer of some questions were depended on how to execute it, in single or multiple batch.
I think you understand my point.
Please find below correct question with "Go"
CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1001,1) NOT NULL,
[CustomerName] [varchar](50) NOT NULL,
[CEO] [varchar](40) NULL,
[Phone] [varchar](20) NOT NULL
PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)
)
GO
-- Create view
Create VIEW vw_customer
WITH SCHEMABINDING
AS
SELECT CustomerID, CustomerName
from dbo.Customer
;
GO
--Scenario 1
Create index IX_CustomerID
ON vw_customer (CustomerID);
GO
--Scenario 2
Create unique clustered index IX_CustomerID
ON vw_customer (CustomerID);
GO
--Now alter the view after Scenario 2
Alter VIEW vw_customer
WITH SCHEMABINDING AS
SELECT CustomerID, CustomerName, getdate() CurrentDate
from dbo.Customer
GO
Alter VIEW vw_customer
WITH SCHEMABINDING
AS
SELECT CustomerID, CustomerName
from dbo.Customer
;
GO
--Scenario 3
Create index IX_CustomerID
ON vw_customer (CustomerID);
GO
-- cleanup
DROP VIEW vw_customer;
DROP TABLE customer;
anyway thanks for great question. Keep it up 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Nice question....
-
Good question.....
-
Interesting question!
I would have expected the ALTER VIEW to fail because of the presence of an index - if you had included that as an answer option, I would have picked the wrong answer.
Now I picked the right answer, but for the wrong reason (I thought the last CREATE INDEX would fail because of the duplicate index name).
Since I was unaware of the effect of ALTER VIEW on an indexed view, I decided to check if it's documented. Turns out it is, in the ALTER VIEW section of Books Online:
"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Before ALTERing an INDEXED VIEW is better to save its list of indexes, because they'll be dropped.
It would be better putting a comment in the view with the command to re-create all indexes or just a WARN.
🙂
-
Nice question, and not being sure that the index was removed by the ALTER VIEW statement, having the index with the same name as the clustered index was really helpful 😀
-
Good question. Thanks Kapil!
-
Nice interesting question.
I guess that "after scenario 3" in the explanation is a typo, ideally it should read "after scenario 2 as a result of the alter view operations", and at the very least "3" should be "2" - something that happened "after scenario 3" couldn't make scenario 3 fail.
Tom
-
Hugo Kornelis (7/24/2013)
Interesting question!Now I picked the right answer, but for the wrong reason (I thought the last CREATE INDEX would fail because of the duplicate index name).
Glad I'm not the only one that thought this. Learned something new today.
Thanks for the question!
Everything is awesome! -
Hugo Kornelis (7/24/2013)
Since I was unaware of the effect of ALTER VIEW on an indexed view, I decided to check if it's documented. Turns out it is, in the ALTER VIEW section of Books Online:"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
It would be nice if it would send a caution like when renaming an object. Something to let us know that it has just wiped out all the indexes on the view.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Great question. It reinforces some concepts I forgot 🙂
Being a business intelligence guy, I try to avoid indexed views unless the client is so large, I have no choice but to use them in the following situations (because the data volumes are even too big for Analysis Services MOLAP).
1. ROLAP (multidimensional model) with indexed views
2. Direct Query Mode (tabular model)
That said, there are probably other potential uses for indexed views (BI or non-BI related) than I can think of at this moment in time.
-
Sean Lange (7/24/2013)
Hugo Kornelis (7/24/2013)
Since I was unaware of the effect of ALTER VIEW on an indexed view, I decided to check if it's documented. Turns out it is, in the ALTER VIEW section of Books Online:"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
It would be nice if it would send a caution like when renaming an object. Something to let us know that it has just wiped out all the indexes on the view.
This is a very good point, and likely deserves some attention in the future.
Thanks for the question. This was a behavior with which I also was not familiar.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply