On index-based query in SQL Server database

• Published in: Chinese Control Conference pp. 9519 - 9523
• Main Author: Qi, Chunxia
• Format: Conference Proceeding Journal Article
• Language: English
• Published: TCCT 01.07.2016
• Subjects: Algorithm design and analysis; Clustered index; Clustering algorithms; Computational efficiency; Indexing; Non-clustered index; Performance indices; Query; Query languages; Query processing; Resource management; Servers; Tables (data); Trees
• ISSN: 1934-1768
• DOI: 10.1109/ChiCC.2016.7554868

Indexes are very important in query data from database, especially in large scale of data. Rational use of index technology is essential in improving database's query performance. SQL Server database use B + tree structure to store indexes. Clustered index can change data's physical position. Both of Clustered index and non-clustered index depend on B + index tree to query data. Non-clustered index should depend on either data row pointer or clustered index key to find the retrieve data. Two algorithms are used in SQL Server database to retrieve data. Usually table scan should be avoided except that large-scale of data involved in the query or most columns of the table are covered in the query. Table seek has a better efficiency for most cases. Some principle of how to use index properly are given in this paper. The paper also suggest that functions, calculations and some query conditions should to be avoided or replaced, or to be used as little as possible in query statement to make indexes effective.