forked from bmaluijb/CloudDesignPatterns
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathInitializeShard.sql
More file actions
73 lines (67 loc) · 5.22 KB
/
InitializeShard.sql
File metadata and controls
73 lines (67 loc) · 5.22 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- Copyright (c) Microsoft. All rights reserved.
-- Licensed under the MIT license. See LICENSE file in the project root for full license information.
-- Reference table that contains the same data on all shards
IF OBJECT_ID('Regions', 'U') IS NULL
BEGIN
CREATE TABLE [Regions] (
[RegionId] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL
CONSTRAINT [PK_Regions_RegionId] PRIMARY KEY CLUSTERED (
[RegionId] ASC
)
)
INSERT INTO [Regions] ([RegionId], [Name]) VALUES (0, 'North America')
INSERT INTO [Regions] ([RegionId], [Name]) VALUES (1, 'South America')
INSERT INTO [Regions] ([RegionId], [Name]) VALUES (2, 'Europe')
INSERT INTO [Regions] ([RegionId], [Name]) VALUES (3, 'Asia')
INSERT INTO [Regions] ([RegionId], [Name]) VALUES (4, 'Africa')
INSERT INTO [Regions] ([RegionId], [Name]) VALUES (5, 'Oceania')
END
GO
-- Reference table that contains the same data on all shards
IF OBJECT_ID('Products', 'U') IS NULL
BEGIN
CREATE TABLE [Products] (
[ProductId] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL
CONSTRAINT [PK_Products_ProductId] PRIMARY KEY CLUSTERED (
[ProductId] ASC
)
)
INSERT INTO [Products] ([ProductId], [Name]) VALUES (0, 'Gizmos')
INSERT INTO [Products] ([ProductId], [Name]) VALUES (1, 'Widgets')
END
GO
-- Sharded table containing our sharding key (CustomerId)
IF OBJECT_ID('Customers', 'U') IS NULL
CREATE TABLE [Customers] (
[CustomerId] [int] NOT NULL, -- since we shard on this column, it cannot be an IDENTITY
[Name] [nvarchar](256) NOT NULL,
[RegionId] [int] NOT NULL
CONSTRAINT [PK_Customer_CustomerId] PRIMARY KEY CLUSTERED (
[CustomerID] ASC
),
CONSTRAINT [FK_Customer_RegionId] FOREIGN KEY (
[RegionId]
) REFERENCES [Regions]([RegionId])
)
GO
-- Sharded table that has a foreign key column containing our sharding key (CustomerId)
IF OBJECT_ID('Orders', 'U') IS NULL
CREATE TABLE [Orders](
[CustomerId] [int] NOT NULL, -- since we shard on this column, it cannot be an IDENTITY
[OrderId] [int] NOT NULL IDENTITY(1,1),
[OrderDate] [datetime] NOT NULL,
[ProductId] [int] NOT NULL
CONSTRAINT [PK_Orders_CustomerId_OrderId] PRIMARY KEY CLUSTERED (
[CustomerID] ASC,
[OrderID] ASC
),
CONSTRAINT [FK_Orders_CustomerId] FOREIGN KEY (
[CustomerId]
) REFERENCES [Customers]([CustomerId]),
CONSTRAINT [FK_Orders_ProductId] FOREIGN KEY (
[ProductId]
) REFERENCES [Products]([ProductId])
)
GO