SQL Server 2000 added a feature called the distributed view, that lets you segment a view horizontally across several physical SQL servers. When you perform such a segmentation of a view, you are breaking up a subset of the records from the complete view and placing it on server 1, placing a second set of records on server 2, and so on. This lets you achieve better performance, particularly in views that contain an extremely large number of records.
When you horizontally partition a view you use the CHECK CONTRAINTS command to limit the value that a column can hold. Once the view has been partitioned, you can create a view that links all of the servers together so that to a user it appears as if a single table is being browsed. The CHECK CONSTRAINTS command has a second impact on a distributed view. If you enforce a restriction on the view and that restriction excludes the records on some of your servers, the CHECK CONTSTAINTS command is used to eliminate those servers from the query, which can dramatically improve system performance.
Distributed views are particularly valuable in large corporate databases, and large data-driven Web sites. But here's the biggest plus: As your site continues to grow you can scale your SQL Server database by simply adding more servers to your distributed view.
Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
This was first published in November 2002