0

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

Data Engineer
  • 795
  • 16
  • 41

1 Answers1

0

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.

Brian
  • 1,238
  • 2
  • 11
  • 17
  • Thanks Brian. I read this article just before posting my question. Do you get this statement for the article: "A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results." ? – Data Engineer Oct 20 '17 at 18:50
  • Also, would you say it's better to put only the most distinct column to the index definition and put the least distinct (EmpId) to the INCLUDE to make int covering index? – Data Engineer Oct 20 '17 at 18:52
  • Actually, Just realized that i don't need to use the INCLUDE option for my filtered index as the EmpID and the MyId fields are the only that are being selected in the view and they are heavily used in the JOIN and the WHERE clauses. And the "Included columns" are not stored in the index B-Tree. – Data Engineer Oct 20 '17 at 19:15
  • Sorry - I've read the statement you quoted five times now and I can't make sense of it. I generally like covering indices, but whenever someone asks me "which is better?" the only answer I ever give is "try it both ways and compare results". – Brian Oct 20 '17 at 19:16
  • Brian, My statement was that I dis not need to go with a covering index eg.; (INCLUDE (EmpId) ) as it's used in JOIN and WHERE and nowhere else. – Data Engineer Oct 20 '17 at 21:13
  • Ahh - we must have cross-posted. I'm glad that a simple index will work for you here. – Brian Oct 20 '17 at 21:45