Sql-server – Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause

hintsquerysql servertemporal-tables

Probably a pretty quick question, but is there any way to specify Query/Table hints for the History Table when using the FOR SYSTEM_TIME statement when querying temporal tables? I suspect not, but I wanted to double-check before I throw in the towel on this one.

Here's a dbfiddle that shows a basic breakdown of different ways I'm aware of specifying hints for a query, and the only way I can figure out how to pass query and/or table hints that interact with a history table is to convert the query to a UNION ALL between the live and history tables rather than use the FOR SYSTEM_TIME clause.

When trying to specify a history table hint when using the FOR SYSTEM_TIME clause, I get the following error:

Msg 308 Level 16 State 1 Line X
Index '<<HISTORY TABLE INDEX NAME>>' on table '<<LIVE TABLE NAME>>' (specified in the FROM clause) does not exist.

When trying to specify a query hint pointing to the history table when using the FOR SYSTEM_TIME clause, I get the following error:

Msg 8723 Level 16 State 1 Line X
Cannot execute query. Object '<<HISTORY TABLE>>' is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.

These errors both make sense on the surface as the history table is not specifically referenced in the query, but is there another query hint, trace flag, etc. that I can use so that I can still use the FOR SYSTEM_TIME clause and specify hints against the history table?

Best Answer

While not a great idea in general, a workaround that seems to work is using the USE PLAN hint

Base query to get the XML from

-- UNION ALL instead of FOR SYSTEM_TIME clause - Table Hints
SELECT  *
FROM    dbo.People WITH (INDEX(IX_Live_People__Name))
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

UNION ALL

SELECT  *
FROM    hist.People WITH (INDEX (IX_Hist_People__Name))
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Get the XML enter image description here

Replace ' with ''

enter image description here

Run the query with USE PLAN and the previously generated execution plan

SELECT  *
FROM    dbo.People  FOR SYSTEM_TIME ALL 
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John' 
OPTION(USE PLAN N'...')

Result

enter image description here

Using a plan guide to force the plan

Change ' to '''' in the query plan from the base query

enter image description here

Create the plan guide

EXEC sp_create_plan_guide   
    @name = N'TemplateGuide1',  
    @stmt = N'SELECT    *
FROM    dbo.People  FOR SYSTEM_TIME ALL 
WHERE   LEFT(VolatileData, 2) = ''2A''
    AND Name = ''John''',  
    @type = N'SQL',  
    @module_or_batch = NULL,  
    @hints = N'OPTION(USE PLAN N''Plan Here'')'

Test the query

SELECT  *
FROM    dbo.People  FOR SYSTEM_TIME ALL 
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Result

enter image description here

The plan guide is used, based on the PlanGuideName fragment in the plan's XML

PlanGuideName="TemplateGuide1"

&

UsePlan="true"

As expected, when we add a couple of spaces the plan guide is no longer used

SELECT  *
FROM      dbo.People  FOR SYSTEM_TIME ALL 
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Resulting in the query without the index hints

enter image description here

(Tested on build version 14.0.3045.24 / SQL Server 2017 CU12)

Notes

While this is a possible workaround it is only meant to be used if it is A) needed and B) there are no other options.

The use of plan guides is most beneficial when using paramaterized queries (either by design or force), creating a plan guide for each ad hoc query possibility is not feasible.

Related Question