The Microsoft SQL Server Development Customer Advisory Team has a nice blog post about Distributed Partitioned Views / Federated Databases
They cover the following definitions
Definition 1: Local Partitioned View – A single table is horizontally split into multiple tables, usually all have the same structure.
Definition 2: Cross Database Partitioned View – tables are split among different databases on the same server instance
Definition 3: Distributed (across server or instance) Partitioned View. Tables participating in the view reside in different databases which reside on different servers or different instances.
Make sure you read the list of 13 items under Lessons Learned on Distributed Partitioned Views: (multiple servers involved)
Here is an interesting one:
- Don’t use non-deterministic functions directly in your insert or update statements. This causes the optimizer to send the query to all servers.
DON’T DO THIS
UPDATE myPartitionedView SET column1 = GETDATE() WHERE …
DO THIS
DECLARE @dtNow datetime
SET @dtNow = GETDATE()
UPDATE myPartitionedView SET column1 = @dtNow WHERE …
Link to the article: http://blogs.msdn.com/sqlcat/archive/2007/06/20/distributed-partitioned-views-federated-databases-lessons-learned.aspx