Databases Reference
In-Depth Information
Database that you want to work with a specific atomic unit, giving you the impression you have no other record than
the one you want to change. The following statements are the preferred approach to update your records. Note the use
of
FILTERING=ON
.
// Connect to the first federation member
// FILTERING=ON is optional; it is the default setting.
USE FEDERATION PurchaseHistory (purchaseId = 100) WITH FILTERING=ON, RESET
// Get some data; three records will come back
UPDATE Purchases SET amount = 17.25
You can apply the same logic to add or delete records in the federation member. If you want to delete multiple
atomic units you need to use the
FILTERING=OFF
option so that your statement can affect multiple records. Also,
the
INSERT
statement will fail if you are trying to insert an atomic unit for which the key falls outside of the current
federation member partition key range.
Creating More Federations
So far we have created a single federation and split the records in two in order to create two federation members.
However, the federation we created only makes sense for historical purchases because its partition key is on
purchaseId. As discussed in the introduction to this chapter, one of the major benefits of Federations is the ability to
define multiple data domains and manage the compression of each domain separately. What if we also have a large
table that tracks customer visits?
No problem! We can create as many federations as we need and place a collection of tables inside them. Let's
create another federation called CustomerVisits:
// Connect back to the root database
USE FEDERATION ROOT WITH RESET
// Create a new federation
CREATE FEDERATION CustomerVisits (visitId BigInt RANGE)
// Connect to our first federation member
USE FEDERATION CustomerVisits (visitId=0) WITH FILTERING=OFF, RESET
// And let's create a table with a few records
CREATE TABLE CustomerVisits (
vId bigint NOT NULL PRIMARY KEY,
dateOfVisit dateTime NOT NULL,
storeId int NOT NULL)
FEDERATED ON (visitId = vid)
INSERT INTO CustomerVisits VALUES (1, getdate(), 1)
INSERT INTO CustomerVisits VALUES (2, getdate(), 2)
INSERT INTO CustomerVisits VALUES (3, getdate(), 2)
INSERT INTO CustomerVisits VALUES (4, getdate(), 1)
INSERT INTO CustomerVisits VALUES (5, getdate(), 1)
We now have two federations and three federation members in total, as shown in Figure
10-2
.
