Re: Create indexed view
From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 03/19/04
- Next message: Dave Pearson: "Re: Exporting query results to file using | (pipe) as a deliminator."
- Previous message: Tibor Karaszi: "Re: Mysterious Port of SQL Server not shown by netstat"
- In reply to: Dejan Markic: "Re: Create indexed view"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 19 Mar 2004 06:08:32 -0500
See my answer to the same question elsewhere in this group - and please
don't multi-post.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql . "Dejan Markic" <dejan@akton.is> wrote in message news:XXy6c.6573$%x4.871293@news.siol.net... 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 | > | > | |
- Next message: Dave Pearson: "Re: Exporting query results to file using | (pipe) as a deliminator."
- Previous message: Tibor Karaszi: "Re: Mysterious Port of SQL Server not shown by netstat"
- In reply to: Dejan Markic: "Re: Create indexed view"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
