SQL Server Very Slow Filter on Bit
SQL Server 2008R2 was very slow to filter by a bit column in a joined tiny table, in what I thought was a very simple query. Adding a normal index to the tiny table didn't help:
Select h.Id FROM HugeTable h JOIN TinyTable t ON h.TinyTableId = t.Id
WHERE t.BitCol = 1
Created a Filtered Index on the tiny table... and now the query worked at the expected speed!
CREATE NONCLUSTERED INDEX FIX_TinyTable_BitCol
WHERE BitCol = 1
I had never bothered with filtered indexes before, but I now see that they are very important!
Created By: amos 10/31/2014 4:00:05 PM