Friday, March 16, 2018

Same query, fast and slow

One of my clients had an issue where the same query was running fast or slow depending on the database connected to. This was in spite of both databases residing on the same server and the query was predicated with a database name, so both executions were executing exactly the same code on exactly the same database with the same data. How can this be?
 
Actually, there are a few reasons, with the most obvious being the COMPATIBILITY_LEVEL. Even though you are executing a query on Database dbA, if you are executing it from Database dbB, SQL will honour the compatibility level of dbB. In the case of my client, both databases had the same compatibility level of 130 (SQL Server 2016).

So, what happened?
Microsoft introduced LEGACY_CARDINALITY_ESTIMATION in SQL Server 2016 specifically so that you could configure a database with compatibility level 130, while keeping the old cardinality estimator (CE). A database upgraded to 2016 will keep the old CE by default, even though you run with compatibility level of 130 (2016). However, a new database on the same 2016 server will have compatibility level of 130 and the new CE.

So, two databases with compatibility level, 130, on the same server can have different CEs, leading to different plans (potentially one fast and one slow) for the same query pointing to the same database. In my client's situation, this led to one query executing in 7 seconds and the other executing in 10 minutes.

Essentially this was the code to reproduce the issue

Use dbA;
go
--This query executes in 7 seconds with an efficient plan
select *
      from dbA.dbo.myView
      where myKey= 23;   
Use dbB;
go 
--This query (on the same connection) executes in 10 minutes with an inefficient plan

select *
      from dbA.dbo.myView
      where myKey= 23;
    

I imagine the new cardinality estimator is generally better than the old one and its use should be encouraged. However, it could be that in your environment, the old CE is better. If you are having performance woes, it might be worth benchmarking the two CEs. It could even be that you create a second empty database, just to be used for those queries that prefer the old CE. (They would be executed from the second, dummy, database).

For instructions on altering the CE see https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql.
 

 

Friday, January 12, 2018

Querying the Query Store

SQL Server 2016 has finally included Query Store functionality where SQL Server efficiently records query/plan performance activity. Operational DBAs really need this information to see which queries are taking the bulk of the resources, and which queries have degraded on a plan flip. Before Query Store, we needed to use SQL Profiler to sample and aggregate. Query Store is much easier and is so efficient it can be left turned on in most environments.
SQL Server provides some visualisations of the top queries etc., but it doesn't give me the detailed information I need. But that's no problem, as we can write our own queries on the Query Store tables.
Here is my base query, which will give a list of queries and their plans, ordered by the queries with the greatest total CPU consumption. It's easy to change minimum thresholds, date range etc.
Note, if you haven't enabled Query Store, here is a link for turning it on. It's very easy. https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store#Enabling
Here is my base Query Store query 
declare
        @DaysBack int = 3
       ,@TotalCPUSecondsThreshold int =600;
with qTot as
       (select q.query_id, sum(rs.avg_cpu_time*rs.count_executions)/1000000 TotalCPUSeconds
              from sys.query_store_plan AS
              inner join sys.query_store_query AS
                     ON p.query_id = q.query_id 
              inner join sys.query_store_query_text AS qt 
                     ON q.query_text_id = qt.query_text_id
              inner join sys.query_store_runtime_stats rs
                     on rs.plan_id=p.plan_id
              inner join sys.query_store_runtime_stats_interval i
                     on i.runtime_stats_interval_id=rs.runtime_stats_interval_id
              where i.end_time>getdate()-@DaysBack
              group by q.query_id)
select
        q.query_id
       ,qt.query_sql_text
       , p.plan_id
       ,p.query_plan
       ,q.object_id
      ,convert(datetime,min(DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),rs.first_execution_time))) FirstExecutionTime
       ,convert(datetime,max(DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),rs.last_execution_time))) LastExecutionTime
       ,sum(rs.count_executions) Executions
       ,max(rs.max_dop) Max_DOP
       ,sum(rs.avg_cpu_time*rs.count_executions)/1000000 TotalCPUSeconds
       ,sum(rs.avg_duration*rs.count_executions)/1000000 TotalDurationSeconds
       ,100*sum(rs.avg_cpu_time*rs.count_executions)/sum(nullif(rs.avg_duration,0)*rs.count_executions) AvgCPUSaturationPct
       ,sum(rs.avg_cpu_time*rs.count_executions)/sum(rs.count_executions)/1000000 AvgCPUSeconds
       ,sum(rs.avg_duration*rs.count_executions)/sum(rs.count_executions)/1000000 AvgDurationSeconds 
       ,sum(rs.avg_physical_io_reads*rs.count_executions)/sum(rs.count_executions) AvgPhysicalReads 
       ,avg(rs.avg_rowcount) AvgRowcount
       ,qTot.TotalCPUSeconds TotalQueryCPU
from sys.query_store_plan p 
inner join sys.query_store_query q 
    ON p.query_id = q.query_id 
inner join sys.query_store_query_text qt 
    ON q.query_text_id = qt.query_text_id
inner join sys.query_store_runtime_stats rs
       on rs.plan_id=p.plan_id
inner join qTot
       on qtot.query_id=q.query_id
inner join sys.query_store_runtime_stats_interval i
       on i.runtime_stats_interval_id=rs.runtime_stats_interval_id
where i.end_time>getdate()-@DaysBack
  and qTot.TotalCPUSeconds > @TotalCPUSecondsThreshold
group by
        q.query_id
       ,qt.query_sql_text
       ,p.plan_id
       ,p.query_plan
       ,q.object_id
       ,qTot.TotalCPUSeconds
order by
        qTot.TotalCPUSeconds desc
       ,q.query_id

       ,p.plan_id

Hope that helps you tune your high performing SQL databases.

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.