Application.Configuration_ApplyPartitioning
Documentation | WideWorldImporters |
Schema | Application |
Name | Configuration_ApplyPartitioning |
Applies table partitioning to Sales.CustomerTransactions andPurchasing.SupplierTransactions`, and rearranges the indexes to suit.
Script
CREATE PROCEDURE [Application].Configuration_ApplyPartitioning WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; IF SERVERPROPERTY(N'IsXTPSupported') = 0 -- TODO Check for a better way to check for partitioning BEGIN -- Currently versions that support in-memory OLTP also support partitions PRINT N'Warning: Partitions are not supported in this edition.'; END ELSE BEGIN -- if partitions are permitted BEGIN TRAN; DECLARE @SQL nvarchar(max) = N''; IF NOT EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'PF_TransactionDateTime') BEGIN SET @SQL = N' CREATE PARTITION FUNCTION PF_TransactionDateTime(datetime) AS RANGE RIGHT FOR VALUES (N''20140101'', N''20150101'', N''20160101'', N''20170101'');'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'PF_TransactionDate') BEGIN SET @SQL = N' CREATE PARTITION FUNCTION PF_TransactionDate(date) AS RANGE RIGHT FOR VALUES (N''20140101'', N''20150101'', N''20160101'', N''20170101'');'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'PS_TransactionDateTime') BEGIN -- for Azure DB, assign to primary filegroup IF SERVERPROPERTY('EngineEdition') = 5 SET @SQL = N' CREATE PARTITION SCHEME PS_TransactionDateTime AS PARTITION PF_TransactionDateTime ALL TO ([PRIMARY]);'; -- for other engine editions, assign to user data filegroup IF SERVERPROPERTY('EngineEdition') != 5 SET @SQL = N' CREATE PARTITION SCHEME PS_TransactionDateTime AS PARTITION PF_TransactionDateTime ALL TO ([USERDATA]);'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'PS_TransactionDate') BEGIN -- for Azure DB, assign to primary filegroup IF SERVERPROPERTY('EngineEdition') = 5 SET @SQL = N' CREATE PARTITION SCHEME PS_TransactionDate AS PARTITION PF_TransactionDate ALL TO ([PRIMARY]);'; -- for other engine editions, assign to user data filegroup IF SERVERPROPERTY('EngineEdition') != 5 SET @SQL = N' CREATE PARTITION SCHEME PS_TransactionDate AS PARTITION PF_TransactionDate ALL TO ([USERDATA]);'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'CX_Sales_CustomerTransactions') BEGIN SET @SQL = N' ALTER TABLE Sales.CustomerTransactions DROP CONSTRAINT PK_Sales_CustomerTransactions;'; EXECUTE (@SQL); SET @SQL = N' ALTER TABLE Sales.CustomerTransactions ADD CONSTRAINT PK_Sales_CustomerTransactions PRIMARY KEY NONCLUSTERED ( CustomerTransactionID );'; EXECUTE (@SQL); SET @SQL = N' CREATE CLUSTERED INDEX CX_Sales_CustomerTransactions ON Sales.CustomerTransactions ( TransactionDate ) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Sales_CustomerTransactions_CustomerID ON Sales.CustomerTransactions ( CustomerID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Sales_CustomerTransactions_InvoiceID ON Sales.CustomerTransactions ( InvoiceID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Sales_CustomerTransactions_PaymentMethodID ON Sales.CustomerTransactions ( PaymentMethodID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Sales_CustomerTransactions_TransactionTypeID ON Sales.CustomerTransactions ( TransactionTypeID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX IX_Sales_CustomerTransactions_IsFinalized ON Sales.CustomerTransactions ( IsFinalized ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); END; IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'CX_Purchasing_SupplierTransactions') BEGIN SET @SQL = N' ALTER TABLE Purchasing.SupplierTransactions DROP CONSTRAINT PK_Purchasing_SupplierTransactions;'; EXECUTE (@SQL); SET @SQL = N' ALTER TABLE Purchasing.SupplierTransactions ADD CONSTRAINT PK_Purchasing_SupplierTransactions PRIMARY KEY NONCLUSTERED ( SupplierTransactionID );'; EXECUTE (@SQL); SET @SQL = N' CREATE CLUSTERED INDEX CX_Purchasing_SupplierTransactions ON Purchasing.SupplierTransactions ( TransactionDate ) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Purchasing_SupplierTransactions_PaymentMethodID ON Purchasing.SupplierTransactions ( PaymentMethodID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Purchasing_SupplierTransactions_PurchaseOrderID ON Purchasing.SupplierTransactions ( PurchaseOrderID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Purchasing_SupplierTransactions_SupplierID ON Purchasing.SupplierTransactions ( SupplierID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX FK_Purchasing_SupplierTransactions_TransactionTypeID ON Purchasing.SupplierTransactions ( TransactionTypeID ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); SET @SQL = N' CREATE INDEX IX_Purchasing_SupplierTransactions_IsFinalized ON Purchasing.SupplierTransactions ( IsFinalized ) WITH (DROP_EXISTING = ON) ON PS_TransactionDate(TransactionDate);'; EXECUTE (@SQL); END; COMMIT; PRINT N'Partitioning successfully enabled'; END; END; |
Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51