Data Browser - Viewing Site  Sector 23 Code Bank Logged in as:  Guest  




           


SQL Server Very Slow Filter on Bit
Issue:
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

Fix:
Created a Filtered Index on the tiny table... and now the query worked at the expected speed!

CREATE NONCLUSTERED INDEX FIX_TinyTable_BitCol
ON TinyTable(Id)
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