Data Tune - Don't Wait For a Problem

Just like the human body, we should keep our databases tuned and running efficiently. Your doctor will recommend to you to monitor and manage your cholesterol, body fat, and general health. We shouldn't wait until we need a gastric band before we change our eating habits. It's the same with a database system. We shouldn't wait for a problem. Rather, we should continually monitor and tune our databases. 

How do we do this

• Continually monitor total CPU usage. If it's over 90% for sustained periods, there's little headroom and you should tune your expensive queries (and/or add more cpu resource). Note, expensive queries are the ones that use the most "total" CPU. So a query that runs 5 times per second using 0.5 seconds is 100 times more expensive than a query that runs once per hour using 90 seconds CPU.
• Continually monitor average read and write IO latencies. If average latency is over 15ms for more than a few minutes per day, then you have an emerging IO bottleneck. The solution is often to tune queries that incur lots of IO. These queries often only require better predicates, updated statistics, or an index to prevent a table scan. Note, if your queries are well tuned, the solution might be to increase RAM (SQL holds more in the buffer so needs less IO) or to upgrade the disk subsystem to support higher IO rates.
• Periodically enable SQL Profiler and find the most expensive SQL queries. It might surprise you how much you can take off the total system resources by tuning the top 10 SQL queries
• Look at your compression strategy. SQL Server likes compressed tables. They take less space in the data cache and require fewer IOs for a full/partial table scan. The CPU overhead in reading these tables is almost negligible. 
• Look at your fillfactor for large tables. If you are sequentially (by clustered key) loading these tables, the default of 10% is just wasting space on disk and data cache. Use fillfactor=100 for sequentially loaded tables.
• Etc.

If you ignore your database until there is a performance issue, it will be hard to fix. With a performance bottleneck, adding Profiler traces etc, will only make it more unstable, initially. So it will be hard to find the issue without further slowing down the system.

Continual tuning will also keep your system overhead to a minimum and you'll get payback from lower hardware and licensing costs. Note, as you add cpu (and to some extent RAM) to your server, your SQL Server licensing costs will increase too. So keep your database systems tuned and healthy. 

I am happy to discuss this topic in more detail as it applies to your systems.

No Excuses for Failed Data Warehouse Projects

I really struggle to understand why so many Data Warehouse (DW) projects fail. In 2005, Gartner reported that 50% of DW projects fail or have limited success. From 1997 thru to now, 2018, I have had 100% success rate with numerous DWs and Business Intelligence solutions. It's not that hard.

I recently helped with performance issues on a DW that was designed 8 years ago by a reputable vendor and built/deployed more recently by a second vendor. I have always said there is scope to improve database performance and this system was no exception. In a few weeks we identified fixes for the slow ETL process and query fixes so that all reports rendered in a few seconds. However, I was shocked by the poor design/implementation. This customer had a detailed full design before outsourcing the development. Designing a big DW, like this, and implementing the design over many years is a recipe for failure. Design flaws, requirement ambiguities etc. only surface once the DW has been built and populated. It's very hard to redesign later. My rule of thumb is that if a DW takes 12 developers 3 years to develop, significant design changes will take a proportion of the 3 years for 12 developers to change. Whereas an agile development that takes information all the way through to end users in stages will take a fraction of the effort to fix a design/requirement ambiguity flaw.

The best insurance to avoid this sort of DW monster is to develop in short phases that take on the biggest risks and most important aspects first. For example, if there are large and volatile source tables that need to be close to real-time in the DW, some of these tables should be implemented first, to test and prove the efficiency and accuracy of the ETL. Once visualised by users and proved to scale, the design can be used as a template for the other large source tables. If there are performance/locking/interpretation issues, they can be corrected with relative ease. Only continue with DW development once the user representatives are happy with the reports/dashboards/analysis etc this far. It's unfair to ask users to sign off on hundreds of pages of "specifications" before a big waterfall implementation. Note, just because there are daily stand-ups does not mean the project is agile.

For a full list of my DW design principles see Guiding Principles of Data Warehousing.

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.