Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter? If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the indexed view) is there any extra contention from the indexed view against the table as those records are being added to it?

Example:

CREATE VIEW FilteredIndexedView WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2, DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 &lt; '1/1/2019';

CREATE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexView (DateColumn1);

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view
Most of the information you will need will be found in this post by Erik Darling.


  ...SQL Server is sort of smart about some modifications. 
  Why it isn’t smart about all of them is beyond me


Inserts are fine when we adapt and run your queries:

CREATE TABLE dbo.BoringTable(ID INT IDENTITY(1,1),DateColumn1 DATE,TextColumn1 varchar(50),TextColumn2 varchar (50))

INSERT INTO dbo.BoringTable(DateColumn1,TextColumn1,TextColumn2)
SELECT TOP(1000) CONVERT(date,DATEADD(day,(-ROW_NUMBER() OVER(ORDER BY (SELECT NULL))),GETDATE())),'val1','val2'
FROM master..spt_values s
CROSS APPLY master..spt_values


GO
CREATE VIEW FilteredIndexedView 
WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2,DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 &lt; CONVERT(DATETIME, '20190101', 101)
GO

CREATE UNIQUE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexedView (DateColumn1);


And perform the insert that does not match the filter:

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view


The execution plan shows that the indexed view was not added to the plan, and that the optimizer knows that it does not need to access it. There is no additional contention found.



If we do insert a date that matches the indexed view, we get this plan:



And then, when performing an update on rows that do not match the indexed view (before or after):

UPDATE dbo.BoringTable
SET DateColumn1=  '12/13/2019'
WHERE DateColumn1 = '12/11/2019';


Nothing gets updated in the indexed view, but it does bring some extra overhead as additional operators are added to the plan, to be able to update the indexed view if necessary:



The delete does not add the indexed view operators

DELETE FROM dbo.BoringTable
WHERE DateColumn1=  '12/13/2019';