Download - Table Partitioning document - New.docx
-
7/27/2019 Table Partitioning document - New.docx
1/36
The Sliding Window Scenario
The sliding window scenario is a method for aging old data out and bringing new data into apartitioned table on a periodic basis. Using a time-based or date-based partitioned table, thetechnique employs the SWITCH option of ALTER TABLE, along with the MERGE and SPLIT
operations of ALTER PARTITION FUNCTION, to move the table's partitions forward in time onepartition at a time, leaving the overall number of partitions the same
The key feature of a sliding window scenario is that the partitions represent time linearly, andthe window of time slides forward period by period, keeping the overall number of partitions inthe partitioned table constant. The window moves forward partition by partition, reflecting theperiod of time chosen by the boundary values of the partition function.
Sliding Window Steps
In general, a sliding window consists of the following steps:
1. Create a nonpartitioned archive table with the same structure, and a matching clusteredindex (if required). Optionally you can add matching secondary indexes and constraints.
Place it on the same filegroup as the oldest partition.
2. Use SWITCH to move the oldest partition from the partitioned table to the archive table.
3. Remove the boundary value of the oldest partition using MERGE.
4. Designate the NEXT USED filegroup.
5. Create a new boundary value for the new partition using SPLIT (the best practice is tosplit an empty partition at the leading end of the table into two empty partitions tominimize data movement.).
6. Create a staging table that has the same structure as the partitioned table on the targetfilegroup.
7. Populate the staging table.
8. Add indexes.
9. Add a check constraint that matches the constraint of the new partition.
10. Ensure that all indexes are aligned.
11. Switch the newest data into the partitioned table (the staging table is now empty).
12. Update statistics on the partitioned table.
In addition, to protect the partitioned table from allowing any data values from beyond thedesired boundary range, a check constraint over the entire boundary range can be applied.
There are a number of things you can do to make the sliding window scenario as efficient aspossible:
Minimize data movement by using SPLIT and MERGE on empty partitions. Switching
out the oldest data partition and leaving an empty partition in its place allows you tomerge an empty partition. The same is true when you use the SPLIT operation to createthe partition for the newest data: It is initially an empty partition, so the SPLIT operation
does not cause any data movement.
Create the staging table for new data as initially a heap, load the data, and then buildindexes (and indexed views, if any) as a preparation for switching.
Update statistics on the table after every partition switching cycle (because statistics arekept at the table level, this operation can be time-consuming and resource intensive).
-
7/27/2019 Table Partitioning document - New.docx
2/36
In a sliding window scenario, the oldest data switched out of the partition can be archived ordeleted, depending on business requirements. We'll deal with archiving scenarios in the nextsection.
-
7/27/2019 Table Partitioning document - New.docx
3/36
Partitioning analysis on UNFI
We had found following tables having high number of rows and the best candidates for the partitioning:
Database: East Datamart
Tables Row Count File Group
tblOrders 18181943
East_Group
tblLines 894524104
tblCredit 20948720
tblReceivedPOheader 1761872
tblReceivedPOline 15292957
tblInventoryHistory 873059714
tblEDLCDetails 8066270
tblAcctsRcvTxnHistory 40893592
tblRebateHistory 139405538
tblMCB_History 77320823
tblMCBOverpullResultsAuditByDivi
sion98042526
Database: National Datamart
Tables Row Count FileGroup
tblMPWDailyOrderHeader 13402588
National_Group
tblMPWDailyOrderLines 557382636
tblMPWDailyCreditHeader 1801026
tblMPWDailyCreditLines 9064997
tblMPWDailyOrderLines_Remarks 14292975
tbl_MPW_MCBLines 57890065
tbl_MPW_ProductCostPromoAllo
wances2672353
tbl_MPW_POHeader 2420328
Proposed Partition Schemes
The proposed partition schema is Horizontal Range Base Partition based on Index.
1. One Partition Function for Clustered Indexed Columns
2. One Partition Function for Non Clustered Indexed Columns (if Required).
-
7/27/2019 Table Partitioning document - New.docx
4/36
Advantages related to Horizontal Range Base Partition already discussed above.
UNFI Partitioning Objects Scope & Impacts
Please find below the analysis on partition columns of above mentioned tables.
Data Compression for Previous Partition Data:
Table Compression:
ALTERTABLEREBUILDPARTITION=1WITH (DATA_COMPRESSION = PAGE)GO
Index Compression:
ALTERINDEX IX_TransactionHistory_ProductID ON Production.TransactionHistoryREBUILDPARTITION=1WITH (DATA_COMPRESSION =PAGE);GO
List of Compression Tables / Indexes:
---Get List of Compression Tables---
USE [East_Datmart]SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
[p].[data_compression_desc] AS [Compression]FROM [sys].[partitions] AS [p]INNERJOINsys.tablesAS [t] ON [t].[object_id] = [p].[object_id]WHERE[p].[data_compression_desc] 'NONE'
---Get List of Compression Indexes---
USE [East_Datmart]SELECT [t].[name] AS [Table], [i].[name] AS [Index],
[p].[partition_number] AS [Partition],[p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]INNERJOINsys.tablesAS [t] ON [t].[object_id] = [p].[object_id]
INNERJOINsys.indexesAS [i] ON [i].[object_id] = [p].[object_id]WHERE
[p].[data_compression_desc] 'NONE'
TblOrders
-
7/27/2019 Table Partitioning document - New.docx
5/36
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP TblOrders_FG1GO
ALTERDATABASE EAST_DatamartADDFILE(NAME =TblOrders_FG_DF01,FILENAME='M:\TblOrders_FG_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblOrders_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_DatamartADD FILEGROUP TblOrders_FG2GO
ALTERDATABASE EAST_DatamartADDFILE(NAME =TblOrders_FG_DF02,FILENAME='M:\TblOrders_FG_DF02.mdf',
SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblOrders_FG2GO
Column need partitioning details tblOrders
Table Name Candidate column for Partitioning Filegroup DataFile
TblOrders DeliveryDate East_Group TblOrders_df
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
-
7/27/2019 Table Partitioning document - New.docx
6/36
CREATEPARTITION SCHEME TblOrders_PSASPARTITION East_PFTO (TblOrders_FG1,TblOrders_FG2)GO
Indexes on Table ( TblOrders)
Index Name Index Description Index Keys
idx_InvNUm nonclustered located on PRIMARY InvoiceNumber
idx_InvoiceDate nonclustered located on PRIMARY InvoiceDate
idx_RptDte nonclustered located on PRIMARY ReportingDate
tblOrder_Index_3 nonclustered located on PRIMARY DateLoaded
tblOrders_Index_1 clustered, ignore duplicate keys, unique located on PRIMARY OrderNumber, Delivery
tblOrders_Index_2 nonclustered located on PRIMARY CustomerNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
Data Analysis Pattern (2013) on tblOrders
TblOrdersYears Records
2013 587763
TblLines
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP TblLines_FG1
-
7/27/2019 Table Partitioning document - New.docx
7/36
GO
ALTERDATABASE EAST_DatamartADDFILE(NAME =TblLines_DF01,FILENAME='M:\TblLines_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblLines_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_DatamartADD FILEGROUP TblLines_FG2GO
ALTERDATABASE EAST_DatamartADDFILE(NAME =TblLines_DF02,FILENAME='M:\TblLines_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblLines_FG2GO
Column need partitioning details TblLines
Table Name Candidate column for Partitioning Filegroup DataFileTblLines DeliveryDate East_Group TblLines_df
Data Analysis Pattern (2013) on TblLines
TblLinesYears Records
2013 30013995
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME TblLines_PS
-
7/27/2019 Table Partitioning document - New.docx
8/36
ASPARTITION East_PFTO (TblLines_FG1, TblLines_FG2)GO
Indexes on Table ( TblLines)
Index Name Index Description Index Keys
ix_prd nonclustered located on PRIMARY ProductNumber
tblLines_Ind
ex_1
clustered, ignore duplicate keys, unique
located on PRIMARY
OrderNumber, DeliveryDate,
LineSequenceNumber
tblLines_Ind
ex_2 nonclustered located on PRIMARY DateLoaded
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
TblCredit
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP TblCredit_FG1GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =TblCredit_DF01,FILENAME='M:\TblCredit_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblCredit_FG1GO
-
7/27/2019 Table Partitioning document - New.docx
9/36
Files for latest data (2013):
ALTER DATABASE EAST_DatamartADD FILEGROUP TblCredit_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =TblCredit_DF02,FILENAME='M:\TblCredit_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblCredit_FG2GO
Column need partitioning details TblCredit
Table Name Candidate column for Partitioning Filegroup DataFile
TblCredit ReleaseDate East_Group TblCredit_df
Data Analysis Pattern (2013) on TblCredit
TblCredit
Year Records
2013 700284
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME TblCredit_PS
ASPARTITION East_PFTO (TblCredit_FG1, TblCredit_FG2)GO
Indexes on Table ( TblCredit)
Index Name Index Description Index Keys
-
7/27/2019 Table Partitioning document - New.docx
10/36
Idx_RelDteCredN
umLinSeqMsgSeq
clustered, ignore duplicate
keys, unique located on
PRIMARY
ReleaseDate, CustomerNumber, CreditNumber,
LineSequenceNumber, MessageSequenceNumber
ix_applyTo
nonclustered located on
PRIMARY ApplyToInvoiceNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
TblReceivedPOheader
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP TblReceivedPOheader_FG1GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =TblReceivedPOheader_DF01,FILENAME='M:\TblReceivedPOheader_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblReceivedPOheader_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_DatamartADD FILEGROUP TblReceivedPOheader_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =TblReceivedPOheader_DF01,
-
7/27/2019 Table Partitioning document - New.docx
11/36
FILENAME='M:\TblReceivedPOheader_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblReceivedPOheader_FG2GO
Column need partitioning details TblReceivedPOheader
Table Name Candidate column for Partitioning Filegroup DataFile
TblReceivedPOheader ReceivedDate East_Group TblReceivedPOheader_df
Data Analysis Pattern (2013) on TblReceivedPOheader
TblReceivedPOheader
Year Records
2013 56696
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME TblReceivedPOheader_PSASPARTITION East_PFTO (TblReceivedPOheader_FG1, TblReceivedPOheader_FG2)GO
Indexes on Table ( TblReceivedPOheader)
Index Name Index Description Index Keys
idx_vend nonclustered located on PRIMARY VendorNumber
tblReceivedPOheader_Ind
ex_1
clustered, ignore duplicate keys, unique located on
PRIMARY
ReceivedDate,
PONumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
-
7/27/2019 Table Partitioning document - New.docx
12/36
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
TblReceivedPOline
File Groups and Data Files DistributionFiles for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP TblReceivedPOline_FG1GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =TblReceivedPOline_DF01,FILENAME='M:\TblReceivedPOline_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblReceivedPOline_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_Datamart
ADD FILEGROUP TblReceivedPOline_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =TblReceivedPOline_DF02,FILENAME='M:\TblReceivedPOline_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP TblReceivedPOline_FG2GO
Column need partitioning details TblReceivedPOline
Table Name Candidate column for Partitioning Filegroup DataFile
TblReceivedPOline ReceivedDate East_Group TblReceivedPOline_df
-
7/27/2019 Table Partitioning document - New.docx
13/36
Data Analysis Pattern (2013) on TblReceivedPOline
TblReceivedPOlineYear Records
2013 477652
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME TblReceivedPOline_PSASPARTITION East_PFTO (TblReceivedPOline_FG1, TblReceivedPOline_FG2)GO
Indexes on Table ( TblReceivedPOline)
Index Name Index Description Index Keys
ix_entryDate nonclustered located on PRIMARY EntryDate
ix_vendor nonclustered located on PRIMARY VendorNumber
tblReceivedPOline_I
ndex_1
clustered, ignore duplicate keys, unique
located on PRIMARY
ReceivedDate, PONumber,
POSequenceNumber
tblReceivedPOline_I
ndex_2 nonclustered located on PRIMARY
WarehouseNumber,
ProductNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
TblInventoryHistory
File Groups and Data Files Distribution
Files for Previous data:
-
7/27/2019 Table Partitioning document - New.docx
14/36
ALTER DATABASE EAST_DatamartADD FILEGROUP tblInventoryHistory_FG1GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblInventoryHistory_DF01,FILENAME='M:\tblInventoryHistory_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblInventoryHistory_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_DatamartADD FILEGROUP tblInventoryHistory_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblInventoryHistory_DF02,FILENAME='M:\tblInventoryHistory_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblInventoryHistory_FG2
GO
Column need partitioning details TblInventoryHistory
Table Name Candidate column for Partitioning Filegroup DataFile
tblInventoryHistory ReceivedDate East_Group tblInventoryHistory_df
Data Analysis Pattern (1-Months) on TblInventoryHistory
tblInventoryHistoryMonth Records
2013-Apr 12453482
Partition Function and partition Scheme
-
7/27/2019 Table Partitioning document - New.docx
15/36
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblInventoryHistory_PSASPARTITION East_PFTO (tblInventoryHistory_FG1, tblInventoryHistory_FG2)GO
Indexes on Table ( TblInventoryHistory)
Index Name Index Description Index Keys
PK_tblInventory
History
nonclustered, unique, primary key
located on PRIMARY
WHPDLoadDate, Region, Warehouse,
ProductNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
TblAcctsRcvTxnHistory
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP tblAcctsRcvTxnHistory_FG1
GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblAcctsRcvTxnHistory_DF01,FILENAME='M:\tblAcctsRcvTxnHistory_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
-
7/27/2019 Table Partitioning document - New.docx
16/36
TO FILEGROUP tblAcctsRcvTxnHistory_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_DatamartADD FILEGROUP tblAcctsRcvTxnHistory_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblAcctsRcvTxnHistory_DF02,FILENAME='M:\tblAcctsRcvTxnHistory_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblAcctsRcvTxnHistory_FG2
GO
Column need partitioning details TtblAcctsRcvTxnHistory
Table Name Candidate column for Partitioning Filegroup DataFile
tblAcctsRcvTxnHistory ARDatePosted East_Group tblAcctsRcvTxnHistory_df
Data Analysis Pattern (2013) on TtblAcctsRcvTxnHistory
tblAcctsRcvTxnHistoryYear Records
2013 2098065
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFOR
VALUES ('20130101');GO
CREATEPARTITION SCHEME tblAcctsRcvTxnHistory_PSASPARTITION East_PFTO (tblAcctsRcvTxnHistory_FG1, tblAcctsRcvTxnHistory_FG2)GO
Indexes on Table ( TtblAcctsRcvTxnHistory)
-
7/27/2019 Table Partitioning document - New.docx
17/36
Index Name Index Description Index Keys
ix_customer nonclustered located on PRIMARY CustomerNumber
tblAcctsRcvTxnHistory_Index_1 clustered located on PRIMARY ARDatePosted
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
TtblRebateHistory
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP tblRebateHistory_FG1GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblRebateHistory_DF01,FILENAME='M:\tblRebateHistory_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblRebateHistory_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_Datamart
ADD FILEGROUP tblRebateHistory_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblRebateHistory_DF02,FILENAME='M:\tblRebateHistory_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB
-
7/27/2019 Table Partitioning document - New.docx
18/36
)TO FILEGROUP tblRebateHistory_FG2GO
Column need partitioning details TblRebateHistory
Table Name Candidate column for Partitioning Filegroup DataFile
tblRebateHistory DeliveryDate East_Group tblRebateHistory_df
Data Analysis Pattern (2013) on TblRebateHistory
tblRebateHistory
Year Records
2013
5638660
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblRebateHistory_PSASPARTITION East_PFTO (tblRebateHistory_FG1, tblRebateHistory_FG2)GO
Indexes on Table ( TblRebateHistory)
Index Name Index Description Index Keys
tblRebateHisto
ry_Index_1
clustered, ignore duplicate keys,
unique located on PRIMARY
InvoiceDate, InvoiceNumber,
LineSequenceNumber,
RebateHistorySequenceNumber
tblRebateHistory_Index_2 nonclustered located on PRIMARY
OrderNumber, DeliveryDate,
LineSequenceNumber,RebateHistorySequenceNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
-
7/27/2019 Table Partitioning document - New.docx
19/36
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
TblMCB_History
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_DatamartADD FILEGROUP tblMCB_History_FG1GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblMCB_History_DF01,FILENAME='M:\tblMCB_History_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMCB_History_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_DatamartADD FILEGROUP tblMCB_History_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME =tblMCB_History_DF02,FILENAME='M:\tblMCB_History_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMCB_History_FG2
GO
Column need partitioning details TblMCB_History
Table Name Candidate column for Partitioning Filegroup DataFile
tblMCB_History ExtractDate East_Group tblMCB_History_df
-
7/27/2019 Table Partitioning document - New.docx
20/36
Data Analysis Pattern (2013) on TblMCB_History
tblMCB_History
Year Records
2013
4707746
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblMCB_History_PSASPARTITION East_PFTO (tblMCB_History_FG1, tblMCB_History_FG2)GO
Indexes on Table ( TblMCB_History)
Index Name Index Description Index Keys
idx_PeriodEnd_RemitNum clustered located on PRIMARY PeriodEnd, RemitNum
ix_Inv nonclustered located on PRIMARY InvoiceNumberix_prd nonclustered located on PRIMARY ProductNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name; Alter Table Add constraint using New file group.
TblMCBOverpullResultsAuditByDivision
-
7/27/2019 Table Partitioning document - New.docx
21/36
File Groups and Data Files Distribution
Files for Previous data:
ALTER DATABASE EAST_Datamart
ADD FILEGROUP tblMCBOverpullResultsAuditByDivision_FG1GO
ALTERDATABASE EAST_Datamart
ADDFILE(NAME = tblMPWDailyOrderHeader_DF01,FILENAME='M:\tblMCBOverpullResultsAuditByDivision_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
TO FILEGROUP tblMCBOverpullResultsAuditByDivision_FG1GO
Files for latest data (2013):
ALTER DATABASE EAST_Datamart
ADD FILEGROUP tblMCBOverpullResultsAuditByDivision_FG2GO
ALTERDATABASE EAST_Datamart
ADDFILE
(NAME = tblMPWDailyOrderHeader_DF02,FILENAME='M:\tblMCBOverpullResultsAuditByDivision_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
TO FILEGROUP tblMCBOverpullResultsAuditByDivision_FG2GO
Column need partitioning tblMCBOverpullResultsAuditByDivision
Table NameCandidate column for
Partitioning
Filegro
up
DataFile
tblMCBOverpullResultsAuditB
yDivisionDeliverydate
East_Gro
uptblMCBOverpullResultsAuditByD
ivision_df
Data Analysis Pattern (36-Months) ontblMCBOverpullResultsAuditByDivision
tblMCBOverpullResultsAuditByDivisio Year Records
-
7/27/2019 Table Partitioning document - New.docx
22/36
n
2013
9060267
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblMCBOverpullResultsAuditByDivision_PSASPARTITION East_PFTO (tblMCBOverpullResultsAuditByDivision_FG1,tblMCBOverpullResultsAuditByDivision_FG2)GO
Indexes on Table (tblMCBOverpullResultsAuditByDivision)Index Name Index Description Index Keys
idx_MCBOverpullResultsAuditB
yDivision
clustered located on
PRIMARY
Division, PricingYear, CalendarMonth,
ProductNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
tblMPWDailyOrderHeader
File Groups and Data Files Distribution
Files for Previous data:
ALTERDATABASE National_DatamartADD FILEGROUP tblMPWDailyOrderHeader_FG1GO
ALTERDATABASE National_Datamart
ADDFILE(NAME = tblMPWDailyOrderHeader_DF01,
-
7/27/2019 Table Partitioning document - New.docx
23/36
FILENAME='M:\tblMPWDailyOrderHeader_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMPWDailyOrderHeader_FG1GO
Files for latest data (2013):
ALTERDATABASE National_DatamartADD FILEGROUP tblMPWDailyOrderHeader_FG2GO
ALTERDATABASE National_Datamart
ADDFILE(NAME = tblMPWDailyOrderHeader_DF02,FILENAME='M:\tblMPWDailyOrderHeader_DF02.mdf',
SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMPWDailyOrderHeader_FG2GO
Column need partitioning details tblMPWDailyOrderHeader
Table NameCandidate column for
Partitioning
Filegroup DataFile
tblMPWDailyOrderHead
erInvoiceDate
National_Grou
ptblMPWDailyOrderHeader_
df
Data Analysis Pattern (2013) on tblMPWDailyOrderHeader
tblMPWDailyOrderHeaderyear Records
2013 639244
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblMPWDailyOrderHeader_PSASPARTITION East_PFTO (tblMPWDailyOrderHeader_FG1, tblMPWDailyOrderHeader_FG2)GO
-
7/27/2019 Table Partitioning document - New.docx
24/36
Indexes on Table (tblMPWDailyOrderHeader)
index_name index_description index_keys
idx_Cust nonclustered located on PRIMARY CustAccount
idx_DateLoaded nonclustered located on PRIMARY DateLoaded
idx_MPWWhse nonclustered located on PRIMARY MPWWarehouseNumber
idx_OrderType nonclustered located on PRIMARY OrderType
idx_territory nonclustered located on PRIMARY Territory
ix_RqstDte nonclustered located on PRIMARY RequestDate
PK_tblMPWDailyOrde
rHeader
clustered, unique, primary key
located on PRIMARY
InvoiceDate, OrderNumber,
OrderSequenceNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
tblMPWDailyOrderLines
File Groups and Data Files Distribution
Files for Previous data:
ALTERDATABASE National_Datamart ADD FILEGROUP tblMPWDailyOrderLines_FG1GO
ALTERDATABASE National_DatamartADDFILE(
NAME = tblMPWDailyOrderLines_DF01,FILENAME='M:\tblMPWDailyOrderLines_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMPWDailyOrderLines_FG1GO
-
7/27/2019 Table Partitioning document - New.docx
25/36
Files for latest data (2013):
ALTERDATABASE National_Datamart ADD FILEGROUP tblMPWDailyOrderLines_FG2GO
ALTERDATABASE National_Datamart
ADDFILE(NAME = tblMPWDailyOrderLines_DF02,FILENAME='M:\tblMPWDailyOrderLines_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMPWDailyOrderLines_FG2GO
Column need partitioning details tblMPWDailyOrderLines
Table NameCandidate column for
Partitioning
Filegroup DataFile
tblMPWDailyOrderLine
sInvoiceDate
National_Grou
ptblMPWDailyOrderLines_d
f
Data Analysis Pattern (2013) on tblMPWDailyOrderLines
tblMPWDailyOrderLinesyear Records
2013 22745231
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblMPWDailyOrderLines_PSASPARTITION East_PFTO (tblMPWDailyOrderLines_FG1, tblMPWDailyOrderLines_FG2)GO
Indexes on Table (tblMPWDailyOrderLines)
index_name index_description index_keys
idx_CustAccount
nonclustered located on
PRIMARY CustAccount
idx_DateLoaded
nonclustered located on
PRIMARY DateLoaded
-
7/27/2019 Table Partitioning document - New.docx
26/36
idx_MPWWhse
nonclustered located on
PRIMARY MPWWarehouseNumber
idx_ProductNum
ber
nonclustered located on
PRIMARY ProductNumber
idx_UPC
nonclustered located on
PRIMARY UPC
PK_tblMPWDaily
OrderLines
clustered, unique, primary key
located on PRIMARY
InvoiceDate, OrderNumber,
OrderSequenceNumber, LineSeqeunceNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
tblMPWDailyCreditHeader
File Groups and Data Files Distribution
Files for Previous data:
ALTERDATABASE National_Datamart ADD FILEGROUP tblMPWDailyCreditHeader_FG1GO
ALTERDATABASE National_DatamartADDFILE(NAME = tblMPWDailyCreditHeader_DF01,FILENAME='M:\tblMPWDailyCreditHeader_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
TO FILEGROUP tblMPWDailyCreditHeader_FG1GO
Files for latest data (2013):
ALTERDATABASE National_Datamart ADD FILEGROUP tblMPWDailyCreditHeader_FG2GO
ALTERDATABASE National_DatamartADDFILE(
-
7/27/2019 Table Partitioning document - New.docx
27/36
NAME = tblMPWDailyCreditHeader_DF02,FILENAME='M:\tblMPWDailyCreditHeader_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
TO FILEGROUP tblMPWDailyCreditHeader_FG2GO
Column need partitioning details tblMPWDailyCreditHeader
Table NameCandidate column for
Partitioning
Filegroup DataFile
tblMPWDailyCreditHead
erInvoiceDate
National_Grou
ptblMPWDailyCreditHeader_
df
Data Analysis Pattern (2013) on
tblMPWDailyCreditHeader
tblMPWDailyCreditHeaderyear Records
2013 75688
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');
GO
CREATEPARTITION SCHEME tblMPWDailyCreditHeader_PSASPARTITION East_PFTO (tblMPWDailyCreditHeader_FG1, tblMPWDailyCreditHeader_FG2)GO
Indexes on Table (tblMPWDailyCreditHeader)
index_name index_description index_keys
idx_CustAccount nonclustered located on PRIMARY CustAccount
idx_dteLoad nonclustered located on PRIMARY DateLoaded
PK_tblMPWDailyCredi
tHeader
clustered, unique, primary key
located on PRIMARY
InvoiceDate, OrderNumber,
OrderSequenceNumber
Partitioned Aligned Indexes:
-
7/27/2019 Table Partitioning document - New.docx
28/36
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
tblMPWDailyCreditLines
File Groups and Data Files Distribution
Files for Previous data:
ALTERDATABASE National_Datamart ADD FILEGROUP tblMPWDailyCreditLines_FG1GO
ALTERDATABASE National_DatamartADDFILE(NAME = tblMPWDailyCreditLines_DF01,FILENAME='M:\tblMPWDailyCreditLines_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
TO FILEGROUP tblMPWDailyCreditLines_FG1GO
Files for latest data (2013):
ALTERDATABASE National_Datamart ADD FILEGROUP tblMPWDailyCreditLines_FG2GO
ALTERDATABASE National_DatamartADDFILE(NAME = tblMPWDailyCreditLines_DF02,FILENAME='M:\tblMPWDailyCreditLines_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
TO FILEGROUP tblMPWDailyCreditLines_FG2GO
Column need partitioning details tblMPWDailyCreditLines
Table Name Candidate column for Filegroup DataFile
-
7/27/2019 Table Partitioning document - New.docx
29/36
Partitioning
tblMPWDailyCreditLine
sInvoiceDate
National_Grou
ptblMPWDailyCreditLines_d
f
Data Analysis Pattern (2013) on tblMPWDailyCreditLines
tblMPWDailyCreditLinesyear Records
2013 375652
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblMPWDailyCreditLines_PSASPARTITION East_PF
TO (tblMPWDailyCreditLines_FG1, tblMPWDailyCreditLines_FG2)GO
Indexes on Table (tblMPWDailyCreditLines)
index_name index_description index_keys
idx_CustAccount nonclustered located onPRIMARY CustAccount
idx_DteLd
nonclustered located on
PRIMARY DateLoaded
idx_MPWWareho
useNumber
nonclustered located on
PRIMARY MPWWarehouseNumber
idx_ProductNumb
er
nonclustered located on
PRIMARY ProductNumber
idx_UPC
nonclustered located on
PRIMARY UPC
PK_tblMPWDailyC
reditLines
clustered, unique, primary key
located on PRIMARY
InvoiceDate, OrderNumber,
OrderSequenceNumber, LineSeqeunceNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
-
7/27/2019 Table Partitioning document - New.docx
30/36
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
tblMPWDailyOrderLines_RemarksFile Groups and Data Files Distribution
Files for Previous data:
ALTERDATABASE National_Datamart ADD FILEGROUPtblMPWDailyOrderLines_Remarks_FG1GO
ALTERDATABASE National_Datamart
ADDFILE(NAME = tblMPWDailyOrderLines_Remarks_DF01,FILENAME='M:\tblMPWDailyOrderLines_Remarks_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMPWDailyOrderLines_Remarks_FG1GO
Files for latest data (2013):
ALTERDATABASE National_Datamart ADD FILEGROUPtblMPWDailyOrderLines_Remarks_FG2GO
ALTERDATABASE National_DatamartADDFILE(NAME = tblMPWDailyOrderLines_Remarks_DF02,FILENAME='M:\tblMPWDailyOrderLines_Remarks_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tblMPWDailyOrderLines_Remarks_FG2GO
Column needs partitioning detailstblMPWDailyOrderLines_Remarks
Table NameCandidate column for
Partitioning
Filegroup DataFile
tblMPWDailyOrderLines_R
emarksInvoiceDate
National_Gr
ouptblMPWDailyOrderLines_Rem
arks_df
-
7/27/2019 Table Partitioning document - New.docx
31/36
Data Analysis Pattern (2013) on tblMPWDailyOrderLines_Remarks
tblMPWDailyOrderLines_Remarksyear Records
2013 679017
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tblMPWDailyOrderLines_Remarks_PSASPARTITION East_PFTO (tblMPWDailyOrderLines_Remarks_FG1, tblMPWDailyOrderLines_Remarks_FG2)
GO
Indexes on Table (tblMPWDailyOrderLines_Remarks)
index_nam
e index_description index_keys
idx_cust
nonclustered located on
PRIMARY CustAccount
idx_ordSeq
Dte
clustered located on
PRIMARY
OrderNumber, OrderSequenceNumber, InvoiceDate,
LineSequenceNumber
idx_origPro
d
nonclustered located on
PRIMARY OriginalProductOrdered
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
tbl_MPW_MCBLines
File Groups and Data Files Distribution
-
7/27/2019 Table Partitioning document - New.docx
32/36
Files for Previous data:
ALTERDATABASE National_Datamart ADD FILEGROUP tbl_MPW_MCBLines_FG1GO
ALTERDATABASE National_DatamartADDFILE(NAME = tbl_MPW_MCBLines_DF01,FILENAME='M:\tbl_MPW_MCBLines_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tbl_MPW_MCBLines_FG1GO
Files for latest data (2013):
ALTERDATABASE National_Datamart ADD FILEGROUP tbl_MPW_MCBLines_FG2GO
ALTERDATABASE National_DatamartADDFILE(NAME = tbl_MPW_MCBLines_DF02,FILENAME='M:\tbl_MPW_MCBLines_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tbl_MPW_MCBLines_FG2GO
Column need partitioning details tbl_MPW_MCBLines
Table Name Candidate column for Partitioning Filegroup DataFile
tbl_MPW_MCBLines Shipdate National_Group tbl_MPW_MCBLines_df
Data Analysis Pattern (2013) on tbl_MPW_MCBLines
tbl_MPW_MCBLinesyear Records
2013 3027811
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
-
7/27/2019 Table Partitioning document - New.docx
33/36
CREATEPARTITION SCHEME tbl_MPW_MCBLines_PSASPARTITION East_PFTO (tbl_MPW_MCBLines_FG1, tbl_MPW_MCBLines_FG2)GO
Indexes on Table (tbl_MPW_MCBLines)
index_name index_description index_keys
idx_Cust nonclustered located on PRIMARY CustomerNumber
idx_DateLoaded nonclustered located on PRIMARY DateLoaded
idx_OrdnumSeqnu
mLineKey
clustered, ignore duplicate keys, unique
located on PRIMARY
OrderNumber,
OrderSequenceNumber,
OrderLineKey
idx_Prod nonclustered located on PRIMARY ProductNumber
idx_ShipDate nonclustered located on PRIMARY Shipdateidx_VendorNumber nonclustered located on PRIMARY VendorNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name; Alter Table Add constraint using New file group.
tbl_MPW_ProductCostPromoAllowances
File Groups and Data Files Distribution
Files for Previous data:
ALTERDATABASE National_Datamart ADD FILEGROUP
tbl_MPW_ProductCostPromoAllowances_FG1GO
ALTERDATABASE National_DatamartADDFILE(NAME = tbl_MPW_ProductCostPromoAllowances_DF01,FILENAME='M:\tbl_MPW_ProductCostPromoAllowances_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)
-
7/27/2019 Table Partitioning document - New.docx
34/36
TO FILEGROUP tbl_MPW_ProductCostPromoAllowances_FG1GO
Files for latest data (2013):
ALTERDATABASE National_Datamart ADD FILEGROUPtbl_MPW_ProductCostPromoAllowances_FG2GO
ALTERDATABASE National_DatamartADDFILE(NAME = tbl_MPW_ProductCostPromoAllowances_DF02,FILENAME='M:\tbl_MPW_ProductCostPromoAllowances_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tbl_MPW_ProductCostPromoAllowances_FG2GO
Column need partitioning tbl_MPW_ProductCostPromoAllowance
Table NameCandidate column for
Partitioning
Filegroup DataFile
tbl_MPW_ProductCostPromo
AllowancesPricePromoEndDate
National_G
rouptbl_MPW_ProductCostPromoAll
owances_df
Data Analysis Pattern (2013) on tbl_MPW_ProductCostPromoAllowance
tbl_MPW_ProductCostPromoAllowances year Records2013 245153
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFORVALUES ('20130101');GO
CREATEPARTITION SCHEME tbl_MPW_ProductCostPromoAllowances_PS
ASPARTITION East_PFTO (tbl_MPW_ProductCostPromoAllowances_FG1,tbl_MPW_ProductCostPromoAllowances_FG2)GO
Indexes on Table (tbl_MPW_ProductCostPromoAllowances)
index_name index_description index_keys
-
7/27/2019 Table Partitioning document - New.docx
35/36
ix_ProdStart
EndTypeRef
clustered, ignore duplicate keys,
unique located on PRIMARY
ProductNumber, CostPromoStartDate,
CostPromoEndDate, PromoType,
CostPromoReferenceNumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using New file group.
tbl_MPW_POHeader
File Groups and Data Files DistributionFiles for Previous data:
ALTERDATABASE National_Datamart ADD FILEGROUP tbl_MPW_POHeader_FG1GO
ALTERDATABASE National_DatamartADDFILE(NAME = tbl_MPW_POHeader_DF01,FILENAME='M:\tbl_MPW_POHeader_DF01.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB
)TO FILEGROUP tbl_MPW_POHeader_FG1GO
Files for latest data (2013):
ALTERDATABASE National_Datamart ADD FILEGROUP tbl_MPW_POHeader_FG2GO
ALTERDATABASE National_DatamartADDFILE(
NAME = tbl_MPW_POHeader_DF02,FILENAME='M:\tbl_MPW_POHeader_DF02.mdf',SIZE = 1024MB,MAXSIZE =UNLIMITED,FILEGROWTH = 500MB)TO FILEGROUP tbl_MPW_POHeader_FG2GO
-
7/27/2019 Table Partitioning document - New.docx
36/36
Column need partitioning tbl_MPW_POHeader
Table Name Candidate column for Partitioning Filegroup DataFile
tbl_MPW_POHeader ReceivedDate National_Group tbl_MPW_POHeader_df
Data Analysis Pattern (2013) on tbl_MPW_POHeader
tbl_MPW_POHeaderyear Records
2013 108554
Partition Function and partition Scheme
CREATEPARTITIONFUNCTION East_PF(datetime)ASRANGEleftFOR
VALUES ('20130101');
GO
CREATEPARTITION SCHEME tbl_MPW_POHeader_PSASPARTITION East_PFTO (tbl_MPW_POHeader_FG1, tbl_MPW_POHeader_FG2)GO
Indexes on Table (tbl_MPW_POHeader)
index_name index_description index_keys
idx_dateloaded nonclustered located on PRIMARY DateLoaded
idx_ReceivedDate nonclustered located on PRIMARY ReceivedDate
idx_Vend nonclustered located on PRIMARY VendorAcctNumber
idx_WhsePOTypeVrf
yDt nonclustered located on PRIMARY
WarehouseOfVendor, POType,
VerifyDate
PK_tbl_MPW_POHe
ader
clustered, unique, primary key located on
PRIMARY PONumber
Partitioned Aligned Indexes:
All non-clustered indexes will be partitioned aligned as per clustered indexes . (required for
sliding window operation)
Clustered index will partition the already created table
Altertable Table_Name; dropconstraint Constraint_Name;
Alter Table Add constraint using new file group.