Database Optimization Practice: Advanced index, anti-normal text

After index optimization, do you have a performance problem with your database after you refactor the TSQL? It is quite possible that you must find another way to do it. SQL Server also provides some advanced features in indexing that you may never have used, and using advanced indexing can significantly improve system performance, this article starts with advanced indexing technology, and also introduces the inverse paradigm technology.

Step Sixth: Apply the Advanced index

Implementing computed columns and creating indexes on these columns

You may have written the application code from the database to query a result set, which computes each row of the result set to generate the final display output information. For example, you might have a query that retrieves order information from the database, and in the application code you may have calculated the total order price by performing arithmetic operations on the product and sales, but why don't you perform these operations in the database?

Take a look at this picture below, you can specify a formula to use a database table column as a computed column, your TSQL. This computed column is included in the query list, and the SQL engine applies the formula to calculate the value of the column, and when the query is executed, the database engine calculates the order price and returns the result for the computed column.

Figure 1 Computed column

Using computed columns you can give all of your computing work to the backend, but if the table has too many rows, it may not compute well, and if the calculation lists the case in the WHERE clause of the select query, in which case the database will match the value specified by the WHERE clause. The engine has to calculate the value of computed columns in all rows in the table, which is an inefficient process because it always requires a full table scan or a full clustered index scan.

So the question is, how do you improve the performance of computed columns? The workaround is to create an index on a computed column, and when indexed on a computed column, SQL Server computes the results in advance and builds the index on top of the results. Also, the index value on the computed column is updated when the value of the corresponding column (the computed column-dependent column) is updated. Therefore, when you execute a query, the database engine does not perform a single calculation formula for each row in the result set, instead, you can get the computed column's calculated value directly from the index, so creating an index on the computed column will speed up the query.

Tip: If you want to create an index on a computed column, you must make sure that the formula on the computed column cannot include any "indeterminate" functions, such as GETDATE (), which is an indeterminate function, because it returns a different value each time it is invoked.

Create an indexed view

Do you know that you can create an index on a view? OK, do not know that it does not matter, read my introduction you will understand.

Why should I use a view?

As we all know, the view itself does not store any data, just a compiled SELECT statement. The database generates an execution plan for the view that can be reused because the execution plan can also be reused.

The view itself does not create performance improvements, I used to think it would "remember" the query results, but later I realized that it was nothing other than a compiled query and that the view simply didn't remember the query, and I bet a lot of people who had just come in contact with SQL would have the wrong idea.

But now I'm going to show you a way to make the view remember the query results, which is very simple, is to create an index on the view.

If you apply an index to a view, the view becomes an indexed view, and for an indexed view, the database engine processes SQL and stores the results in a data file, similar to a clustered table, where SQL Server automatically maintains the index when the data in the underlying table changes, so when you query on the indexed view, The database engine simply looks up the value from the index, and of course it's fast, so creating an index on the view can significantly speed up the query.

But please note that there is no free lunch in the world, creating an indexed view can improve performance, and when data in the underlying table changes, the database engine also updates the index, so when the view has a lot of rows to handle, and requirements and, when the data and underlying tables don't change frequently, you should consider creating an indexed view.