Clearly (methinks), creating a catalog on the
BIT column is unnecessary. However, should you have had a column you need to search by which every value is probably unique, like
StreetAddress or something like that, then a catalog appears appropriate (again, methinks).
But what is the cutoff? Let's say you anticipate 10,000 rows you'll also find about 20 unique values included in this. Should a catalog be produced?
Thanks ahead of time.
The very best response to this really is to profile your queries if the index enhances your queries. The problem in responding to this is it is almost impossible to generalize the behaviour from the query optimizer.
Nevertheless, a guide-of-thumb is that if your selectivity is 10% or less on the given query on the table, then you'll probably take advantage of a catalog. So inside your example, you might take advantage of a catalog in case your values are distributed. However, thinking about that the table is small, so that your performance boost may be minimal.
This isn't a solid rule as you will find lots of factors that may alter the 10% number, including using a clustered or any other index types, size the rows, if some posts not inline, query structure, etc.
Also bear in mind there's a substantial performance penalty for placing right into a table by having an index. If the table is often up-to-date or appended, the rate boost in the index might be negated through the reduced card inserts and updates.
Begin to see the MSDN article on Tablescan vs Index access.
Edit: As others have stated, your query will benefit from a catalog if you're carrying out aggregation queries, for example counting the amount of occasions a specific value seems. You may even benefit should you frequently sort on the particular column.
James hit the nail around the mind. I'll just include that even a little column might take advantage of a catalog for the way you're while using table. For instance, if you want to count the amount of rows which have single many occasions during the day, a catalog there might be helpful. Indexes aren't always about getting a single record - they may also be used for aggregations.
Within the column you suggest, there'd be rationale to produce a reference or research table for that data to prevent data redundancy. This could build your column an overseas key, pointing for the PK from the new research table.
All foreign key posts ought to be indexed.
Otherwise, I'd avoid placing a catalog under normal conditions on this type of column.
creating a catalog on the
BITcolumn is unnecessary.
You would be suprised.
I have needed to create a catalog including a little column for any query like:
SELECT foo.Name FROM foo WHERE foo.Active = 1
There have been about 300,000 rows within the table though.
Indexes with low cardinality are extremely problematic. If you will find only several possible values, SQL Server more often than not index-scans, regardless of what the proportions.
Example: I have were built with a table having a Condition area that only recognized the values "A", "N", and "R" (for Active, New, and Upon the market.) Typically you'd approach an ailment where 95% were "R", 4+% were "A", along with a couple of were "N". Choose WHERE condition = 'N' would table-scan, regardless of what.
BUT - there is a newish index type known as a Filtered Index, which finally handles this problem. It is also handy when you wish to exclude records with NULL values.
I'd say everything is dependent how the table has been used and also the overall system needs. For instance, whether it's part of a big JOIN and also the parent has become a report type table then you will want the index without a doubt. Whether it's relatively small compared to your other tables within the database, and it is heavily placed and rarely read, then a catalog is probably not preferred.
But, scope of operation among the whole database versus available assets to become allotted is paramount decision factor. It's how this table + possible index reacts within the entire system versus all your other tables as well as their needs. If you do not keep your large picture in your mind you can get rid of the whole system if you attempt to use some arbitrary rule simply with regard to using a random rule.