Databases Reference
In-Depth Information
BEGIN
DECLARE @NetPrice Numeric(10,3)
SET @NetPrice = @TotalPrice + (@TotalPrice*@Freight/100)
RETURN @NetPrice
END
GO
--adding computed column SalesOrderDetailDemo table
ALTER TABLE SalesOrderDetailDemo
ADD [NetPrice] AS [dbo].[UDFTotalAmount] ( OrderQty*UnitPrice,5)
GO
3.
Now, create one clustered index on the table, so that the table doesn't become a
heap, as explained in the previous chapter, and set some
SET
options to measure
performance for each
SELECT
query. After setting the
STATISTICS
option to
ON
,
it will be time to execute one
SELECT
statement on the
SalesOrderDetailDemo
table. Keep in mind that we have not created any index on a Computed Column, yet.
--creating Clustered Index on table.
CREATE Clustered Index idx_SalesOrderID_SalesOrderDetailID_
SalesOrderDetailDemo
ON SalesOrderDetailDemo(SalesOrderID,SalesOrderDetailID)
GO
--checking SalesOrderDetailDemo with statistics option ON to
--measure performance
SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
--checking SELECT statement without having Index on Computed
Column
SELECT * FROM SalesOrderDetailDemo WHERE NetPrice>5000
GO
In the Messages tab of the Result panel, you might receive results of our
STATISTICS
options, such as in the following text:
SQL Server parse and compile time:
CPU time = 650 ms, elapsed time = 650 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(3864 row(s) affected)
Search WWH ::
Custom Search