Database Reference
In-Depth Information
IF EXISTS ( SELECT *
FROM sys.objects AS o
WHERE o.object_id = OBJECT_ID(N'dbo.NewOrderDetail')
AND o.type IN (N'U') )
DROP TABLE dbo.NewOrderDetail;
GO
SELECT *
INTO dbo.NewOrderDetail
FROM Sales.SalesOrderDetail;
GO
CREATE INDEX IX_NewOrders_ProductID ON dbo.NewOrderDetail (ProductID);
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.NewOrders')
AND type IN (N'P',N'PC') )
DROP PROCEDURE dbo.NewOrders;
GO
CREATE PROCEDURE dbo.NewOrders
AS
SELECT nod.OrderQty,
nod.CarrierTrackingNumber
FROM dbo.NewOrderDetail nod
WHERE nod.ProductID = 897;
GO
SET STATISTICS XML ON;
EXEC dbo.NewOrders;
SET STATISTICS XML OFF;
GO
Next you need to modify a number of rows before reexecuting the stored procedure.
UPDATE dbo.NewOrderDetail
SET ProductID = 897
WHERE ProductID BETWEEN 800 AND 900;
GO
SET STATISTICS XML ON;
EXEC dbo.NewOrders;
SET STATISTICS XML OFF;
GO
The first time, SQL Server executes the
SELECT
statement of the stored procedure using an
Index Seek
operation,
as shown in Figure
17-6
.
