THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Good Distributed Partitioned Views / Federated Databases Article

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:

  1. Don’t use non-deterministic functions directly in your insert or update statements.   This causes the optimizer to send the query to all servers.



UPDATE myPartitionedView SET column1 = GETDATE() WHERE …



DECLARE @dtNow datetime

SET @dtNow = GETDATE()

UPDATE myPartitionedView SET column1 = @dtNow WHERE …


Link to the article:

Published Thursday, June 21, 2007 10:43 AM by Denis Gobo


No Comments
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement