Sql-server – Schema binding seems to not be enforced for view doing temporal query

sql serversql-server-2019temporal-tablesview

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.
*/

Best Answer

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).

Related Question