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
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9151 Visits: 2778
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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5178 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
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9151 Visits: 2778
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
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3068 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 (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

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



Bangla
Bangla
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 180
Good question.....
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29821 Visits: 12835
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10954 Visits: 3476
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.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1891 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
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 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