Six rules of SQL Server Index Management

Through indexing, you can speed up data query and reduce system response time, and speed up the connection between tables. However, it is not always possible to use indexes at any time. If the index is used improperly, it will be counterproductive.

An index is a database object based on a table column. The index stores the sorted index columns in the table, and records the physical storage location of the index column in the database table. This logically sorts the data in the table. Through indexing, you can speed up data query and reduce system response time, and speed up the connection between tables.

However, it is not always possible to use indexes at any time. If the index is used improperly, it will be counterproductive. Therefore, you must follow certain rules when using indexes in the SQL Server database.

Iron 1: There is no free lunch in the world, and you have to pay for indexing.

The advantages of indexes are obvious to all, but few people are concerned about the cost of using indexes. If the database administrator has a full understanding of the price required for indexing, it will not create indexes everywhere.

The cost of building an index is quite high after careful calculation. For example, it takes time and effort to create and maintain indexes. Especially when designing a database, the database administrator needs to set up indexes for the fields in the table and conduct research and coordination. For example, when the records in a table with an index are increased, deleted, or modified, the database must re-adjust the index. Although the work database is automatically completed, the resources of the server need to be consumed. The more data the table contains, the more resources it consumes. If an index is an actual object in a database, each index occupies a certain amount of physical space. If there are more indexes, it will not only occupy a large amount of physical space, but also affect the operation performance of the entire database.

It can be seen that if database administrators want to use indexes to improve system performance, they still need to pay a lot of costs. What database administrators need to consider now is how to achieve a balance between the two. Or, find a critical point of return and investment.

Iron 2: do not create an index for columns that are rarely involved in queries or columns with more repeated values.

During the query, if we do not query by a field, it is a waste to create an index on this field. If there is an employee information table, we may query employee information by employee ID, employee name, or place of origin. However, we often do not query by ID card number. Although this ID number is unique. At this time, even if an index is created on this field, the query speed cannot be improved. In contrast, it increases system maintenance time and occupies system space. This is just the way to lift a stone and smash your feet.

In addition, some fields have repeated values in the preceding employee information table. For example, the gender field is mainly "male" and "female", and the position field is limited. In this case, adding indexes to these fields does not significantly increase the query speed and reduce the user response time. On the contrary, because space is required, the overall performance of the database is reduced.

The second rule in database index management is that you do not need to create an index for columns that are rarely involved in queries or columns with a large number of repeated values.

Iron Law 3: it is best to create an index for columns queried by range.

In the information management system, you often need to query certain transaction records by range. For example, in the ERP system, you often need to query the sales orders and shipment status of the current month, which requires querying transaction records by date range. If you find that the stock is incorrect, you also need to check the stock in and out in a certain period of time, such as the stock transaction situation from April 1 to April 3. In this case, the query is based on the date.

You must create an index for these data columns that need to be quickly or frequently queried within a specified range. Because the index has been sorted, the specified range is continuous when it is saved. You can use the index sorting to speed up the query time and reduce the user waiting time.

However, if you may need to query by range, it is better to use the index if the range query conditions are not used much. For example, in the employee information table, you may need to query the details of employees who joined the company before January March 2008 to add benefits to them. However, because there are not many records in the table and similar queries are rarely performed. If the dimension field is used to create an index, although it is harmless, it is clear that the benefit of the index is lower than the cost. For database administrators, it is not worth the candle.

Furthermore, if range query is used, it is best to use the top keyword to limit the results of a query. For example, only the first 500 records are displayed in sequence for the first time. Using Top keywords with ranges can greatly improve the query efficiency.

Iron 4: If a table has a primary key or a foreign key, you must create an index for it.

To define an index column with a primary key, you must create an index for it. Because the primary key can be accelerated to a row in the table. Combined with the functions of indexes, the query speed can be doubled. For example, in the employee information table, we usually set employee numbers as primary keys. This not only improves the query speed, but also ensures the uniqueness of employee numbers because the primary key requires the record to be unique. In this case, if the employee ID field is set as an index, the employee ID is used to query the employee information, which is much more efficient than the absence of an index.

In addition, to make the value of a field unique, you can use two indexing methods. One is the primary key index mentioned above. The unique index is used to specify the uniqueness of the field content using the unique keyword. Both methods automatically create a unique index on the specified column in the table. There is no obvious difference between the two methods. The query optimizer does not distinguish which method is used to create a unique index, and they perform data queries in the same way.

If a data column in a table has a foreign key defined, it is best to create an index for this field. Because the foreign key is mainly used to query connections between tables. If an index is created on the foreign key, the connection query between the table and the table can be accelerated. For example, in the employee basic information table, there is a field for employee positions. Due to frequent changes in employee positions, here, only one employee's position is storedCode. The information about the position is recorded in the information table of another position. In this case, the employee position field is a foreign key. If a foreign key is created for this field, the connection speed of the two tables can be significantly improved. In addition, the more records, the more obvious the effect.

Therefore, when a table has a foreign key or primary key, it is best to create an index for it. Through indexes, the primary key and foreign key can be enhanced to improve database performance.

Iron V: do not create indexes for some special data types

In the table, some fields are special. Such as text fields (txt) and Image Fields. If the fields in the table belong to these data types, it is best not to create an index for them. These fields have some common characteristics. If you are not sure about the length, either it is long, a few characters, or it is a null string. For example, the text data type is often used for remarks in the database table of the application system. Sometimes the remarks are long, but sometimes there is no data. If an index is created for a field of this type, it does not work. On the contrary, it increases the burden on the system.

Therefore, exercise caution when creating indexes for some special data types. In general, there is no need to create an index for it. However, there are also special cases. For example, in the ERP system, there is a product information table, and there is a product specification field. Sometimes, the length may be up to 5000 characters. In this case, only the text data type can accommodate such a large amount of data. In addition, when querying, users like to query product information through the specification parameter. If you do not create an index for this field, the query speed will be slow. In this case, the database administrator only sacrifices a little system resources to create an index for it.

It can also be seen from the above that, although the above several rules are true, whether or not the database administrator needs to make a reasonable choice based on the actual situation of the enterprise.

Iron 6: indexes can be integrated with a set of where statements.

When querying information, you may frequently use some limit statements. For example, when querying a sales order, the condition set of the customer and the order date is often used. For example, when querying the inventory transaction of a product, the product number and the condition set of the start and end dates of the transaction are used.

For these data columns that are frequently used in the WHERE clause, the index is created in the Set process of the where clause. For data columns that require acceleration or frequent retrieval, these data columns that frequently participate in the query can be queried by index sorting to speed up the query time.

In short, indexing is like a double-edged sword, that is, it can improve the performance of the database and may play a negative role in the performance of the database. As a database administrator, you must have the ability to create appropriate indexes at the right time, right businesses, and right fields. The above six rules are just some basic requirements for index creation.