ch 30 - advanced scalability.sql

来自「《SQLServer2000宝典》—包括本书中的所有代码」· SQL 代码 · 共 634 行 · 第 1/2 页

      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