Subquery in the WHERE clause in Nonclustered Filtered Index

Question

I'm using SSMS 2016 and trying to create a Filtered nonclustered index on the two columns which are used in the JOIN and Where Conditions. Is it allowed to use Subquery in the WHERE Clause when filtering index. Below is corresponding example:

CREATE NONCLUSTERED INDEX [ncIX_MyId_&_EmpId_on_MyDB_Mytbl_optimized] 
ON MyDB.dbo.[Mytbl_Optimized] ([MyId], [EmpId])

WHERE [MyId] IN 
(SELECT DISTINCT Mytbl.[EmpId] FROM MyDB.dbo.Mytbl_Optimized AS t1 (NOLOCK) 
            LEFT OUTER JOIN AnotherDB.dbo.Another-tbl AS t2 WITH (NOLOCK)
            ON t1.EmpId = t2.EmpId)

--The SSMS compiler says incorrect syntax near 'IN'... 

But when I substitute the subquery to explicit values like:

WHERE [MyId] IN (1,2) -- it works fine. 

My goal is to improve performance of a not indexed view (making this view Indexed is not an option as it is getting updated every 5 minutes). In that view the select statement above is used as a filed result set:

CREATE VIEW 
AS 
col1,
Col2,
(SELECT DISTINCT Mytbl.[EmpId] FROM MyDB.dbo.Mytbl_Optimized AS t1 (NOLOCK) 
            LEFT OUTER JOIN AnotherDB.dbo.Another-tbl AS t2 WITH (NOLOCK)
            ON t1.EmpId = t2.EmpId 
WHERE AnotherDB.dbo.Table3.MyId = t1.MyId
FOR XML PATH('')) AS MyConcatenatedID
FROM AnotherDB.dbo.Table3 

Note: the total number of records in the tbl_Optimized table is 460,000 and total number of unique EmpId values in the table is 26

Answer 1

Unless I'm mis-reading this article, a filtered index won't help your view.

Also, I don't believe you can use a sub-query to define what is included in the index. Based on my research and testing, the list in the IN clause has to be explicit.