Thursday, November 9, 2017

Multidimensional and Tabular Compared

Microsoft's SSAS offers a choice of technology for OLAP cubes - the original Multidimensional, and the newer, Tabular. I compared them back in 2012 http://richardlees.blogspot.com.au/2012/05/sql-server-2012-tabular-versus.html. It is now safe to assume now that Tabular is the strategic technology. Not officially strategic, but with the dearth of Multidimensional enhancements since 2012 and Tabular recently becoming the default install, it is clear to see where Microsoft is heading.


It has been pleasing to see enhancements to Tabular in each SQL release, which has reduced the need for Multidimensional. However, there are still a few features that Tabular does not support, necessitating new developments with Multidimensional. The standout feature that Tabular does not support is composite primary keys. Ralph Kymball, needs to take some responsibility. His Star Schema modeling methodology employs single column surrogate keys for all dimension tables. Kymball has advanced data modeling a great deal, but I think it's wrong to blindly generate surrogate keys when natural primary keys exist. Kymball methodology requires fact and dimension tables to be physically generated from underlying normalized tables. This involves a lot of work, reconciliation and performance cost, when the underlying tables could be used directly, with their composite primary keys.


The really sad fact is SSAS documentation, comparing Multidimensional and Tabular, does not even list composite keys as a feature worth comparing. It's as if, no one would require a cube technology supporting composite primary keys. See https://docs.microsoft.com/en-us/sql/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas





Since SQL 2012, I have chosen Tabular where possible, but it has been on rare occasions that Tabular could succeed. Mostly due to Tabular not supporting composite keys, and my unwillingness to generate redundant star schemas. Why would you generate copies of fact and dimension tables, just to build surrogate keys? Surrogate keys have no meaning. Business keys have meaning, and are typically part of the table already. Also, when you generate a redundant star schema, you need to continually append (or rewrite) to these tables as your DW data arrives. And, you should continue to reconcile these generated tables with the source tables. One might argue that the resultant star schema is more efficient than a star schema with composite keys, but that argument is marginal and rarely overcomes the increased cost and lag of building star schemas. And there can be significant performance loss in flattening (denormalising) the snowflake dimensions.


Even something simple like a Type 2 dimension already has a perfectly good natural key consisting of the source system's business key (eg ClientId) and EffectiveFromDate. So, why not just add EffectiveFromDate_Client to the fact table? A complete dimension and fact table rewrite is unnecessary!


I'm hoping that data warehouse developers see the sense of natural composite keys and that Tabular supports them in the near future. In the meantime, I will only be creating Tabular models on the rare occasions where composite keys are not part of the data structures.


If you would like to see Tabular support composite keys, please vote on this connect item. https://connect.microsoft.com/SQLServer/feedback/details/3144082

Wednesday, June 7, 2017

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.