Partitioning SQL data sets is an ideal way to improve scale out, and distributed partitioning is particularly valuable in this regard. Here I will explain the benefits of using partitioned views, and offer some points to keep in mind when defining and creating them.
A partitioned view will show row data (horizontal partitioning) from one or more tables, across one or more servers. When a single server is used, the view is referred to as a local-partitioned view. When this type of view spans more than one server, it is a distributed-partitioned view. Distributed-partition views were added in SQL Server 2000, and the collection of instances on different servers is sometimes referred to as a federated database server set. To read about distributed-partition views, see this white paper.
You should use partitioned views to improve performance when dealing with large data sets. They are useful when you have a table that is used by many groups, departments or regions, and each has its own server. In this instance, you can partition your database on a per server basis to service these different entities.
Viewing fewer rows has several desirable effects. In addition to improving performance, you can present data customized for individuals and groups, and you can enhance system security by providing specific data access based on these partitioned views. Using these views allows you to break apart your large data set into separate smaller member tables or to use a UNION ALL operation to reconstruct the entire data set.
For a UNION ALL, multiple queries are combined and duplicate values are removed if the ALL clause is left unspecified. A SELECT run against a distributed-partitioned view is usually defined against a primary key and uses the CHECK constraint to return the ResultSet. A local-partitioned view doesn't require that you use a CHECK constraint as the source of the data is already known.
When defining a partitioned view, be careful to reference all columns in each member table. Also check that you don't double list a column in your SELECT statement, and make sure every identically-named column in each of the separate partitions is the same data type and therefore compatible. You can list a referenced member table in your view by including its name in the FROM clause of your SELECT statement. One common mistake is not including the primary key in each table or defining it correctly, so make sure you always double check that as well.
If you create partitioned views, keep in mind that you can use each member table's SELECT statement to create a view you are able to update. While the SELECT statement is used to create the base table, the UNION ALL operator can collect all of the ResultSets with any remote table containing an additional identifier.
