[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

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
0
MohitPandit
Asked:
MohitPandit
1 Solution
 
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, MCMCommented:
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
 
MohitPanditAuthor Commented:
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now