2

I am using Microsoft SQL Server 2019. There are two strange behaviors that I don't see documented (if it is, please point me to the Microsoft docs link), I suspect they're related.

  1. The INFORMATION_SCHEMA.VIEW_COLUMN_USAGE system view does not show information about view queries that do temporal queries.

  2. If I create a view which does a temporal query, schema binding is not enforced. I can alter the columns on the underlying table that the view's query uses. Normally, creating the view with the "WITH SCHEMABINDING" option would cause such attempts to alter the table to fail.

Try the sample code below to see that behavior. I added "!!" in the comments at the spots where things did not work out as I expected them to.

My main question: is this a bug or is this expected behavior?

--cleanup from past run
DROP VIEW IF EXISTS [vwTemporalTest_doingTemporalQuery]
DROP VIEW IF EXISTS [vwTemporalTest_noTemporalQuery];
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TemporalTest')
BEGIN
    ALTER TABLE [TemporalTest] SET (SYSTEM_VERSIONING = OFF);
END
DROP TABLE IF EXISTS [TemporalTest];
DROP TABLE IF EXISTS [TemporalTestHistory];
GO

--create the temporal table
CREATE TABLE [TemporalTest]
(
    [Id] [int] NOT NULL,
    [Name] nvarchar(500) NOT NULL,
    [Description] nvarchar(100) NULL,
    [period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([period_start], [period_end]),
    PRIMARY KEY CLUSTERED ([Id])    
) ON [PRIMARY]
WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON ))
GO

--insert some data
INSERT INTO [TemporalTest] ([Id],[Name]) VALUES 
(1, 'Alice'),
(2, 'Bob');
--just do an update so some history row gets populated
UPDATE [TemporalTest] SET [Name] = 'Bobby', [Description] = 'blah' WHERE [Id] = 2;
GO

--create the views (first one won't do a temporal query, second one will do a temporal query)
-- and they'll reference different columns to showcase the issue (first one uses Name, second uses Description)
CREATE VIEW [vwTemporalTest_noTemporalQuery]
WITH SCHEMABINDING
    AS SELECT [Id], [Name] FROM [dbo].[TemporalTest];
GO
CREATE VIEW [vwTemporalTest_doingTemporalQuery]
WITH SCHEMABINDING
    AS SELECT [Id], [Description], [period_start], [period_end] FROM [dbo].[TemporalTest] FOR SYSTEM_TIME ALL;
GO

--test out the views
SELECT * FROM [vwTemporalTest_noTemporalQuery]; --returns 2 current rows
SELECT * FROM [vwTemporalTest_doingTemporalQuery]; --returns 3 rows (including 1 historical row)

--look at information about the views
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where View_Name = 'vwTemporalTest_noTemporalQuery' --returns 2 rows (one for each column used)
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where View_Name = 'vwTemporalTest_doingTemporalQuery' --!!returns no rows (I expected 4 rows at least since the view uses 4 columns)

/*
--now verify schema binding prevents the underlying table from being altered
ALTER TABLE [TemporalTest] ALTER COLUMN [Name] nvarchar(600) NOT NULL; --fails as expected - The object 'vwTemporalTest_noTemporalQuery' is dependent on column 'Name'.
ALTER TABLE [TemporalTest] ALTER COLUMN [Description] nvarchar(255) NULL; --!!unexpectedly succeeds. I expected it to fail because vwTemporalTest_doingTemporalQuery uses the Description column.
*/
0

I think one thing in this case must be considered: your view is not referencing the temporal table directly, it is using a feature from SQL Server to get data that is stored on a different table (the history table).

The graph from the doc How do I query temporal data shows how you get the data from a temporal table:

enter image description here

If the picture shows the real data flow, your query doesn't directly "touch" the history table. Therefore, your view with the history data is not exactly dependent on the history table.

Of course the following is not the appropriate way of manipulate temporal data, but I think it helps to exemplify my point:

CREATE VIEW [vwTemporalTest_doingTemporalQuery2]
WITH SCHEMABINDING
    AS SELECT [Id], [Description], [period_start], [period_end] 
       FROM [dbo].[TemporalTestHistory];
GO

This view is directly referencing the TemporalTestHistory table and when you try to alter the history table as you did before, it throws the error you expected. Also, this view appears on the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE.


Important remarks

Under the Important remarks of the Creating a system-versioned temporal table you can see that:

  • The history table must always be schema-aligned with the current or temporal table, in terms of number of columns, column names, ordering and data types.

And the Important remarks section of the Changing the schema of a system-versioned temporal table doc says:

  • Specified schema change is propagated to history table appropriately (depending on type of change).

From those remarks it seems we're not supposed to do changes directly on the history table as a good practice.

About the INFORMATION_SCHEMA.VIEW_COLUMN_USAGE not showing any info regarding that view I couldn't find any explanation. By the way, this is the conclusion I could get from tests and reading the docs I mentioned and I posted it as it might help to get to a more accurate answer (maybe it will be considered as a bug like you mentioned).

5
  • Thanks, this did cover some of the same documentation I read, but it is good to get the links out there. I feel like the VIEW_COLUMN_USAGE should really include double the rows (since really it hits two tables now - TemporalTestHistory and TemporalTest). The fact that the history table's schema must match the current table shouldn't pose a difficulty for the schemabinding enforcement - really it seems like it should help that. I agree the behavior might still be considered a bug.
    – Anssssss
    Mar 24 at 20:22
  • do you know what the flavor-of-the-week is for submitting SQL Server bugs? I miss the Microsoft Connect site. Maybe feedback.azure.com/forums/908035-sql-server is the right place, or is there a github where they take it now?
    – Anssssss
    Mar 25 at 13:59
  • @Anssssss I've never submitted a bug to Microsoft. One time I found a bug related to is_media_read_only, but I got lucky and someone from Microsoft on the forum issued a hotfix. here's other question where they talked about reporting a bug on the comments and Erik Darling's suggestion points to the URL you mentioned.
    – Ronaldo
    Mar 25 at 14:17
  • "suggestion" (apparently you can't create bugs on their feedback site) - feedback.azure.com/forums/908035-sql-server/suggestions/…
    – Anssssss
    Mar 26 at 15:33
  • Yet again Microsoft has shuttered their feedback site and replaced it with a new one. I manually recreated the issue there: techcommunity.microsoft.com/t5/sql-server/…
    – Anssssss
    Aug 20 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.