StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>let me sum this up:</p> <ul> <li>when query a table, in your where clause you can have an <strong>arbitrary combination of the columns</strong></li> <li>you could create an individual index on each column, but only one of them would be used for your query, as normal b-tree indexes cannot be combined</li> <li>you could create <a href="http://aboutsqlserver.com/2010/10/20/composite-indexes/" rel="nofollow"><strong>composite indexes</strong></a> on certain combination of columns but these indexes would only be used by those queries that have a where clause that matches the columns in the composite index; also, having many wide composite indexes would incur big maintenance overhead</li> <li>Since you want to filter against arbitrary combination of the columns in your queries, composite indexes are not an option: you cannot create a composite index for all possible combination of columns</li> </ul> <p>In general, the solution to this problem is having <a href="http://en.wikipedia.org/wiki/Bitmap_index" rel="nofollow"><strong>bitmap indexes</strong></a> on each columns, because bitmap indexes can be combined. Unfortunately SQL Server does not support bitmap indexes but I've heard it has some similar feature. I suggest you look into that:</p> <p><a href="http://msdn.microsoft.com/en-us/library/bb522541.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/bb522541.aspx</a> (This article discuss the usage of bitmap indexes when joining tables, but dont let that confuse you, they can be useful in your use case as well, when you query a single table.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload