<div>
Instead of using 10 different tables use one <a href="http://msdn.microsoft.com/en-us/library/ms188730(v=sql.110).aspx" rel="ugc nofollow">partitioned table</a>.
</div>


Instead of using 10 different tables use one partitioned table [http://msdn.microsoft.com/en-us/library/ms188730(v=sql.110).aspx].

<div>
But for now, we have to make it separate 10 tables.<br />
Could you please assist best optimal solutions using this design for now?
</div>


But for now, we have to make it separate 10 tables.
Could you please assist best optimal solutions using this design for now?

<div>
You can use a standard view. &nbsp;As long as the tables are properly indexed, it should perform OK.<br />
<br />
CREATE VIEW ... AS<br />
SELECT ...<br />
FROM dbo.table1<br />
WHERE ...<br />
UNION ALL<br />
SELECT ...<br />
FROM dbo.table2<br />
WHERE ...<br />
UNION ALL<br />
...
</div>


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
...

<div>
<div class="content wysiwyg-content">
Hello Folks,<br />
<br />
I've 10 different tables with same table design (i.e. fields).<br />
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.<br />
But UNION does not allow in Indexed View.<br />
<br />
Do you have any thoughts for best way to do that?<br />
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.<br />
Actually, one table contains around 1 Million records.<br />
<br />
I am looking forward response.<br />
<br />
Best Regards,<br />
Mohit Pandit
</div>
</div>


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

SQL Server 2012: Materialized View (Indexed View)

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.