Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 251

--checking SalesOrderDetailDemo after an Index on Computed
--Column
SELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000
GO

6. Now, look at the results of our STATISTICS command in the Messages tab. Here is

a copy from my server:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(3864 row(s) affected)
Table 'SalesOrderDetailDemo'. Scan count 1, logical reads 757,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 546 ms, elapsed time = 622 ms.

Please note that you might get different readings in the previous text, in your
installation of SQL Server 2012, as it depends on many parameters like
CPU of server, memory, disk type, disk drive, server load, parallel processes
running on the server, and many more.

How it works...

Creating an index on Computed Columns, stores key values in leaf-level pages and uses index
statistics while executing the SELECT statement, and it works well most of the time. If, for any
reason, the optimizer can't use a Computed Column Index, it will go for a regular index or table
scan. There are many cases (that we have already discussed in the Getting Ready section of
this recipe) where the optimizer won't use a Computed Column Index.

If you observe both copies of the STATISTICS figure, you will find differences in the SQL
Server Parse and Compile time section and also in the SQL Server Execution
Times section. The difference could be larger and better after creating an index on a
Computed Column, if the table is very large (maybe millions of rows) and the calculation
in the Computed Column is complicated.