Could you please tell me cases where indexing on an SQL Server table is not required?
+1
A:
Technically it's never "required", but it's always a good idea. If you don't have a clustered index (and non-clustered indexes need a clustered index to work), then every time you access data from that table, you have to do a table scan to retrieve the information. If you have an extremely small number of rows in a table, then the index would technically be of little benefit.
Kevin
2010-03-19 14:38:59
"non-clustered indexes need a clustered index to work" Non-clustered indexes work without a clustered index, the different being in what they hold for a pointer to the rows. If a clustered index exists, the non-clustered index will hold the cluster key as pointer. Without a clustered index, the non-clustered indexes hold a pointer to the physical location.
Shannon Severance
2010-03-19 16:30:28
A:
My old DBA told me that as a rule of thumb in our environment, narrow tables with less than 50K rows do not need indexing for data retrieval purposes from that one table because the cost of the index lookup offsets the cost of the table scan.
Raj More
2010-03-19 14:39:30
You're better off profiling that yourself on a case-by-case basis rather than using a rule like "50k".
Welbog
2010-03-19 14:41:12
And are you not better off letting the optimizer make that decision? An index on such a table is not going to be wasting much space anyway.
Ben Challenor
2010-03-21 09:47:30
@Ben Challenor: I put a clustered index (PK) on a 5 row lookup table as soon as I create it. The question, however, refers to where an index, and I answered in the context of data retrieval.
Raj More
2010-03-22 14:28:37
@Weblog: I agree, a profiler run would point out ineffective joins. I will change my answer in accordance.
Raj More
2010-03-22 14:38:49
+1
A:
Can't think of a really GREAT example. Maybe if you had a table that only held one value for whatever reason. There would be no need to index that table.
Scott Vercuski
2010-03-19 14:39:30
Let's say you had 10 Million rows of just one column. An index seek would take you to your desired row much faster than a table scan would. A clustered index seek would be even faster than a regular index seek.
Raj More
2010-03-19 14:41:38
@Raj - Sorry ... I should clarify .. I mean a table with 1 column and 1 value. I've worked at several companies that had such a table. Usually it held something like the email of a single contact (or distribution list) which was used for administrative emails or something similar.
Scott Vercuski
2010-03-19 14:45:23
One example of a one column, one row table is the HiLo table for an ORM such as Hibernate/NHibernate.
Ben Challenor
2010-03-21 00:59:09
+1
A:
If a table is below a certain number of rows, then it is quicker to do a table scan than to look up an index.
I'm not sure of the exact number at which one becomes quicker than the other.
NeilD
2010-03-19 14:41:02
I'd say less than 1 or zero rows, based on my back of the envelope calculations
gbn
2010-03-19 20:22:32
+1
A:
One place it might not be required is for something like a logging table where you save some events but would never refer to a primary key within the table. IF you ever access the table it might be for text searches which usually require a table scan anyway. Although one could easily argue that a log table is nothing without a date/time column and that should be indexed.
Paul Sasik
2010-03-19 14:41:48
wrong ;) you are pretty likely to ask for the content by timestamp at least - and definitely are going to ask for it SORTED by timestamp ;)
TomTom
2010-03-19 20:28:01
@TomTom: i think you might not have read the last sentence of my original post.
Paul Sasik
2010-03-20 03:37:21
A:
I can think of a few examples where an index might not be required, but I cant honestly think of any example where you wouldn't want to index your table anyway.
At the very least you should have your primary key indexed - there is just no reason not to.
Kragen
2010-03-19 14:50:53
+2
A:
I think it would be a table in which you need to dump data quickly and possibly in high frequency (like in data acquisition applications, where you would dump raw data from sensors) to be processed later. This is because indexing tends to slow down the data insertion a bit due to overhead in rearranging the index.
Jaya Wijaya
2010-03-19 15:40:36
Possibly the case, especially with SQL Server MERGE statement. If you load mass data to merge it with a fact table (data warehouse) the processing will ANYWAY require table scans on the table. Any index would be surplus and not bring anything. Pretty much the ONLY valid point I can come up with.
TomTom
2010-03-19 20:30:50
A:
For non-staging table, when the table has zero (or maybe just one) rows and has no foreign keys. However, how would you prevent someone adding the same value again?
Otherwise, there may be a benefit for staging tables that are emptied then bulk processed in one call
Simply put, there is almost no case wher you'd not use an index somewhere...
gbn
2010-03-19 20:21:22
A:
I've worked on ETL solutions (Integration Serices writing to SQL Server 2005) where I had to chuck away a lot of indexes as the inserts (and there were only inserts) were taking hours (although as the data was later imported into an Analysis Services cube, where other relations were defined, you could argue that the indexes weren't really ditched). For some tables the compromise was to drop the indexes before the writing process and then recreate them afterwards; the recreation took a long time, but was still quicker than inserting against the momentum of simultaneous index building.
davek
2010-03-19 20:35:08