Databases Reference
In-Depth Information
•
No Fan-Out.
Another important limitation is the lack of support for cross-federation member
queries. In other words, you cannot issue a request that will span, or fan-out, multiple
federation members, let alone multiple federations. If you want to obtain the total sum of your
sales, you would normally run this simple statement:
SELECT SUM(amount) FROM purchases
Because this statement cannot be executed against multiple federation members by SQL
•
Database, you will need to run this query on every federation member, and perform the final
sum operation in your code.
■
Executing the same query across multiple databases is called a fan-out operation. Fan-out operations are
not hard to implement in your code, although you should execute fan-out queries using multiple threads to improve the
performance of your fetch operation.
Note
There are many more limitations that apply to Federations that may impact your design or your ability to adopt
Federations, such as no support for indexed views, lack of support for the timestamp and rowversion data types, and more.
■
if you are planning to use Federations for your application, you should spend some time reviewing all the
documented limitations on Msdn. search for Federation Guidelines and Limitations using Bing.
Note
Sharding Library
Some of the limitations discussed previously related to fan-out queries can be resolved by coding the logic yourself, or
by using open source libraries that have solved this problem for you.
Of importance is that you can use the Enzo Sharding library discussed in previous chapters. The latest release
supports Federations and allows you to execute statements across multiple federation members easily, using
multithreaded operations for faster results. You can use the built-in fan-out operation of the library to perform
schema changes across your databases, return a single result set, or execute simple aggregations across federation
members.
With the latest library you can execute a distributed query, called D-SQL, using a syntax that extends T-SQL.
The library detects the syntax automatically and executes the inner query across the federation members specified.
Because this is a .NET library, you cannot use this command structure outside of your code. Here is an example of a
D-SQL query that fetches all the PurchaseHistory records, regardless of the number of federation members:
SELECT * USING (SELECT * FROM Purchases) FEDERATED ON (PurchaseHistory)
The inner statement is the query being executed across the PurchaseHistory federation; a single data set is
returned to the calling code. Here are a few other variations of D-SQL that perform other requests:
// finds the maximum amount across all federation members
SELECT MAX(amount) USING (SELECT MAX(amount) FROM Purchases)
FEDERATED ON (PurchaseHistory)
// finds the sum of all sales for zipcode 33498
SELECT SUM(amount) USING (SELECT SUM(amount) FROM Purchases P
