Tell me a situation in which SQL Server indexing is not required

Question

Could you please tell me cases where indexing on an SQL Server table is not required?

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.