Re: Create indexed view

From: Dejan Markic (dejan_at_akton.is)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 10:19:33 +0100

Hello!

I've read all that ... I'm just interested about the query with which I can
create an indexed view ... A query that would do the same as UNION ALL, but
would allow me to create an indexed view.

Right now I have a quiry that looks like this:

select id,name from t1 union all
select id,name from t2

Now, I cannot create indexed view because this query uses UNION ... is there
another way to get records from many tables?

Kind regards,
Dejan

[Change TLD from .is to .si]
"Adam Machanic" <amachanic@air-worldwide.nospamallowed.com> wrote in message
news:O8pHbePDEHA.2600@TK2MSFTNGP09.phx.gbl...
| Look up the BOL article titled "Creating an Indexed View". There are a
lot
| of things you need to do in order to qualify for indexed view creation,
| including schema binding, not using SELECT *, including a COUNT_BIG() in
| your SELECT list, etc... They're all listed there. Keep in mind that
| indexed views were created for automatic enhancement of aggregate queries
| that had already been written against base tables (the query optimizer
knows
| to look for indexed views if they're available and use them instead of the
| base tables for aggregate queries... That's what the COUNT_BIG() is for).
| You'll also have other issues in order to make the query optimizer
properly
| use your view because it's partitioned. Another BOL article you should
| check out is "Using Partitioned Views".
|
|
| "Dejan Markic" <dejan@akton.is> wrote in message
| news:2Tg6c.6536$%x4.865008@news.siol.net...
| > Hello!
| >
| > I have three or more tables, each table holds data for one month. Now I
| want
| > to create a view where I would be able to select data from different
| months.
| > I tried to make a view like this:
| > select * from t1 UNION ALL
| > select * from t2 UNION ALL
| > select * from t3 ...
| >
| > ...
| > And it would work OK, but according to documentation this is not an
| indexed
| > view and I cannot create indexes on it. Can I create that view in some
| other
| > way to get the same data and that this view would qualify as index view?
| >
| > Or other way ... if I use indexes on participating tables ... would that
| > help? I hear view would hurt the performace if it is not indexed view?
| >
| > Thanks for you input!
| >
| > Kind regards,
| > Dejan
| >
| >
|
|

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Graphical Union-Query Builder?
    ... If you are limiting the input in each of the sub-queries in your Union ... Use the filtered queries as the input to your Union query: ... Most "functional IT users" will not know anything about SQL. ... I didn't know that fields of subsequent queries in a Union could have ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: How to get a distinct count of result set of multople table joins?
    ... since the UNION syntax removes duplicate rows automatically. ...   "SORT " in your execution plan. ... The base query is an outer join. ...
    (comp.databases.oracle.misc)