ch 30 - advanced scalability.sql

《SQLServer2000宝典》—包括本书中的所有代码

-----------------------------------------------------------
-- SQL Server 2000 Bible 
-- Wiley Publishing 
-- Paul Nielsen

-- Chapter 30 - Advanced Scalability

-----------------------------------------------------------
-----------------------------------------------------------

-- Create the partition tables
USE OBXKites

DROP Table OrderDetailCH
DROP TABLE OrderCH
DROP Table OrderDetailJR
DROP TABLE OrderJR
DROP Table OrderDetailKDH
DROP TABLE OrderKDH
DROP View OrderAll

-- Data Distribution
SELECT LocationCode, Count(OrderNumber) AS Count
  FROM Location
    JOIN [Order]
      ON [Order].LocationID = Location.LocationID
   GROUP BY LocationCode

-- Cape Hatteras

--Order Table
CREATE TABLE dbo.OrderCH (
  LocationCode CHAR(5) NOT NULL,
  OrderID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID()),
  OrderNumber INT NOT NULL,
  ContactID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Contact,
  OrderPriorityID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.OrderPriority,
  EmployeeID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Contact,
  LocationID UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES dbo.Location,
  OrderDate DATETIME NOT NULL DEFAULT (GETDATE()),
  Closed BIT NOT NULL DEFAULT (0) -- set to true when Closed
   ) 
  ON [Primary]
go

-- PK
ALTER TABLE dbo.OrderCH 
  ADD CONSTRAINT 
    PK_OrderCH PRIMARY KEY NONCLUSTERED(LocationCode, OrderID) 

-- Check Constraint
ALTER TABLE dbo.OrderCH
  ADD CONSTRAINT
    OrderCH_PartitionCheck CHECK (LocationCode = 'CH')
go
-- Order Detail Table
CREATE TABLE dbo.OrderDetailCH (
  LocationCode CHAR(5) NOT NULL,
  OrderDetailID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID()),
  OrderID UNIQUEIDENTIFIER NOT NULL, 
  ProductID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Product,
  NonStockProduct NVARCHAR(256),
  Quantity NUMERIC(7,2) NOT NULL,
  UnitPrice MONEY NOT NULL,
  ExtendedPrice AS Quantity * UnitPrice, 
  ShipRequestDate DATETIME,
  ShipDate DATETIME,
  ShipComment NVARCHAR(256)
  ) 
  ON [Primary]
go

ALTER TABLE dbo.OrderDetailCH
  ADD CONSTRAINT
    FK_OrderDetailCH_Order 
      FOREIGN KEY (LocationCode,OrderID) 
      REFERENCES dbo.OrderCH(LocationCode,OrderID)

ALTER TABLE dbo.OrderDetailCH 
  ADD CONSTRAINT 
    PK_OrderDetailCH PRIMARY KEY NONCLUSTERED(LocationCode, OrderDetailID)

ALTER TABLE dbo.OrderDetailCH
  ADD CONSTRAINT
    OrderDetailCH_PartitionCheck CHECK (LocationCode = 'CH')
go

-- move the data
INSERT dbo.OrderCH (LocationCode,
    OrderID, OrderNumber, ContactID, OrderPriorityID,
    EmployeeID, LocationID, OrderDate, Closed)
  SELECT 
      'CH',
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, [Order].LocationID, OrderDate, Closed
    FROM [Order]
      JOIN Location 
        ON [Order].LocationID = Location.LocationID
    WHERE LocationCode = 'CH'

INSERT dbo.OrderDetailCH (
    LocationCode, OrderDetailID, OrderID, ProductID,
    NonStockProduct, Quantity, UnitPrice, ShipRequestDate,
    ShipDate, ShipComment)
  SELECT 'CH',
      OrderDetailID, OrderDetail.OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ShipRequestDate, ShipDate, ShipComment
    FROM OrderDetail
      JOIN OrderCH
      ON OrderDetail.OrderID = OrderCH.OrderID

-- Jockey's Ridge (JR)

--Order Table
CREATE TABLE dbo.OrderJR (
  LocationCode CHAR(5) NOT NULL,
  OrderID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID()),
  OrderNumber INT NOT NULL,
  ContactID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Contact,
  OrderPriorityID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.OrderPriority,
  EmployeeID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Contact,
  LocationID UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES dbo.Location,
  OrderDate DATETIME NOT NULL DEFAULT (GETDATE()),
  Closed BIT NOT NULL DEFAULT (0) -- set to true when Closed
   ) 
  ON [Primary]
go

-- PK
ALTER TABLE dbo.OrderJR 
  ADD CONSTRAINT 
    PK_OrderJR PRIMARY KEY NONCLUSTERED(LocationCode, OrderID) 

-- Check Constraint
ALTER TABLE dbo.OrderJR
  ADD CONSTRAINT
    OrderJR_PartitionCheck CHECK (LocationCode = 'JR')
go
-- Order Detail Table
CREATE TABLE dbo.OrderDetailJR (
  LocationCode CHAR(5) NOT NULL,
  OrderDetailID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID()),
  OrderID UNIQUEIDENTIFIER NOT NULL, 
  ProductID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Product,
  NonStockProduct NVARCHAR(256),
  Quantity NUMERIC(7,2) NOT NULL,
  UnitPrice MONEY NOT NULL,
  ExtendedPrice AS Quantity * UnitPrice, 
  ShipRequestDate DATETIME,
  ShipDate DATETIME,
  ShipComment NVARCHAR(256)
  ) 
  ON [Primary]
go

ALTER TABLE dbo.OrderDetailJR
  ADD CONSTRAINT
    FK_OrderDetailJR_Order 
      FOREIGN KEY (LocationCode,OrderID) 
      REFERENCES dbo.OrderJR(LocationCode,OrderID)

ALTER TABLE dbo.OrderDetailJR 
  ADD CONSTRAINT 
    PK_OrderDetailJR PRIMARY KEY NONCLUSTERED(LocationCode, OrderDetailID)

ALTER TABLE dbo.OrderDetailJR
  ADD CONSTRAINT
    OrderDetailJR_PartitionCheck CHECK (LocationCode = 'JR')
go

-- move the data
INSERT dbo.OrderJR (LocationCode,
    OrderID, OrderNumber, ContactID, OrderPriorityID,
    EmployeeID, LocationID, OrderDate, Closed)
  SELECT 
      'JR',
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, [Order].LocationID, OrderDate, Closed
    FROM [Order]
      JOIN Location 
        ON [Order].LocationID = Location.LocationID
    WHERE LocationCode = 'JR'

INSERT dbo.OrderDetailJR (
    LocationCode, OrderDetailID, OrderID, ProductID,
    NonStockProduct, Quantity, UnitPrice, ShipRequestDate,
    ShipDate, ShipComment)
  SELECT 'JR',
      OrderDetailID, OrderDetail.OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ShipRequestDate, ShipDate, ShipComment
    FROM OrderDetail
      JOIN OrderJR
      ON OrderDetail.OrderID = OrderJR.OrderID

-- Kill Devil Hills (KDH)

--Order Table
CREATE TABLE dbo.OrderKDH (
  LocationCode CHAR(5) NOT NULL,
  OrderID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID()),
  OrderNumber INT NOT NULL,
  ContactID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Contact,
  OrderPriorityID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.OrderPriority,
  EmployeeID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Contact,
  LocationID UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES dbo.Location,
  OrderDate DATETIME NOT NULL DEFAULT (GETDATE()),
  Closed BIT NOT NULL DEFAULT (0) -- set to true when Closed
   ) 
  ON [Primary]
go

-- PK
ALTER TABLE dbo.OrderKDH 
  ADD CONSTRAINT 
    PK_OrderKDH PRIMARY KEY NONCLUSTERED(LocationCode, OrderID) 

-- Check Constraint
ALTER TABLE dbo.OrderKDH
  ADD CONSTRAINT
    OrderKDH_PartitionCheck CHECK (LocationCode = 'KDH')
go
-- Order Detail Table
CREATE TABLE dbo.OrderDetailKDH (
  LocationCode CHAR(5) NOT NULL,
  OrderDetailID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL DEFAULT (NEWID()),
  OrderID UNIQUEIDENTIFIER NOT NULL, 
  ProductID UNIQUEIDENTIFIER NULL FOREIGN KEY REFERENCES dbo.Product,
  NonStockProduct NVARCHAR(256),
  Quantity NUMERIC(7,2) NOT NULL,
  UnitPrice MONEY NOT NULL,
  ExtendedPrice AS Quantity * UnitPrice, 
  ShipRequestDate DATETIME,
  ShipDate DATETIME,
  ShipComment NVARCHAR(256)
  ) 
  ON [Primary]
go

ALTER TABLE dbo.OrderDetailKDH
  ADD CONSTRAINT
    FK_OrderDetailKDH_Order 
      FOREIGN KEY (LocationCode,OrderID) 
      REFERENCES dbo.OrderKDH(LocationCode,OrderID)

ALTER TABLE dbo.OrderDetailKDH 
  ADD CONSTRAINT 
    PK_OrderDetailKDH PRIMARY KEY NONCLUSTERED(LocationCode, OrderDetailID)

ALTER TABLE dbo.OrderDetailKDH
  ADD CONSTRAINT
    OrderDetailKDH_PartitionCheck CHECK (LocationCode = 'KDH')
 
go

-- move the data
INSERT dbo.OrderKDH (LocationCode,
    OrderID, OrderNumber, ContactID, OrderPriorityID,
    EmployeeID, LocationID, OrderDate, Closed)
  SELECT 
      'KDH',
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, [Order].LocationID, OrderDate, Closed
    FROM [Order]
      JOIN Location 
        ON [Order].LocationID = Location.LocationID
    WHERE LocationCode = 'KDH'

INSERT dbo.OrderDetailKDH (
    LocationCode, OrderDetailID, OrderID, ProductID,
    NonStockProduct, Quantity, UnitPrice, ShipRequestDate,
    ShipDate, ShipComment)
  SELECT 'KDH',
      OrderDetailID, OrderDetail.OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ShipRequestDate, ShipDate, ShipComment
    FROM OrderDetail
      JOIN OrderKDH
      ON OrderDetail.OrderID = OrderKDH.OrderID
go

--------------------------------------------------
-- Creating the Local Partition Views

CREATE VIEW OrderAll
AS
  SELECT 
      LocationCode,
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, LocationID, OrderDate, Closed
    FROM OrderCH
UNION ALL
  SELECT 
      LocationCode,
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, LocationID, OrderDate, Closed
    FROM OrderJR
UNION ALL
  SELECT 
      LocationCode,
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, LocationID, OrderDate, Closed
    FROM OrderKDH
go

--OrderDetailAll
CREATE VIEW OrderDetailAll
AS
  SELECT LocationCode,
      OrderDetailID, OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ExtendedPrice,
      ShipRequestDate, ShipDate, ShipComment
    FROM OrderDetailCH
 UNION ALL
  SELECT LocationCode,