SQL Server Performance Forum – Threads Archive

Creating Indexes on Views

Hi all,
I’m using a View in the stored procedure which is asked to be optimized.
So I looked at creating an index on that view
But the view contains derived tables
like: SELECT Column1,Column2
FROM tableName1
INNER JOIN(SELECT columnName FROM SomeOtherTable) as tableName2) How can I get rid of these derived tables to make it posible to create indexes on the view for reference here are the SQL Code for View on which I want indexes to be created CREATE VIEW dbo.vwSignDetail_SignEvents_SignStatus
WITH SCHEMABINDING
AS
SELECTdbo.tblSignDetail.Department, dbo.tblSignDetail.heading, dbo.tblSignDetail.heading2, dbo.tblSignDetail.subheading, dbo.tblSignDetail.subheading2,
dbo.tblSignDetail.comment, dbo.tblSignDetail.sp_head, dbo.tblSignDetail.cprice, dbo.tblSignDetail.sprice, dbo.tblSignDetail.masternote,
StatusEventDivSign.SignNumber,StatusEventDivSign.statuscode,StatusEventDivSign.eventcode,StatusEventDivSign.DivID,StatusEventDivSign.DateUp,StatusEventDivSign.DateDown,StatusEventDivSign.EventID
FROM dbo.tblSignDetail INNER JOIN
(SELECTDateUp, DateDown, EventID, DivID, EventCode,
rowID, flgDiv,
StatusCode, SignNumber
FROM dbo.tblSignStatus INNER JOIN
(SELECT dbo.tblEvents.DateUp, dbo.tblEvents.DateDown, dbo.tblEvents.EventID, dbo.tblEvents.DivID, dbo.tblEvents.EventCode,
dbo.tblDivSigns.rowID, dbo.tblEvents.flgDiv
FROM dbo.tblEvents
INNER JOIN dbo.tblDivSigns ON dbo.tblDivSigns.EventID = dbo.tblEvents.EventID
WHERE dbo.tblDivSigns.SignTypeID = 1) EventDivSign ON dbo.tblSignStatus.DivRowID = EventDivSign.rowID
WHERE dbo.tblSignStatus.StatusCode = 10) StatusEventDivSign ON dbo.tblSignDetail.SignNumber = StatusEventDivSign.SignNumber
GO

---

CREATE UNIQUE CLUSTERED INDEX [ixvw_RespMasterHis] ON [dbo].[vwSignDetail_SignEvents_SignStatus
]([Department) ON [PRIMARY] —————————————-
http://spaces.msn.com/members/dineshasanka

---

Well my problem is not with the sysntax of the Create index …
Since I have a derived table in my SQL for the view I can’t create a index on that view
so what I want to know is whether there is any way to avoid having the derived table and , and create the index on the View

---

View definition cannot contain a derived table andhttp://www.sqlteam.com/item.asp?ItemID=6692 link for your information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Well I too get to know that I can’t have a derived table in the query if i’m to create a index on the View…
So what are my options to get rid of the derived table of the view
that’s my problem

---

Atleast in SQL 2000 there is no way where you can create indexed view with derived tables. It should only refer base tables. However looking at your query I think you can eliminate derived tables and simply make joins. Ex – in the innermost block you are joining tblEvents and tblDivSigns with matching event id and tblDivSigns.SignTypeID = 1 so start with a new query with this join and then join with tblSignStatus and use the condition dbo.tblSignStatus.DivRowID = tblDivSigns.rowID ( rather than using the dervied table name ) and so on. The idea is to start with the inner most block and keep joining with the corresponding outer block.
First build the query in this way ( eliminating derived tables ) and check for results before converting into indexed view.

---

Thanks everyone including indshri
I got it giong with the same way u have given the answer…. removing derived tables with simple joins….(I was bit afraid of changing the SQL because it was done by some one else)
Thanks again….

---

]]>