One moment please...

As of SQL Server 2008 Filtered Indexes are introduced. A filtered index allows us to create an index with a filter on a subset of rows within a table. A filtered index will:

  1. Improve query performance. Statistics are more accurate which can result in better query plans. 
  2. Reduce index maintenance costs. An index is only maintained when the data in the index is changed.
  3. Reduce index storage costs.

Filtered indexes can be very useful on columns which contains mostly NULL values and where the queries retrieve only the rows where the data is NOT NULL.
In our Exact Software we have indexes on columns which contain mostly NULL values.
See Microsoft website link for more information about filtered indexes.

You can use next query to see the allocated space in your database: EXEC sp_spaceused @updateusage = N'TRUE';
To see the difference in index size after implementing the filtered indexes, run this query before and after the filtered index script is executed successfully.

Fill Factor
The attached script will also implement a Fill Factor of 90 for each column of the type uniqueidentifier. This Fill Factor will improve your database performance due to less page splits.
See Microsoft website link for more information about performance impact of the Fill Factor.

If you are planning to execute the script, make sure no users are active in the database while you are executing the script! 
The update process can take about up to 15 minutes for a database size of 30 GB depending on your SQL server hardware specifications.

To implement Filtered Indexes and Fill Factor with SQL Server Management Studio: 

  • Select Start button, Run, SSMS.
    If you do not have installed SQL Server Management Studio, you can install it here.
  • Download the script attached to this document
  • Press the File, Open, File menu and selected the downloaded script ImplementFilteredIndexesFillFactor.sql
  • Connect to your SQL Server. This is the server name which is specified in the improvement report.
  • Select the database which is specified in the improvement report
  • Execute the script by pressing the Execute button.
  • Depending on the index and the size of your database this can take some time to execute.
  • Repeat these steps for every Exact database.

When using SDK based solutions (like E-WMS, Incoming Invoice Register, Field Service, custom solutions, etc.) make sure you use release 401 or newer, otherwise the SDK update script might fail.


If the Filtered Index still cause errors, you can use the attached RemoveFilteredIndexes.sql script to undo the filtered indexes.

With use of the Exact System Information Tool you can easily add filtered indexes to your Exact databases.
On the tab SQL Maintenance you need to select the database and click on the Implement button in the 'Filtered Indexes and Fill Factor' section.




ImplementFilteredIndexesFillFactor.sql 24.6 KB View Download
RemoveFilteredIndexes.sql 14.5 KB View Download