SQL Server 2012: Materialized View (Indexed View)

Hello Folks,

I've 10 different tables with same table design (i.e. fields).
I would like to create a script for complex logic. So, I wanted to create an Indexed View with UNION for all tables and use that view in complex logic.
But UNION does not allow in Indexed View.

Do you have any thoughts for best way to do that?
Like, I've one thought that I create a temp table in complex logic and store all data in temp table from 10 different tables.
Actually, one table contains around 1 Million records.

I am looking forward response.

Best Regards,
Mohit Pandit
LVL 5
MohitPanditAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Instead of using 10 different tables use one partitioned table.
0
MohitPanditAuthor Commented:
But for now, we have to make it separate 10 tables.
Could you please assist best optimal solutions using this design for now?
0
Scott PletcherSenior DBACommented:
You can use a standard view.  As long as the tables are properly indexed, it should perform OK.

CREATE VIEW ... AS
SELECT ...
FROM dbo.table1
WHERE ...
UNION ALL
SELECT ...
FROM dbo.table2
WHERE ...
UNION ALL
...
0
Randy Knight, MCMPrincipal ConsultantCommented:
Another option would be to use a partitioned view if you have a good partitioning key.  This would perform better than just a regular view.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MohitPanditAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.