none
Two rows in table does it need Index

Ответы

  • For performance, there is no reason to add an index. However, you may want to add a constraint to enforce data integrity.

    As for the other question for how many rows there has to be before you add an index, that is not really a good question. It's more relevant to ask how big the table has to be in number of pages. If you have a table with an id and a char(8000) column, each row will take up a page. To find a row by id, it will be faster already at 10 rows, because with an index, SQL Server only needs to read two pages instead of 10. On the other extreme, if you have only two integers in the table, you can fit some 350-400 rows on a single page.

    However, that is largely academic. No matter the number of rows your table should have a primary key to prevent duplicates, and for many small tables the primary key is sufficient.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 19 июня 2012 г. 9:15
    11 июня 2012 г. 9:25

Все ответы

  • No for only few records!

    Effective indexes are one of the best ways to improve performance in a database application. Without an index, the SQL Server engine is like a reader trying to find a word in a book by examining each page. By using the index in the back of a book, a reader can complete the task in a much shorter time. In database terms, a table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

    One of the most important jobs for the database is finding the best index to use when generating an execution plan. Most major databases ship with tools to show you execution plans for a query and help in optimizing and tuning indexes.

    Many Thanks & Best Regards,

    Hua Min




    11 июня 2012 г. 3:07
  • what do you mean by fewer records tens, hindered or thousand?Is there a standard way to find out that?

    Navin.D http://dnavin.wordpress.com


    • Изменено Navind 11 июня 2012 г. 3:16
    11 июня 2012 г. 3:15
  • I suggest you use an index for more than 100 rows!

    Many Thanks & Best Regards, Hua Min

    11 июня 2012 г. 3:21
  • 100 rows is also too small , an optimizer probably will use a table scan or index scan as it is cheaper..

    Best Regards, Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspxhttp://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    11 июня 2012 г. 5:42
  • In my memory, we usually have to have indexes for few thousands of rows or not!

    Many Thanks & Best Regards, Hua Min

    11 июня 2012 г. 5:49
  • It depends on how selective is an index.

    Best Regards, Uri Dimant SQL Server MVP http://www.dfarber.com/microsoft-solutions/mssql-server.aspx http://www.dfarber.com/computer-consulting-blog.aspx http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    11 июня 2012 г. 5:50
  • For performance, there is no reason to add an index. However, you may want to add a constraint to enforce data integrity.

    As for the other question for how many rows there has to be before you add an index, that is not really a good question. It's more relevant to ask how big the table has to be in number of pages. If you have a table with an id and a char(8000) column, each row will take up a page. To find a row by id, it will be faster already at 10 rows, because with an index, SQL Server only needs to read two pages instead of 10. On the other extreme, if you have only two integers in the table, you can fit some 350-400 rows on a single page.

    However, that is largely academic. No matter the number of rows your table should have a primary key to prevent duplicates, and for many small tables the primary key is sufficient.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Помечено в качестве ответа Iric WenModerator 19 июня 2012 г. 9:15
    11 июня 2012 г. 9:25
  • Hi,

      I don't think it's necessary for creating index if only 2 rows. The execution plan will show you there won't be different (if is, I would even estimate without index is faster) due to the small number of records. 

    11 июня 2012 г. 13:13