Indexed View with table relation

Hi all

I am trying to create a Index on a View.
The View selects from several tables and these table
have of corse relations to each other.

Now I get the error:
"Cannot create %S_MSG on view "%.*ls" because it references derived table 
"%.*ls" (defined by SELECT statement in FROM clause). Consider removing the 
reference to the derived table or not indexing the view."

Well, it this message true, is it not possible to create a index
on a view when the underlying tables have relations ??

Thanks for any comment.

Best regards
Frank Uray
0
Utf
1/14/2010 3:01:02 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
1705 Views

Similar Articles

[PageSpeed] 16

You can use joins in indexed views but not derived tables (or subqueries). Try to rewrite the view to avoid using 
derived tables.

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/14/2010 3:29:37 PM
As Plamen said, the problem is that your view has a derived table in it, not 
that you are using tables which have foreign key relations defined on them. 
A derived table is a SELECT statement used in the FROM clause.  For example, 
if you have

SELECT <column list>
FROM Customers c
INNER JOIN (SELECT <column list>
  FROM Orders ,,,) AS x ON c.CustomerID = x.CustomerID

then the
(SELECT <column list>
  FROM Orders ,,,) AS x
is called a derived table.

Indexed Views can be very useful, but in order to create one, your view must 
follow some rules.  There are a fairly large number of these rules 
(primarily things your view is not allowed to have if you want to make it an 
indexed view).  You can find these rules in BOL or at
http://msdn.microsoft.com/en-us/library/ms191432%28SQL.90%29.aspx (for SQL 
2005)
http://msdn.microsoft.com/en-us/library/ms191432.aspx (for SQL 2008)

Tom

"Frank Uray" <FrankUray@discussions.microsoft.com> wrote in message 
news:066ED936-D3BC-4C7C-B900-38F44EF73AEA@microsoft.com...
> Hi all
>
> I am trying to create a Index on a View.
> The View selects from several tables and these table
> have of corse relations to each other.
>
> Now I get the error:
> "Cannot create %S_MSG on view "%.*ls" because it references derived table
> "%.*ls" (defined by SELECT statement in FROM clause). Consider removing 
> the
> reference to the derived table or not indexing the view."
>
> Well, it this message true, is it not possible to create a index
> on a view when the underlying tables have relations ??
>
> Thanks for any comment.
>
> Best regards
> Frank Uray 

0
Tom
1/14/2010 4:17:11 PM
Hi Tom

Thanks a lot for your answer, this help !

Best regards
Frank Uray

"Tom Cooper" wrote:

> As Plamen said, the problem is that your view has a derived table in it, not 
> that you are using tables which have foreign key relations defined on them. 
> A derived table is a SELECT statement used in the FROM clause.  For example, 
> if you have
> 
> SELECT <column list>
> FROM Customers c
> INNER JOIN (SELECT <column list>
>   FROM Orders ,,,) AS x ON c.CustomerID = x.CustomerID
> 
> then the
> (SELECT <column list>
>   FROM Orders ,,,) AS x
> is called a derived table.
> 
> Indexed Views can be very useful, but in order to create one, your view must 
> follow some rules.  There are a fairly large number of these rules 
> (primarily things your view is not allowed to have if you want to make it an 
> indexed view).  You can find these rules in BOL or at
> http://msdn.microsoft.com/en-us/library/ms191432%28SQL.90%29.aspx (for SQL 
> 2005)
> http://msdn.microsoft.com/en-us/library/ms191432.aspx (for SQL 2008)
> 
> Tom
> 
> "Frank Uray" <FrankUray@discussions.microsoft.com> wrote in message 
> news:066ED936-D3BC-4C7C-B900-38F44EF73AEA@microsoft.com...
> > Hi all
> >
> > I am trying to create a Index on a View.
> > The View selects from several tables and these table
> > have of corse relations to each other.
> >
> > Now I get the error:
> > "Cannot create %S_MSG on view "%.*ls" because it references derived table
> > "%.*ls" (defined by SELECT statement in FROM clause). Consider removing 
> > the
> > reference to the derived table or not indexing the view."
> >
> > Well, it this message true, is it not possible to create a index
> > on a view when the underlying tables have relations ??
> >
> > Thanks for any comment.
> >
> > Best regards
> > Frank Uray 
> 
> .
> 
0
Utf
1/14/2010 4:57:01 PM
Frank Uray wrote:
> 
> Hi all
> 
> I am trying to create a Index on a View.
> The View selects from several tables and these table
> have of corse relations to each other.
> 
> Now I get the error:
> "Cannot create %S_MSG on view "%.*ls" because it references derived table
> "%.*ls" (defined by SELECT statement in FROM clause). Consider removing the
> reference to the derived table or not indexing the view."
> 
> Well, it this message true, is it not possible to create a index
> on a view when the underlying tables have relations ??
> 
> Thanks for any comment.
> 
> Best regards
> Frank Uray

Frank,

Indexed views can be useful, but in my experience that is the exception,
not the rule.

Performance tuning starts with a good data model, and proper indexes on
the base tables. Usually, that is sufficient for good performance. IMO,
considering an indexed view requires a good understanding of the
consequences (in locking, and therefore concurrency). If you are using
derived tables in the view you would like to index, then you are
probably not ready for this yet, and you should consider tuning the base
tables first.

Good luck,
Gert-Jan
0
Gert
1/14/2010 9:21:15 PM
Reply:

Similar Artilces:

Essential Account view/duplicate accounts??
Just upgraded to Money 2005 (from 04) and I now have four credit card accounts instead of two. After calling MS tech support I was told that the reason I have four accounts instead of two is; two have the new Essential Account Register view and two have the Advanced Account Register view. This is dumb. Why does it show you the different views as duplicate accounts? It makes it look like you have more accounts then you really do. It even calls them a different name of the original accounts... Anyone experience this strange function??? Very disappointed as this feature doesn't...

How can I view e-mails before the attachment downloads?
When I receive a new e-mail with an attachment, it takes forever to open that e-mail. Can anyone please advise? It's frustrating to wait 5min. for one e-mail just to open. Thank you! "fRuStRaTeD" <fRuStRaTeD@discussions.microsoft.com> wrote in message news:9B7FCBB8-6B2E-4813-98C5-2BFEDB5CC17A@microsoft.com... > When I receive a new e-mail with an attachment, it takes forever to open > that > e-mail. Can anyone please advise? It's frustrating to wait 5min. for one > e-mail just to open. Attachments aren't some separate entity distin...

How to create a table with numbers from 1 to 600?
Hi! I need to create a table with numbers ranging from 1 to 600. It could be simpler. Just automatically filling cells with these numbers in increasing order. I'm trying with excel, which sould be the most appropriate software to do that, but I'm very negated with it. Could you please help me? Thanks a lot, ciao! If the list of number is all in one column, try something like this: Enter a 1 in cell A1 Select Cell A1 From the Excel main menu: <edit><fill><series> Series in: Column Stop Value: 600 Click the [OK] button Does that help? *********** Regards, Ron XL2...

To index or not to index
I have a table of stuff stored in a repository and an attached table of inventory dates, linked one-to-many by an Autonumber ID field. I regularly need to find the oldest or newest inventory dates (or all, in order by date) for each item record in the inventory table, which is normally an automatic case for indexing. But this stuff is not inventoried very often, so far, only two out of over 80,000 records have three records in the inventory table, all others have zero, one or two inventory records. This is NOT going to change. It will likely be decades before there are as many as...

How do I show email details i.e. From and To in Activities view
The Account and Contact records have the Activities and History details on the left, this uses the Activity pointer info which shows email, letters, faxes etc. This is fine but they would really like to see details relating to emails i.e. the From field. Could it be done by creating a new menu item on the left or even an iframe on a new tab within the Account record? We would also like to know if there is a way to show From and To for Activities that are Emails in the Activities and History views. It's difficult to always have to open the Activity to see who sent and received ...

Viewing Source Code in Outlook
In Outlook Express, when forwarding a message with those annoying forwarding hashmarks and vertical lines on the left, there was an option of View, Source Code and by deleting any instance of "<Blockquote XXXXXX> and everything between the arrows, I could remove these vertical lines. In Outlook 07, I cannot locate this feature. Is there another way to clean up these messages before sending them off ??? TIA, Hagen Sahm Outlook doesn't have the same type of view source command that OE does. You will need to switch to plain text format before forwarding o...

Problem setting color in rich edit view
I have a hander for OnEnChange in my view. In it, I parse the line where the cursor is and set the color for certain portions of the text. To set the color, I set the selection to the portion I want to color, and then do the following: CHARFORMAT2 cf; cf.dwMask = CFM_COLOR; cf.crTextColor = COLOR_KEYWORD; GetRichEditCtrl().SetSelectionCharFormat(cf); where COLOR_KEYWORD is defined as: RGB(0,128,0) SetSelectionCharFormat is returning true, however, the color does not actually change in the view. Any ideas? -- Adam Clauss cabadam@tamu.edu Try: cf.dwEffects = NULL; // Only works if th...

Printing from Arranged View
Is it possible to print two workbooks, that are in a Horizontal Arranged View, at the same time and one one page? For example, workbook A (sheet1) and workbook B (sheet 1) are arranged together in a horizontal view and I like to print the contents of this view on one page. I tried the arranged feature and the compare side by side feature but did not avail. Neo, Excel prints one sheet at a time, on as many pages as it takes. It won't combine sheets. The view settings have no effect on printing. To get stuff from two sheets to print on one page, you'd have to link the d...

Create Clustered index or Covering Index
Which would be better on a 3 column temporary table? A Clustered index or a Covering Index? In this case the uniqueidentifier is always the same and is done this way because there are about 20 procedures that already use this as a static table and I don't want to impace all the procedures with this change. CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE CLUSTERED INDEX idx on #temp (CID) or CREATE TABLE #temp (a uniqueidentifier, PID int,CID int) CREATE INDEX idx on #temp (CID) INCLUDE (PID,a) Thanks, Tom Tom, Better for what? It depends....

INDEX/MATCH help
I just learned about the INDEX/MATCH function while searching some of the Excel tip pages - and I think I can make good use of it. What I want to do is this: worksheet 1 - is the format of our Income Statement worksheet 2 (titled TB) is our Trial Balance. I want worksheet 1 to read TB (worksheet 2) and pick up the YTD amount for each account #. I tested this out and my formula is working fine. However, sometimes an account # on worksheet 1 doesn't appear on Worksheet 2 because there wasn't any activity. In this case I get a $N/A. I need to edit my formula so that if there ...

Viewing MSN Historical Stock Charts
Any idea when they will have a working integration between MSN charts and Money? Constantly get an error trying to pull up stock charts from money's portfolio. Never have any problem when I'm viewing from MSN site. My bet is that there is something wrong with the money browser. Found out one additional item. Things work well if I don't use the bollinger bands (an option under price indicators). Any ideas why that would be? "Brian H" <brianh4321@hotmail.com> wrote in message news:eB5ctNrtDHA.2712@tk2msftngp13.phx.gbl... > Any idea when they will have a working...

Pivot Table with Multiple Row Fields
I know pivot tables are used to summarize information, but for my application I need to fill in. e.g. (Normal Pivot) Item Desc Region Week 1 Week 2 Week 3 XXXX XXXX XXXX XX XX XX XXXX XX XX XX XXXX XX XX XX XXXX XX XX XX (Would Like) Item Desc Region Week 1 Week 2 Week 3 XXXX XXXX XXXX XX XX XX XXXX XXXX XXXX XX XX XX XXXX XXXX XXXX XX XX ...

Negative Extended Cost in SOP Header table
I have a situation where a fair number of documents in the SOP30200 table have a negative amount in the EXTDCOST field. I am taking steps to resolve the problem, but was wondering if anyone else finds the same thing? You'd basically need to do a query of your table for EXTDCOST < 0. I'd put it a full SQL statement, but my last post got rejected - I think because of the SQL statement. Anyway, I'm trying to find out if it's just a problem isolated to myself or not. Thanks! Mike, I have about 7 out of ~800 entries in a GP 8.0 database with EXTDCOST < 0. The ...

Customizing my active view
Hi, is there a chance to add a column to "my active accounts" view that contains besides the owner the user/team the individual account is shared with? Thanks for reply! ...

Table sorting and v look up possible &
Hello, Just need some advice with regards to the below. I have 2 tables. The first consists of 1 column of names and several other columns of other details. This table contains the records of people i need to update with details of the 2nd table.... The 2nd table contains 2 columns of the same names but split, with first name being in a separate column than the surname. Then further columns that have more info in. This table has 11000 peoples names in as this data is a simple data dump, opposed to the exact names of the people in table 1 i need. I will use a v look up but after i ha...

Indexes in tables.
Hi, Can I add index for tables in SQL Manager. For example for SOP30200 index for any field. Will it be a problem with program or not? Any other ways? Thanks, Vitali I believe any future upgrade will blow away your index. -- Charles Allen, MVP "Vitali V" wrote: > Hi, > > Can I add index for tables in SQL Manager. For example for SOP30200 index > for any field. Will it be a problem with program or not? > Any other ways? > > Thanks, > > Vitali > > > Suggest you leave the tables alone. You'll just get into a pickle.. "Char...

Match & Index??
I have the following info in different workbooks. In workbook 1, I have in Columns A,B,C,D: Mark & No. Start End Deal SLGG1234 3/15/2004 6/15/2004 211 SLGG1234 1/1/2004 3/14/2004 111 SLGG1234 6/16/2004 8/15/2004 311 SLGG1255 2/13/2004 8/15/2004 411 In workbook 2, I have Columns A,B, C: Mark & No. Date Deal SLGG1234 3/14/2004 SLGG1234 6/14/2004 In Column C of workbook 2, I want a formula to look at Cols A and B. Compare the info in them to Cols A,B and C in workboo...

Address Book View
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Am I able to view my Address Book contacts as a Business Card like they were in Outlook 2003? If not will it be incorporated into updates later on? <br><br>Robin <FONT FACE="Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'>On 1/6/10 6:23 AM, in article <a href="59bb02ce.-1@webcrossing.JaKIaxP2ac0">59bb02ce.-1@webcrossing.JaKIaxP2ac0</a>, &quot;<a href="rbukunus@officeformac.com">rbukunus@officeformac...

is there an Excel table available?
My maths isn't up to this! I'm hoping there is a pre-defined Excel table in existence already - if so, please point me to where I can download? For a range of Annual Percentage Rates (APR) - between say 3% and 8%, the AER rates for interest paid monthly (and the daily rates by month, as interest is allegedly calculated daily). Intention is to track progress/total of various cash investments. Some are annual deposits, capital increased annually, others are "ad hoc" deposits with spasmodic capital injections, interest accrued daily, credited monthly. It's the latter...

Journal view settings
Hello: I am using Outlook 2002. I'd like to switch the journal from the default horizontal scrolling to a vertical view, and filter empty dates. I've tried to find the settings for that with no luck. It seems that the journal view I want is set automatically on archived journals, because my "archive folders" display it like that without me doing anything. Create a new table view on the Journal folder and set its filter.=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers ...

Table Creation Head Scratcher
Here is a problem I have never seen. I have a stored procedure that is in excess of 3500 lines and has a single INT input parameter. Its function is to create 80+ tables along with indexes, foreign keys, extended properties and the like. The sp works exactly as expected in Management Studio. Yet when called from code (Java via Apache Tomcat) only half the tables are created. Oddly enough it’s the bottom half of the sp whose tables actually get created. To be clear, roughly the bottom 40 tables get created while the top 40 do not. It almost seems like I am bumping into so...

index
Hi guys, I have a web site I developed in Publisher Windows 98. We now have a new version. When I work on it and then publish to the web, it does not create a index page, infact it does not see page 1 ! I inserted a blank page as new number 1 and then it made page 2 as the index page ! Appears to work but what am I missing. Also where I have photos and a frame around them, it makes two files/copies of each photo to be sent to web site. Like 505 and 5051. In 5051 the photo is of pour quality but 505 is fine. Have not sent over yet as it appears I have to remove the existing index...

Order/Invoice Work and History tables
We use the Scribe Great Plains adapter to pass in and process orders and invoices. Off and on we have failures for one reason or another when passing the transactions. My question is: when are the SOPTYPE 2 [orders] moved from the SOP10100 & SOP10200 work tables to the SOP30200 & SOP30300 History tables? In our case we have ended up with some order transaction records still in the work tables after the corresponding invoices have processed through and been posted. I assume that the order should be in history if it has already been invoiced. I would like to know which records should...

Index of using XPATH?
Hi. I have an xml file in this format: <strings> <string>Item1</string> <string>Item2</string> <string>Item3</string> <string>Item4</string> </string> I'm looking for the best way to search for a specific string, and return the index of its node within the strings element. That is, if someone enters Item3, I need the value 3 (or 2, if the index is 0-based). I have a feeling there's a reasonably simple XPath expression that will get this for me, but I'm trying to avoid iterating through all the elements to fin...

covert web table in excel
Recently,i have opened a table in the oracle database,while it just appeared in the html as it opened with excel. ie:<table><tr>product*<td>*</td></tr> how can i convert into the table in the excel? Any help will be appreciate. Regards, Sebation.G ...