ch 30 - advanced scalability.sql

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

      OrderDetailID, OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ExtendedPrice,
      ShipRequestDate, ShipDate, ShipComment
    FROM OrderDetailJR
UNION ALL
  SELECT LocationCode,
      OrderDetailID, OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ExtendedPrice,
      ShipRequestDate, ShipDate, ShipComment
    FROM OrderDetailKDH

go
--------------------------------------------------------
-- Selecting through the Partition View

SELECT LocationCode, OrderNumber 
  FROM OrderAll

SELECT OrderNumber 
  FROM OrderAll
  WHERE LocationCode = 'KDH'

--Updating through the Partition View

UPDATE OrderAll
  SET Closed = 1

UPDATE OrderAll
  SET Closed = 0 
  WHERE LocationCode = 'KDH'

----------------------------------------------------------
--Moving Data
UPDATE OrderAll
  SET Locationcode = 'JR'
  WHERE OrderNumber = 9

select * from [orderall] where ordernumber = 9

go
-----------------------------------------------------------
-- Move Data Stored Procedure

CREATE PROCEDURE OrderMovePartition (
  @OrderNumber INT,
  @NewLocationCode CHAR(5) )
AS
SET NoCount ON

DECLARE @OldLocationCode CHAR(5)

SELECT @OldLocationCode = LocationCode
  FROM OrderAll
  WHERE OrderNumber = @OrderNumber

-- Insert New Order
  SELECT DISTINCT
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, LocationID, OrderDate, Closed
    INTO #OrderTemp
    FROM OrderAll
    WHERE OrderNumber = @OrderNumber
      AND LocationCode = @OldLocationCode

INSERT dbo.OrderAll (LocationCode,
    OrderID, OrderNumber, ContactID, OrderPriorityID,
    EmployeeID, LocationID, OrderDate, Closed)
  SELECT 
      @NewLocationCode,
      OrderID, OrderNumber, ContactID, OrderPriorityID,
      EmployeeID, LocationID, OrderDate, Closed
    FROM #OrderTemp

-- Insert the New OrderDetail
  SELECT DISTINCT
      OrderDetailID, OrderDetailAll.OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ShipRequestDate, ShipDate, ShipComment
    INTO #TempOrderDetail
    FROM OrderDetailALL
      JOIN OrderALL
      ON OrderDetailALL.OrderID = OrderALL.OrderID
    WHERE OrderNumber = @OrderNumber

Select * from #TempOrderDetail

INSERT dbo.OrderDetailAll (
    LocationCode, OrderDetailID, OrderID, ProductID,
    NonStockProduct, Quantity, UnitPrice, ShipRequestDate,
    ShipDate, ShipComment)
  SELECT @NewLocationCode,
      OrderDetailID, OrderID, 
      ProductID, NonStockProduct, Quantity, UnitPrice, 
      ShipRequestDate, ShipDate, ShipComment
    FROM #TempOrderDetail

-- Delete the Old OrderDetail
DELETE FROM OrderDetailAll
  FROM OrderDetailAll
    JOIN OrderALL
      ON OrderAll.OrderID = OrderDetailAll.OrderID    
  WHERE OrderNumber = @OrderNumber
    AND OrderDetailAll.LocationCode = @OldLocationCode

-- Delete the Old Order
DELETE FROM OrderALL
  WHERE OrderNumber = @OrderNumber
    AND LocationCode = @OldLocationCode
go 
-------------------------------

EXEC OrderMovePartition 9, 'JR'

Select 
    OrderAll.OrderNumber,
    OrderALL.LocationCode as OrderL,
    OrderDetailALL.LocationCode AS DetailL
  FROM OrderDetailAll
    JOIN OrderAll
      ON OrderAll.OrderID = OrderDetailAll.OrderID
  WHERE OrderNumber = 9

------------------------------------------------
------------------------------------------------
-- Distributed Partition Views

-- On Server 1 ...

DROP DATABASE DistView
go
CREATE DATABASE DistView
go
USE DistView
CREATE TABLE dbo.Inventory(
  LocationCode CHAR(10) NOT NULL,
  ItemCode INT NOT NULL,
  Quantity INT )
ALTER TABLE dbo.Inventory 
  ADD CONSTRAINT 
    PK_Inventory PRIMARY KEY NONCLUSTERED(LocationCode, ItemCode)
ALTER TABLE dbo.Inventory
  ADD CONSTRAINT
    Inventory_PartitionCheck CHECK (LocationCode = 'Noli')

INSERT dbo.Inventory
  (LocationCode, ItemCode, Quantity)
VALUES ('NOLI', 12, 1)

-- Link to the Second Server
EXEC sp_addlinkedserver 
  @server = 'Noli\SQL2', 
  @srvproduct = 'SQL Server'

EXEC sp_addlinkedsrvlogin 
  @rmtsrvname = 'NOLI\SQL2'

-- Lazy Schema Validation
EXEC sp_serveroption 'Noli\SQL2','lazy schema validation', true
go
-- Create the Distributed Partition View
CREATE VIEW InventoryAll
AS
  SELECT * 
    FROM dbo.Inventory
UNION ALL
  SELECT * 
    FROM [NOLI\SQL2].DistView.dbo.Inventory

---------------
-- On Server 2 ...

DROP DATABASE DistView
go
CREATE DATABASE DistView
go
USE DistView
CREATE TABLE dbo.Inventory(
  LocationCode CHAR(10) NOT NULL,
  ItemCode INT NOT NULL,
  Quantity INT )
ALTER TABLE dbo.Inventory 
  ADD CONSTRAINT 
    PK_Inventory PRIMARY KEY NONCLUSTERED(LocationCode, ItemCode)
ALTER TABLE dbo.Inventory
  ADD CONSTRAINT
    Inventory_PartitionCheck CHECK (LocationCode = 'Noli\SQL2')

INSERT dbo.Inventory
  (LocationCode, ItemCode, Quantity)
VALUES ('NOLI\SQL2', 14, 2)

-- Link to the First Server
EXEC sp_addlinkedserver 
  @server = 'Noli', 
  @srvproduct = 'SQL Server'

EXEC sp_addlinkedsrvlogin 
  @rmtsrvname = 'NOLI'

-- Lazy Schema Validation
EXEC sp_serveroption 'Noli','lazy schema validation', true

-- Create the Distributed Partition View
CREATE VIEW InventoryAll
AS
  SELECT * 
    FROM dbo.Inventory
UNION ALL
  SELECT * 
    FROM NOLI.DistView.dbo.Inventory

-------
-- From either server

SELECT * 
  FROM InventoryAll

-- Update 
SET XACT_ABORT ON
UPDATE InventoryAll
  SET LocationCode = 'Noli'
  WHERE ItemCode = 14


-- Highly Scalable Distributed Partition View
SELECT * 
  FROM InventoryAll
    WHERE LocationCode = 'Noli\SQL2'
      AND ItemCode = 14
     

-----------------------------------------
-----------------------------------------
-- Indexed Views

USE OBXKites

SET ANSI_Nulls ON
SET ANSI_Padding ON
SET ANSI_Warnings ON
SET ARITHABORT ON
SET Concat_Null_Yields_Null ON
SET Quoted_Identifier ON
SET Numeric_RoundAbort OFF

----------------------
-- two table indexed view

CREATE VIEW vContactOrder
WITH SCHEMABINDING
AS
SELECT c.ContactID, o.OrderID
  FROM dbo.Contact as c
    JOIN dbo.[Order] as o
      ON c.ContactID = o.ContactID
    
CREATE UNIQUE CLUSTERED INDEX ivContactOrder ON vContactOrder
  (ContactID, OrderID)

sp_help vContactOrder

SELECT  Contact.ContactID, OrderID 
  FROM dbo.Contact
    JOIN dbo.[Order]
      ON Contact.ContactID = [Order].ContactID
  
 
----------------------------
-- 3 table indexed view

CREATE alter VIEW vContactOrderDetail
WITH SCHEMABINDING
AS
SELECT c.ContactID, o.OrderID, d.OrderDetailID
  FROM dbo.Contact as c
    JOIN dbo.[Order] as o
      ON c.ContactID = o.ContactID
    JOIN dbo.OrderDetail as d
      ON o.OrderID = d.OrderID

CREATE UNIQUE CLUSTERED INDEX ivContactOrderDetail ON vContactOrderDetail
  (ContactID, OrderID, OrderdetailID)

sp_help vContactOrderDetail

SELECT Contact.ContactID, OrderdetailID
  FROM dbo.Contact
    JOIN dbo.[Order]
      ON Contact.ContactID = [Order].ContactID
    JOIN dbo.OrderDetail
      ON [Order].OrderID = OrderDetail.OrderID
    JOIN dbo.Product
      ON OrderDetail.ProductID = Product.ProductID