Originally posted on stackexchange (https://dba.stackexchange.com/questions/287616/schema-binding-seems-to-not-be-enforced-for-view-doin...), languished in user voice for a while before that was shut down (https://feedback.azure.com/forums/908035-sql-server/suggestions/43011810-schema-binding-seems-to-not...), now manually migrated to this site.
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.
*/
-----------------------------------------------------------------
