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.
The INFORMATION_SCHEMA.VIEW_COLUMN_USAGE system view does not show information about view queries that do temporal queries.
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. */
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:
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
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.
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).