SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexing in views


Indexing in views

Author
Message
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7598 Visits: 2776
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/
Lokesh Vij
Lokesh Vij
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4402 Visits: 1599
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 :-D

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7598 Visits: 2776
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 :-D

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/
Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 1549
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 !!!
www.GrowWithSql.com
Anipaul
Anipaul
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11829 Visits: 1407
Nice question....



Bangla
Bangla
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2029 Visits: 180
Good question.....
Hugo Kornelis
Hugo Kornelis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24893 Visits: 12580
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9298 Visits: 3439
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.
:-)
sqldoubleg
sqldoubleg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1703 Visits: 1363
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 :-D
Nick Doyle
Nick Doyle
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 210
Good question. Thanks Kapil!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search