tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 148

Hướng tới mục tiêu này, SQL Server 2008 bao gồm khả năng ước tính trước giảm dữ liệu tiềm năng nén dữ liệu sử dụng hệ thống sp_estimate_data_compression_savings thủ tục lưu trữ. Cụ thể, hệ thống thủ tục này được lưu trữ sẽ sao chép 5% dữ liệu được nén thành tempdb và nén nó. 5% không phải là một mẫu ngẫu nhiên nhưng mỗi XX | Part IX Performance Tuning and Optimization ALTER TABLE ADD CONSTRAINT FK_OrderDetailCH_Order FOREIGN KEY LocationCode OrderID REFERENCES LocationCode OrderID ALTER TABLE ADD CONSTRAINT PK_OrderDetailCH PRIMARY KEY NONCLUSTERED LocationCode OrderDetailID ALTER TABLE ADD CONSTRAINT OrderDetailCH_PartitionCheck CHECK LocationCode CH go -- move the data INSERT 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 WHERE LocationCode CH INSERT LocationCode OrderDetailID OrderID ProductID NonStockProduct Quantity UnitPrice ShipRequestDate ShipDate ShipComment SELECT CH OrderDetailID ProductID NonStockProduct Quantity UnitPrice ShipRequestDate ShipDate ShipComment FROM OrderDetail JOIN OrderCH ON Creating the partition view With the data split into valid partition tables that include the correct primary keys and constraints SQL Server can access the correct partition table through a partition view. The OrderAll view uses a UNION ALL to vertically merge data from all three partition tables CREATE VIEW OrderAll AS 1432 Partitioning 68 SELECT LocationCode OrderlD OrderNumber ContactID OrderPrioritylD EmployeelD LocationlD OrderDate Closed FROM OrderCH UNION ALL SELECT LocationCode OrderlD OrderNumber ContactID OrderPrioritylD EmployeeID LocationID OrderDate Closed FROM OrderJR UNION ALL SELECT LocationCode OrderID OrderNumber ContactID OrderPriorityID EmployeeID LocationID OrderDate Closed FROM OrderKDH Selecting through the partition view When all the data is selected from the OrderAll partition view the query plan shown in Figure 68-1 includes all three partition tables as expected SELECT .