Sql-server – ny best number for rows count in a table to start using indexing

indexnonclustered-indexsql serversql server 2014

I'm a newbie in DBA. I want to know if there is any specified point in a table's rows count that when we reach that point, then we could use an index. I know there should be some dependency between table's rows count and using index, but I don't know if there is an standard table's rows count or not.

Best Answer

I don't know about a best but at a small number there is no real value.

There is some overhead to using the index. Yes an index seek is faster than a table scan but there is some overhead to using the index. Index maintenance clearly has overhead.

If a table has a PK then you should use that as a PK and typically clustered.

Consider a table of USstates (50 rows)

ID PK identity tinyint
Name varchar(20)
Region tinyint

Region would be use to group states like NE, SE, ...

I personally would never use indexes on Name or Region - a table scan is still very fast. Region would be a FK but that does not automatically create an index (to my understanding). The whole table is right at the 2K page size. If sort on State.Name is used a lot then yes that index would be used but I just don't think you could even measure the performance gain.

Over a million rows then yes start building indexes up front.

Between a thousand and a million then consider building indexes on a case by case basis.

Even at 10,000 rows there are going to be a lot of cases of obvious indexes. A column like AddDate that is not likely to change and would be used a lot for search and sort I would index and maintain (de-fragment). A table with more than 10,000 rows that reference State as a FK I would index that column up front. But since you are asking the question maybe wait and optimize for real life queries.

I would not want you to take the other extreme and put an index on every column as it might be used. An index has overhead. An index will slow down insert and update. A highly fragment index can be slower than a table scan.

I get a lot of the users on this site want to optimize up front and have theoretical discussions. This is real life advice for a newbie in DBA.