March 26, 2015 at 3:42 am
Hello
Im using SQL Server 2008R2 + SP2 .... I have a view defined as :
CREATE view vwIndexedView
WITH SCHEMABINDING
AS
SELECTa.r_object_id AS p1
FROMdbo.dm_document_s AS a
LEFT OUTER JOIN (SELECT b.r_object_id FROM dbo.dmi_object_type AS b) AS b ON b.r_object_id = a.r_object_id
WHEREb.r_object_id is null
I'm trying to create a clustered index on the view using the following command :
CREATE UNIQUE CLUSTERED INDEX [idx_p1] ON [dbo].[vwIndexedView]
(
[p1] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
However its failing with the following error :
Msg 10109, Level 16, State 1, Line 1
Cannot create index on view "documentum_main_DBA.dbo.vwIndexedView" because it references derived table "b" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.
Can anyone show me how to rewrite the query?
Cheers
Tim
March 26, 2015 at 3:48 am
I don't think you can.
Removing the derived table is easily enough, but then you run into the "No outer joins." limitation. Convert to NOT IN/NOT EXISTS and you run into the "No subqueries" rule
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2015 at 11:27 am
You don't really need a separate indexed view if that's the full query. You could instead add a flag to the dm_document table of whether a matching row existing in the other table for the value in that row, then create a filtered index (if on Enterprise) or regular (if not) on the dm_document table.
You'd need triggers to properly maintain the flag, but those would be relatively trivial.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply