Databases Reference
In-Depth Information
// Connect to the federation member
// FILTERING=OFF means that we are working with all the atomic units in
// this federation member
USE FEDERATION PurchaseHistory (purchaseId = 0) WITH FILTERING=OFF, RESET
// Create a federated table on the purchaseId federation key
CREATE TABLE Purchases (
pid bigint NOT NULL primary key, -- unique identifier for this record
amount decimal(10, 2) NOT NULL, -- the amount of the purchase
storeId int NOT NULL -- the store where the purchase was made
)
FEDERATED ON (purchaseId = pid)
Let's create additional objects in this federation member. First let's create a reference table, which contains the
list of stores, and a stored procedure that returns the total sales amount for a given store:
// Create a reference table called StoreLocations
CREATE TABLE StoreLocations (
storeId int NOT NULL primary key,
storeName nvarchar(20) NOT NULL,
zipCode nvarchar(10) NOT NULL
)
Let's also add a stored procedure and a foreign key reference:
// Create a stored procedure that returns purchases made in a zip code
CREATE PROC GetPurchasesByZipCode(@zipCode nvarchar(10))
AS
SELECT * FROM Purchases P JOIN StoreLocations S
ON P.storeId = S.storeId
WHERE S.zipCode = @zipCode
// Let's add the foreign key now.
ALTER TABLE Purchases
ADD CONSTRAINT fk_storeId
FOREIGN KEY (storeId) REFERENCES StoreLocations (storeId)
Last, but not least, let's add a few records in our tables. You will see how the split operation will redistribute our
records shortly.
INSERT INTO StoreLocations VALUES (1, 'Boca Raton', '33498' )
INSERT INTO StoreLocations VALUES (2, 'Orlando', '32801' )
INSERT INTO Purchases VALUES (100, 25.0, 1)
INSERT INTO Purchases VALUES (200, 75.0, 2)
INSERT INTO Purchases VALUES (300, 50.0, 1)
INSERT INTO Purchases VALUES (500, 100.0, 2)
INSERT INTO Purchases VALUES (600, 12.0, 1)
At this point, the PurchaseHistory is a shard with a single database in it (a scale-up shard in some ways).
Federations allow you to manage the degree of compression by splitting shards further. Imagine that we need to split
