Filtered Indexes are useful - mostly

Other people have blogged on optimizer limitations of filtered indexes (eg Rob Farley) but there is more.


Filtered indexes are a very useful tool in relational databases for two main purposes

1. Creating an index on a subset of rows, so the index is smaller and cheaper.
2. Filtered indexes can be declared unique for the filtered set. A classic example of this is Type 2 dimensions, which could have a unique index on the business key when filtered by IsCurrent=1. (There will be multiple versions of the business key that are no longer current.) Since the current record is the one most often selected, a filtered index on IsCurrent=1 is common practice.

However, these filtered indexes are not used by the SQL optimizer as often as they could be. Here are two examples.

1. Select from table where the predicates ensure the desired record is included in a unique filtered index and the predicates contain the full unique key. The filtered unique index won't be used. However, if the index was not filtered (Filter column is in index) the index is used. Logically, the index would be appropriate in both cases.
2. Select from a view that has a base table "left" joined to another table(s) using all the unique filtered index columns on the "right" side table. Normally, if none of the columns from the "right" side table are in the query (from the view) SQL will avoid accessing the "right" table. This is because there's no data required from it and it doesn't matter if there is no record (left join) and there can be no more than one record (unique constraint). Unfortunately, if the "right" table has a filtered index SQL won't ignore this table and will access it unnecessarily.

Here's some code to reproduce the phenomena.


CREATE TABLE dbo.tbCountries(
CountryCode char(2) NOT NULL,
Country varchar(255) NOT NULL,
CONSTRAINT PKtbCountries PRIMARY KEY CLUSTERED
(CountryCode ASC))

go

CREATE TABLE dbo.tbCountryGroups(
IsCurrent bit NOT NULL,
EffectiveFromDate datetime NOT NULL,
EffectiveToDate datetime NOT NULL,
CountryCode char(2) NOT NULL,
CountryGroup varchar(255) NOT NULL,
CONSTRAINT PKtbCountryGroupsx PRIMARY KEY CLUSTERED
(EffectiveFromDate ASC,
CountryCode ASC,
CountryGroup ASC)) 

go

CREATE UNIQUE NONCLUSTERED INDEX FilteredCountryCodeGroup ON dbo.tbCountryGroups
(CountryCode ASC,
CountryGroup ASC)
WHERE (IsCurrent=(1))

go

INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'AE', N'United Arab Emirates')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'AR', N'Argentina')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'AT', N'Austria')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'AU', N'Australia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'AW', N'Aruba')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'BB', N'Barbados')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'BD', N'Bangladesh')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'BE', N'Belgium')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'BG', N'Bulgaria')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'BH', N'Bahrain')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'BM', N'Bermuda')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'BR', N'Brazil')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CA', N'Canada')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CH', N'Switzerland')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CI', N'Côte d''Ivoire')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CK', N'Cook Islands')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CL', N'Chile')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CN', N'China')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CO', N'Colombia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CW', N'Curaçao')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CY', N'Cyprus')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'CZ', N'Czech Republic')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'DE', N'Germany')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'DK', N'Denmark')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'EC', N'Ecuador')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'EE', N'Estonia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'EG', N'Egypt')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'ES', N'Spain')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'FI', N'Finland')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'FR', N'France')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'GB', N'United Kingdom')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'GG', N'Guernsey')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'GR', N'Greece')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'HK', N'Hong Kong')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'HR', N'Croatia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'HU', N'Hungary')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'ID', N'Indonesia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'IE', N'Ireland')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'IL', N'Israel')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'IN', N'India')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'IS', N'Iceland')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'IT', N'Italy')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'JE', N'Jersey')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'JO', N'Jordan')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'JP', N'Japan')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'KR', N'Korea, Republic of')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'KW', N'Kuwait')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'KY', N'Cayman Islands')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'KZ', N'Kazakhstan')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'LI', N'Liechtenstein')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'LK', N'Sri Lanka')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'LR', N'Liberia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'LT', N'Lithuania')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'LU', N'Luxembourg')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'LV', N'Latvia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MA', N'Morocco')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MC', N'Monaco')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MH', N'Marshall Islands')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MN', N'Mongolia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MO', N'Macau')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MT', N'Malta')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MU', N'Mauritius')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MX', N'Mexico')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'MY', N'Malaysia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'NG', N'Nigeria')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'NL', N'Netherlands')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'NO', N'Norway')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'NZ', N'New Zealand')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PA', N'Panama')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PE', N'Peru')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PG', N'Papua New Guinea')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PH', N'Philippines')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PK', N'Pakistan')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PL', N'Poland')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PR', N'Puerto Rico')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'PT', N'Portugal')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'QA', N'Qatar')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'RO', N'Romania')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'RS', N'Serbia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'RU', N'Russia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'SA', N'Saudi Arabia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'SE', N'Sweden')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'SG', N'Singapore')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'SI', N'Slovenia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'SK', N'Slovakia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'SN', N'Supra National')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'SV', N'Slovak Republic')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'TH', N'Thailand')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'TN', N'Tunisia')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'TR', N'Turkey')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'TT', N'Trinidad and Tobago')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'TW', N'Taiwan')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'UA', N'Ukraine')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'UR', N'Uruguay')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'US', N'United States')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'VE', N'Venezuela')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'VG', N'British Virgin Islands')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'VN', N'Vietnam')
INSERT dbo.tbCountries (CountryCode, Country) VALUES (N'ZA', N'South Africa')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST (N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'AR', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'AT', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'AT', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'AU', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'AU', N'Strawberries')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'BE', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'BE', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'BR', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CA', N'Pears')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CZ', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'DE', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'DE', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'DK', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'DK', N'Oranges')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'ES', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'ES', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'FI', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'FI', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'FR', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'FR', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'GB', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'GB', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'GB', N'Grapes')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'GR', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'GR', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'HK', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'HK', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'HU', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'ID', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'ID', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'IE', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'IE', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'IN', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'IT', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'IT', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'JP', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'JP', N'Grapefruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'KR', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'KR', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'LU', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'MX', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'MY', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'MY', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'NL', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'NL', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'NO', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'NO', N'Oranges')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'NZ', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'NZ', N'Strawberries')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PH', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PH', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PL', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PT', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PT', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'RU', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'SE', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'SE', N'Oranges')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'SG', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'SG', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'TH', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'TH', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'TW', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'TW', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'US', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'US', N'Pears')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'ZA', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CL', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'EG', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CO', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CN', N'Lemons')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CN', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CN', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'IL', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'JO', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'MA', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PE', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PK', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'TR', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'VE', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PG', N'Strawberries')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CH', N'Oranges')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'BM', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'BB', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'KY', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'AE', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'SA', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'QA', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'KW', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'BH', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'SV', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'IS', N'Oranges')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'CY', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'LI', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'BG', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'EC', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'PA', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'UA', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'JE', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'MT', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'SN', N'Mangos')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'VG', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'AW', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'GG', N'Zespri')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'LK', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'VN', N'Apples')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'NG', N'Kiwifruit')
INSERT dbo.tbCountryGroups (IsCurrent, EffectiveFromDate, EffectiveToDate, CountryCode, CountryGroup) VALUES (1, CAST(N'1900-01-01 00:00:00.000' AS DateTime), CAST(N'9999-12-31 00:00:00.000' AS DateTime), N'RO', N'Oranges')


Now if you look at the showplan for the following query, you will see that it does not use the filtered index, even though it is appropriate.

Select * from tbCountryGroups
where IsCurrent=1
and CountryCode='AU'
and CountryGroup='Lemons'

Notice the table is accessed by scanning the clustered index, when an index seek would be optimal since it "knows" there's, at max, only one record to return.
Likewise, the following query will access the 3 "right" tables even though two of them are not requested and they can make no difference to the number of output rows (unique constraint ensures there is only 0 or 1 row).

Select CountryCode, Apples
from ( select
c.CountryCode
,c.Country
,app.CountryGroup Apples
,kfr.CountryGroup Kiwifruit
,lem.CountryGroup Lemons
from tbCountries c
left outer join tbCountryGroups app
on app.IsCurrent=1
and app.CountryCode=c.CountryCode
and app.CountryGroup='Apples'
left outer join tbCountryGroups kfr
on kfr.IsCurrent=1
and kfr.CountryCode=c.CountryCode
and kfr.CountryGroup='Kiwifruit'
left outer join tbCountryGroups lem
on lem.IsCurrent=1
and lem.CountryCode=c.CountryCode
and lem.CountryGroup='Lemons') v
where Apples is not null

Notice the index seeks to tbCountryGroups, two of which are unnecessary.

Now, if you create a new index similar to the filtered index, but instead of filtering by IsCurrent, include IsCurrent in the index. You will find that the above queries will use the unique index to access the appropriate rows for the first query and will avoid accessing the irrelevant "right" side tables in the second query.


create unique index UnFilteredCountryCodeGroup on tbCountryGroups
(IsCurrent
,CountryCode
,CountryGroup)

Notice how the plan above uses the unfiltered index, whereas before it was scanning the clustered index.

Notice how the plan above avoids joining the unnecessary "right" side tables now that we have an unfiltered index.

Note, the tables and queries I have created above are only meant to demonstrate the issue. I am not suggesting that these tables and queries are the business issue. My comment above about the filtered index being common practice with Type 2 dimensions is true, which makes this issue not uncommon in data warehousing.

Conclusion: Filtered indexes, are very useful in OLTP and DW environments, just not as useful as I would like.