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
|
|