Using Partitioned Tables in Nav

geschwint · 2008-02-07

Partitioned tables/indexes is a new and exiting feature in SQL 2005 that is not used in Nav.

Using partitioned tables in Nav can increase performance when working with BIG tables such as transaction history tables. With a partitioned table, you can define that all history from 2006 and back (inklusive) should be stored on another slower disk, like a fileserver, while keeping the last year including the current on a super fast local RAID10 storage units.

Setting up tables in Nav to use partitioned tables are a little bit tedious since there is no support from within Nav to do this. Following is a code snippet where I used the customer table as the base, for simplicity. Any table could be used in its place. Just remember to include all table objects, thus do not forget Primary Keys, Indexes and Triggers or any other Non-Nav object created on the table.

Partitioned Indexes are not covered in this sample but they behave roughly the same by saving parts of the index in different filegroups depending on how the index is defined and used.

The steps are as follows:

1. Make a backup of the table in question, both definition in SQL Server terms (keys, indexes, triggers, ...), and the actual content, then delete it. Yes, Navision will complain slightly now when running it. The table definition still exists in Navision so you can do an edit but I've never tried to modify and save it in this state.
2. Create a partition function that controls what to make the split on and at most how many splits will be made
3. Add filegroups at your hearts content, I played with 4 extra filegroups in addition to the defaults
4. Add files to the empty filegroups in #3
5. Create a partition scheme that controls what data goes into which filegroup. This step is closely related to #2
6. Recreate the table based on the saved definition in #1. That would include keys, indexes and triggers. Make sure the table and primary key uses the partition scheme
7. Restore data saved in 1.
8. Run the table in Navision.
9. Run a select statement in query analyzer to see that information is really stored in different partitions based on the rule.

Here is the code:

1. I script the table in the management studio and make sure in Tools -> Options: Scripting that keys, indexes and triggers are scripted as well. Then I drop all the keys, indexes and triggers on the backed up table since an object name must be unique within the same database even if tightly connected to one table. Finally I rename the table using an extension of '_bak'

2. Create the partition function

CREATE PARTITION FUNCTION [CRONUS Sverige AB$Customer_PF1] (VARCHAR(20))
AS RANGE RIGHT FOR VALUES ('50000', '10000000', '30000000') ;
GO

3,4. Add the filegroups with their files

ALTER DATABASE NAVISION_40_SP3_2005
ADD FILEGROUP [Data Filegroup 2]
GO

ALTER DATABASE NAVISION_40_SP3_2005
ADD FILEGROUP [Data Filegroup 3]
GO

ALTER DATABASE NAVISION_40_SP3_2005
ADD FILEGROUP [Data Filegroup 4]
GO

ALTER DATABASE NAVISION_40_SP3_2005
ADD FILEGROUP [Data Filegroup 5]
GO


ALTER DATABASE NAVISION_40_SP3_2005
ADD FILE 
(
    NAME = NAVISION_40_SP3_2005_20_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_20_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = NAVISION_40_SP3_2005_21_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_21_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP [Data Filegroup 2]
GO

ALTER DATABASE NAVISION_40_SP3_2005
ADD FILE 
(
    NAME = NAVISION_40_SP3_2005_30_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_30_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = NAVISION_40_SP3_2005_31_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_31_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP [Data Filegroup 3]
GO

ALTER DATABASE NAVISION_40_SP3_2005
ADD FILE 
(
    NAME = NAVISION_40_SP3_2005_40_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_40_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = NAVISION_40_SP3_2005_41_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_41_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP [Data Filegroup 4]
GO

ALTER DATABASE NAVISION_40_SP3_2005
ADD FILE 
(
    NAME = NAVISION_40_SP3_2005_50_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_50_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = NAVISION_40_SP3_2005_51_Data,
    FILENAME = 'C:\NAVISION_40_SP3_2005_51_Data.ndf',
    SIZE = 10MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP [Data Filegroup 5]
GO

5. Create the partition scheme that says where goes what

CREATE PARTITION SCHEME [CRONUS Sverige AB$Customer_PS1]
AS PARTITION [CRONUS Sverige AB$Customer_PF1]
TO ([Data Filegroup 2], [Data Filegroup 3], [Data Filegroup 4], [Data Filegroup 5]) ;
GO

6. Recreate the table with all the objects, this is important to make Nav work properly

CREATE TABLE [dbo].[CRONUS Sverige AB$Customer](
	[timestamp] [timestamp] NOT NULL,
	[No_] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Name] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Search Name] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Name 2] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Address] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Address 2] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[City] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Contact] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Phone No_] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Telex No_] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Our Account No_] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Territory Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Global Dimension 1 Code] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Global Dimension 2 Code] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Chain Name] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Budgeted Amount] [decimal](38, 20) NOT NULL,
	[Credit Limit (LCY)] [decimal](38, 20) NOT NULL,
	[Customer Posting Group] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Currency Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Customer Price Group] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Language Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Statistics Group] [int] NOT NULL,
	[Payment Terms Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Fin_ Charge Terms Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Salesperson Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Shipment Method Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Shipping Agent Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Place of Export] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Invoice Disc_ Code] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Customer Disc_ Group] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Country Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Collection Method] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Amount] [decimal](38, 20) NOT NULL,
	[Blocked] [int] NOT NULL,
	[Invoice Copies] [int] NOT NULL,
	[Last Statement No_] [int] NOT NULL,
	[Print Statements] [tinyint] NOT NULL,
	[Bill-to Customer No_] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Priority] [int] NOT NULL,
	[Payment Method Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Last Date Modified] [datetime] NOT NULL,
	[Application Method] [int] NOT NULL,
	[Prices Including VAT] [tinyint] NOT NULL,
	[Location Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Fax No_] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[Telex Answer Back] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[VAT Registration No_] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Combine Shipments] [tinyint] NOT NULL,
	[Gen_ Bus_ Posting Group] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Picture] [image] NULL,
	[Post Code] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[County] [varchar](30) COLLATE Latin1_General_CI_AS NOT NULL,
	[E-Mail] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
	[Home Page] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
	[Reminder Terms Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[No_ Series] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Tax Area Code] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Tax Liable] [tinyint] NOT NULL,
	[VAT Bus_ Posting Group] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Reserve] [int] NOT NULL,
	[Block Payment Tolerance] [tinyint] NOT NULL,
	[IC Partner Code] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Primary Contact No_] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
	[Responsibility Center] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Shipping Advice] [int] NOT NULL,
	[Shipping Time] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,
	[Shipping Agent Service Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Service Zone Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Notification Process Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Queue Priority] [int] NOT NULL,
	[Allow Line Disc_] [tinyint] NOT NULL,
	[Base Calendar Code] [varchar](10) COLLATE Latin1_General_CI_AS NOT NULL,
	[Copy Sell-to Addr_ to Qte From] [int] NOT NULL,
	[Postal Giro No_] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [CRONUS Sverige AB$Customer$0] PRIMARY KEY CLUSTERED 
(
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) ON [CRONUS Sverige AB$Customer_PS1] (No_)
) ON [CRONUS Sverige AB$Customer_PS1] (No_)
GO

CREATE UNIQUE NONCLUSTERED INDEX [$1] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[Search Name] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [$2] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[Customer Posting Group] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [$3] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[Currency Code] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [$4] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[Country Code] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [$5] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[Gen_ Bus_ Posting Group] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [$6] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[Name] ASC,
	[Address] ASC,
	[City] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [$7] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[VAT Registration No_] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

CREATE UNIQUE NONCLUSTERED INDEX [$8] ON [dbo].[CRONUS Sverige AB$Customer] 
(
	[Salesperson Code] ASC,
	[No_] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [Data Filegroup 1]
GO

7. Reload the data. This step depends on how you saved the data, I used the rename way

INSERT INTO [dbo].[CRONUS Sverige AB$Customer] (
[No_]
      ,[Name]
      ,[Search Name]
      ,[Name 2]
      ,[Address]
      ,[Address 2]
      ,[City]
      ,[Contact]
      ,[Phone No_]
      ,[Telex No_]
      ,[Our Account No_]
      ,[Territory Code]
      ,[Global Dimension 1 Code]
      ,[Global Dimension 2 Code]
      ,[Chain Name]
      ,[Budgeted Amount]
      ,[Credit Limit (LCY)]
      ,[Customer Posting Group]
      ,[Currency Code]
      ,[Customer Price Group]
      ,[Language Code]
      ,[Statistics Group]
      ,[Payment Terms Code]
      ,[Fin_ Charge Terms Code]
      ,[Salesperson Code]
      ,[Shipment Method Code]
      ,[Shipping Agent Code]
      ,[Place of Export]
      ,[Invoice Disc_ Code]
      ,[Customer Disc_ Group]
      ,[Country Code]
      ,[Collection Method]
      ,[Amount]
      ,[Blocked]
      ,[Invoice Copies]
      ,[Last Statement No_]
      ,[Print Statements]
      ,[Bill-to Customer No_]
      ,[Priority]
      ,[Payment Method Code]
      ,[Last Date Modified]
      ,[Application Method]
      ,[Prices Including VAT]
      ,[Location Code]
      ,[Fax No_]
      ,[Telex Answer Back]
      ,[VAT Registration No_]
      ,[Combine Shipments]
      ,[Gen_ Bus_ Posting Group]
      ,[Picture]
      ,[Post Code]
      ,[County]
      ,[E-Mail]
      ,[Home Page]
      ,[Reminder Terms Code]
      ,[No_ Series]
      ,[Tax Area Code]
      ,[Tax Liable]
      ,[VAT Bus_ Posting Group]
      ,[Reserve]
      ,[Block Payment Tolerance]
      ,[IC Partner Code]
      ,[Primary Contact No_]
      ,[Responsibility Center]
      ,[Shipping Advice]
      ,[Shipping Time]
      ,[Shipping Agent Service Code]
      ,[Service Zone Code]
      ,[Notification Process Code]
      ,[Queue Priority]
      ,[Allow Line Disc_]
      ,[Base Calendar Code]
      ,[Copy Sell-to Addr_ to Qte From]
      ,[Postal Giro No_]
)
SELECT [No_]
      ,[Name]
      ,[Search Name]
      ,[Name 2]
      ,[Address]
      ,[Address 2]
      ,[City]
      ,[Contact]
      ,[Phone No_]
      ,[Telex No_]
      ,[Our Account No_]
      ,[Territory Code]
      ,[Global Dimension 1 Code]
      ,[Global Dimension 2 Code]
      ,[Chain Name]
      ,[Budgeted Amount]
      ,[Credit Limit (LCY)]
      ,[Customer Posting Group]
      ,[Currency Code]
      ,[Customer Price Group]
      ,[Language Code]
      ,[Statistics Group]
      ,[Payment Terms Code]
      ,[Fin_ Charge Terms Code]
      ,[Salesperson Code]
      ,[Shipment Method Code]
      ,[Shipping Agent Code]
      ,[Place of Export]
      ,[Invoice Disc_ Code]
      ,[Customer Disc_ Group]
      ,[Country Code]
      ,[Collection Method]
      ,[Amount]
      ,[Blocked]
      ,[Invoice Copies]
      ,[Last Statement No_]
      ,[Print Statements]
      ,[Bill-to Customer No_]
      ,[Priority]
      ,[Payment Method Code]
      ,[Last Date Modified]
      ,[Application Method]
      ,[Prices Including VAT]
      ,[Location Code]
      ,[Fax No_]
      ,[Telex Answer Back]
      ,[VAT Registration No_]
      ,[Combine Shipments]
      ,[Gen_ Bus_ Posting Group]
      ,[Picture]
      ,[Post Code]
      ,[County]
      ,[E-Mail]
      ,[Home Page]
      ,[Reminder Terms Code]
      ,[No_ Series]
      ,[Tax Area Code]
      ,[Tax Liable]
      ,[VAT Bus_ Posting Group]
      ,[Reserve]
      ,[Block Payment Tolerance]
      ,[IC Partner Code]
      ,[Primary Contact No_]
      ,[Responsibility Center]
      ,[Shipping Advice]
      ,[Shipping Time]
      ,[Shipping Agent Service Code]
      ,[Service Zone Code]
      ,[Notification Process Code]
      ,[Queue Priority]
      ,[Allow Line Disc_]
      ,[Base Calendar Code]
      ,[Copy Sell-to Addr_ to Qte From]
      ,[Postal Giro No_]
FROM dbo.[CRONUS Sverige AB$Customer_bak]
GO

Now you can open the table in Nav and it will behave as nothing happened.

Now validate that the data is saved on different filegroups based on used key

SELECT [Name], [No_], $PARTITION.[CRONUS Sverige AB$Customer_PF1](No_) 'Partition'
FROM dbo.[CRONUS Sverige AB$Customer]

-- With this, you can see on which partition/filegroup the data would go
-- by running the partition function with sample values
SELECT $PARTITION.[CRONUS Sverige AB$Customer_PF1]('50000')

For more details on using partitioned tables, see SQL Server 2005 Books Online.

The above was done using Navision 4.0 SP3 on SQL Server 2005 but the same would work on Nav 5.0.

Also, all names should be renamed to fit your environment.

Tomas · 2008-02-07

Thanks geschwint! Few more things to try while learning :-)

Howdy, Stranger!

Quick Links

Categories

Using Partitioned Tables in Nav

Comments