In this article, we’ll examine the Distributed Partitioned Views, which we can combine data from tables on multiple instances with UNION ALL. If you combine tables with UNION ALL in the same instance, use Partitioned View instead of Distributed Partitioned Views. “What is Partitioned View in SQL Server”
Unlike Partitioned View, we access other instances via linked server. For more information about View and Partitioned Views, please read “What is View in SQL Server and How To Create a View” and “What is Partitioned View in SQL Server“.
You can find the detailed information about linked server in the article named “How To Create a Linked Server To Connect To SQL Server From SQL Server”
For example, we have 3 instances. Server names are Server1, Server2 and Server3.
To connect to Server2 and Server3 from the Server1, we create a linked server in Server2 and Server3 in the Server1.
Then, we can create a distributed partitioned view with the help of the following scripts.(In Server1)
|
1 2 3 4 5 6 |
CREATE VIEW Dist_Part_View AS SELECT * FROM DatabaseName.Schema_Name.Tablo_Name_x UNION ALL SELECT * FROM Server2.DatabaseName.Schema_Name.Table_Name_y UNION ALL SELECT * FROM Server3.DatabaseName.Schema_Name.Table_Name_z |
