Use computed columns to improve query performance

Generally, you should avoid using functions in the condition when writing SQL statements, because the index cannot be effectively used and an efficient execution plan cannot be generated.

SQL Server provides columns to help us solve this problem. Let's take a common example. Many companies use SQL Server collcation to be case sensitive, because there is no way to control user input (of course inProgramCan also be converted, for example, all converted to uppercase, but many programmers did not consider it at the time of design), so it is necessary to force conversion when making a query.

The following statement cannot use the index:
Select * from t wherelower (C1) = 'az '.

However, we can solve this problem by adding computing columns:

Setstatistics profile on
Go
-- Note that this query does table scan because of the lower Function Applied
Select * from t where lower (C1) = '00'
Go
Set statistics profile off
Go
-- Let's add a computed column and create index on this computed Column
Alter table t add C2 as lower (C1) persisted
O
Create index indx_c2 on T (C2)
Go
Set statistics profile on
Go
-- Note that this query does index seek
Select * from t where lower (C1) = '00'
Go
Set statistics profile off

After adding an index column, let's take a look at the results. Before calculating a column index, we can only use table scan. After adding a calculated column index, we can use seek.

Note:: If the persisted keyword is not used, the calculated column is a virtual column not actually stored in the table. Each time a column is referenced in a query, their values are recalculated. The computed columns using the persisted keyword are actually stored in the table. If any column is involved in the calculation change of the calculated column, the calculated column value is updated.

In addition, the computed column expression can be a column name, constant, or function of a non-computed column, or any combination of the above elements connected by one or more operators.

 

Because the storage and computing columns require extra space and the functions are complex, CPU resources are required. Therefore, you need to perform a test first.

 

For more information about calculation columns, see msdn:Http://msdn.microsoft.com/zh-cn/library/ms191250 (V = SQL .105). aspx