This code is not in the same order as the book. A number of edits were done to the document after it was produced.
USE [AdventureWorks];
GO
–Listing 1
–Select
–highlight this and click “Display Estimated Plan” on tool bar, right click & select from menu,
–select from the Query menu above, or type CTL-L.
SELECT * FROM [dbo].[DatabaseLog];
GO
–Listing 2
–SelectJoin
— click “Include Actual Excecution Plan” on tool bar, highlight and execute
SELECT e.[Title]
,a.[City]
,c.[LastName] + ‘,’ + c.[FirstName] AS EmployeeName
FROM [HumanResources].[Employee] e
JOIN [HumanResources].[EmployeeAddress] ed
ON e.[EmployeeID] = ed.[EmployeeID]
JOIN [Person].[Address] a
ON [ed].[AddressID] = [a].[AddressID]
JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID] ;
–Listing 3
–SelectJoin2
SELECT e.[Title]
,a.[City]
,c.[LastName] + ‘,’ + c.[FirstName] AS EmployeeName
FROM [HumanResources].[Employee] e
JOIN [HumanResources].[EmployeeAddress] ed
ON e.[EmployeeID] = ed.[EmployeeID]
JOIN [Person].[Address] a
ON [ed].[AddressID] = [a].[AddressID]
JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID]
WHERE e.[Title] = ‘Production Technician – WC20’ ;
–Listing 4
–Text Plan and XML Plan
–SET SHOWPLAN_ALL ON
–SET SHOWPLAN_TEXT ON
SET SHOWPLAN_XML ON;
GO
SET STATISTICS XML ON;
GO
SELECT c.[LastName]
,a.[City]
,cu.[AccountNumber]
,st.[Name] AS TerritoryName
FROM [Person].[Contact] c
JOIN [Sales].[Individual] i
ON c.[ContactID] = i.[ContactID]
JOIN [Sales].[CustomerAddress] ca
ON i.[CustomerID] = ca.[CustomerID]
JOIN Person.Address a
ON [ca].[AddressID] = [a].[AddressID]
JOIN [Sales].Customer cu
ON cu.[CustomerID] = i.[CustomerID]
JOIN [Sales].[SalesTerritory] st
ON [cu].[TerritoryID] = [st].[TerritoryID]
WHERE st.[Name] = ‘Northeast’
AND a.[StateProvinceID] = 55 ;
GO
–SET SHOWPLAN_ALL OFF
–SET SHOWPLAN_TEXT OFF
SET SHOWPLAN_XML OFF;
GO
–Listing 5
–Insert 1
INSERT INTO [AdventureWorks].[Person].[Address]
(
[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate]
)
VALUES (
‘1313 Mockingbird Lane’
,’Basement’
,’Springfield’
,24
,’02134′
,NEWID()
,GETDATE()
) ;
— Listing 6
–Update1
SET SHOWPLAN_XML ON;
GO
UPDATE [Person].[Address]
SET [City] = ‘Munro’
,[ModifiedDate] = GETDATE()
WHERE [City] = ‘Monroe’ ;
GO
SET SHOWPLAN_XML OFF;
GO
— Listing 7
— Delete
DELETE FROM [Person].[Address]
WHERE [AddressID] = 52 ;
— Listing 8
— Order By
SELECT *
FROM [Production].[ProductInventory]
ORDER BY [Shelf] ;
— Listing 9
— Order By Cluster
SET SHOWPLAN_XML ON;
GO
SET STATISTICS XML ON;
GO
SELECT *
FROM [Production].[ProductInventory]
ORDER BY [ProductID] ;
GO
SET SHOWPLAN_XML OFF;
GO
SET STATISTICS XML OFF;
GO
— Listing 10
SELECT [City]
,COUNT([City]) AS CityCount
FROM [Person].[Address]
GROUP BY [City]
HAVING COUNT([City]) > 1 ;
— Listing 11
— different plans
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[NewOrders]’)
AND type in ( N’U’ ) )
DROP TABLE [NewOrders]
GO
SELECT *
INTO NewOrders
FROM Sales.SalesOrderDetail
GO
CREATE INDEX IX_NewOrders_ProductID on NewOrders ( ProductID )
GO
SET SHOWPLAN_XML ON
— Estimated Plan
GO
SELECT [OrderQty]
,[CarrierTrackingNumber]
FROM NewOrders
WHERE [ProductID] = 897
GO
SET SHOWPLAN_XML OFF
GO
BEGIN TRAN
UPDATE NewOrders
SET [ProductID] = 897
WHERE [ProductID] between 800 and 900
GO
SET STATISTICS XML ON
— Actual Plan
GO
SELECT [OrderQty]
,[CarrierTrackingNumber]
FROM NewOrders
WHERE [ProductID] = 897
ROLLBACK TRAN
GO
SET STATISTICS XML OFF
GO
— Listing 12
— New table fails estimated plan
CREATE TABLE TempTable
(
Id INT IDENTITY(1, 1)
,Dsc NVARCHAR(50)
);
INSERT INTO TempTable ( Dsc )
SELECT [Name]
FROM [Sales].[Store];
SELECT *
FROM TempTable;
DROP TABLE TempTable;
— Listing 13
— Stored Proc
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[Sales].[spTaxRateByState]’)
AND type in ( N’P’, N’PC’ ) )
DROP PROCEDURE [Sales].[spTaxRateByState];
GO
CREATE PROCEDURE [Sales].[spTaxRateByState]
@CountryRegionCode NVARCHAR(3)
AS
SET NOCOUNT ON;
SELECT [st].[SalesTaxRateID]
,[st].[Name]
,[st].[TaxRate]
,[st].[TaxType]
,[sp].[Name] AS StateName
FROM [Sales].[SalesTaxRate] st
JOIN [Person].[StateProvince] sp
ON [st].[StateProvinceID] = [sp].[StateProvinceID]
WHERE [sp].[CountryRegionCode] = @CountryRegionCode
ORDER BY [StateName]
GO
EXEC [Sales].[spTaxRateByState] @CountryRegionCode = ‘US’;
–Listing 14
–Derived Tables
SELECT [p].[Name]
,[p].[ProductNumber]
,[ph].[ListPrice]
FROM [Production].[Product] p
INNER JOIN [Production].[ProductListPriceHistory] ph
ON [p].[ProductID] = ph.[ProductID]
AND ph.[StartDate] = ( SELECT TOP ( 1 )
[ph2].[StartDate]
FROM [Production].[ProductListPriceHistory] ph2
WHERE [ph2].[ProductID] = [p].[ProductID]
ORDER BY [ph2].[StartDate] DESC
)
WHERE p.ProductID = 839;
–Listing 15
–CROSS APPLY
SELECT [p].[Name]
,[p].[ProductNumber]
,[ph].[ListPrice]
FROM [Production].[Product] p
CROSS APPLY ( SELECT TOP ( 1 )
[ph2].[ProductID]
,[ph2].[ListPrice]
FROM [Production].[ProductListPriceHistory] ph2
WHERE [ph2].[ProductID] = [p].[ProductID]
ORDER BY [ph2].[StartDate] DESC
) ph
WHERE [p].[ProductID] = ‘839’;
–Listing 16
–Adventureworks Procedure
SET STATISTICS XML ON;
GO
EXEC [dbo].[uspGetEmployeeManagers] @EmployeeID = 9;
GO
SET STATISTICS XML OFF;
GO
–Listing 17
–Views
SELECT *
FROM [Sales].[vIndividualCustomer]
WHERE [CustomerID] = 26131;
–Listing 18
–Indexed Views
SELECT *
FROM [Person].[vStateProvinceCountryRegion]
–Listing 18
–Accessing Indexed Views
SELECT sp.[Name] AS [StateProvinceName]
,cr.[Name] AS [CountryRegionName]
FROM [Person].[StateProvince] sp
INNER JOIN [Person].[CountryRegion] cr
ON sp.[CountryRegionCode] = cr.[CountryRegionCode] ;
–Listing 19
–Indexed Views Not Working
SELECT a.[City]
,v.[StateProvinceName]
,v.[CountryRegionName]
FROM [Person].[Address] a
JOIN [Person].[vStateProvinceCountryRegion] v
ON [a].[StateProvinceID] = [v].[StateProvinceID]
WHERE [a].[AddressID] = 22701 ;
–Listing 20
–Parallel execution. Set max degree of parallelism to 1 prior to running proc
sp_configure ‘cost threshold for parallelism’, 1 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
SET STATISTICS XML ON ;
GO
SELECT [so].[ProductID]
,COUNT(*) AS Order_Count
FROM [Sales].[SalesOrderDetail] so
WHERE [so].[ModifiedDate] >= ‘2003/02/01’
AND [so].[ModifiedDate] < DATEADD(mm, 3, ‘2003/02/01’)
GROUP BY [so].[ProductID]
ORDER BY [so].[ProductID] ;
GO
SET STATISTICS XML OFF ;
GO
sp_configure ‘cost threshold for parallelism’, 5 ;
GO
RECONFIGURE WITH OVERRIDE ;
GO
–Listing 21
–bookmark lookup
SELECT [sod].[ProductID]
,[sod].[OrderQty]
,[sod].[UnitPrice]
FROM [Sales].[SalesOrderDetail] sod
WHERE [sod].[ProductID] = 897
–Listing 22
–Fixing the bookmark lookup
IF EXISTS ( SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]’)
AND name = N’IX_SalesOrderDetail_ProductID’ )
DROP INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
WITH ( ONLINE = OFF ) ;
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ( [ProductID] ASC )
INCLUDE ( [OrderQty], [UnitPrice] ) WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON )
ON [PRIMARY] ;
GO
SET STATISTICS XML ON ;
GO
SELECT [sod].[ProductID]
,[sod].[OrderQty]
,[sod].[UnitPrice]
FROM [Sales].[SalesOrderDetail] sod
WHERE [sod].[ProductID] = 897 ;
GO
SET STATISTICS XML OFF ;
GO
IF EXISTS ( SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]’)
AND name = N’IX_SalesOrderDetail_ProductID’ )
DROP INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail]
WITH ( ONLINE = OFF ) ;
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID] ON [Sales].[SalesOrderDetail] ( [ProductID] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ;
GO
EXEC sys.sp_addextendedproperty @name = N’MS_Description’,
@value = N’Nonclustered index.’, @level0type = N’SCHEMA’,
@level0name = N’Sales’, @level1type = N’TABLE’,
@level1name = N’SalesOrderDetail’, @level2type = N’INDEX’,
@level2name = N’IX_SalesOrderDetail_ProductID’ ;
–Listing 23
— Indexes
SELECT sod.[ProductID]
,sod.[SalesOrderID]
,sod.[SalesOrderDetailID]
FROM [Sales].[SalesOrderDetail] sod
WHERE sod.[ProductID] = 991
ORDER BY sod.[ProductID] DESC
–Listing 24
— Indexes not covering
SELECT sod.[ProductID]
,sod.[SalesOrderID]
,sod.[SalesOrderDetailID]
,sod.[LineTotal]
FROM [Sales].[SalesOrderDetail] sod
WHERE sod.[ProductID] = 991
ORDER BY sod.[ProductID] DESC
–Listing 25
— A New index
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_OrderQty] ON [Sales].[SalesOrderDetail] ( [OrderQty] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
SELECT sod.OrderQty
,sod.[SalesOrderID]
,sod.[SalesOrderDetailID]
,sod.[LineTotal]
fROM [Sales].[SalesOrderDetail] sod
WHERE sod.[OrderQty] = 10
DROP INDEX [Sales].[SalesOrderDetail].[IX_SalesOrderDetail_OrderQty]
— Listing 26
— Plan reuse
SELECT [cp].[refcounts]
,[cp].[usecounts]
,[cp].[objtype]
,[st].[dbid]
,[st].[objectid]
,[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp ;
— Listing 27
— Query Hints
—
SELECT …
OPTION (<hint>,<hint>…)
–Listing 28
— HASH|ORDER GROUP
SELECT [c].[Suffix]
,COUNT([c].[Suffix]) AS SuffixUsageCount
FROM [Person].[Contact] c
GROUP BY [Suffix]
OPTION ( ORDER GROUP )
–Listing 29
–MERGE UNION
SELECT [pm1].[Name],
[pm1].[ModifiedDate]
FROM [Production].[ProductModel] pm1
UNION
SELECT [pm2].[Name],
[pm2].[ModifiedDate]
FROM [Production].[ProductModel] pm2
OPTION (MERGE UNION)
–Listing 30
–HASH UNION
SELECT [pm1].[Name]
,[pm1].[ModifiedDate]
FROM [Production].[ProductModel] pm1
UNION
SELECT [pm2].[Name]
,[pm2].[ModifiedDate]
FROM [Production].[ProductModel] pm2
OPTION ( HASH UNION )
–Listing 31
–JOIN
SELECT s.[Name] AS StoreName
,ct.[Name] AS ContactTypeName
,c.[LastName] + ‘, ‘ + c.[LastName]
FROM [Sales].[Store] s
JOIN [Sales].[StoreContact] sc
ON [s].[CustomerID] = [sc].[CustomerID]
JOIN [Person].[Contact] c
ON [sc].[ContactID] = [c].[ContactID]
JOIN [Person].[ContactType] ct
ON [sc].[ContactTypeID] = [ct].[ContactTypeID]
OPTION ( LOOP JOIN )
–OPTION ( MERGE JOIN )
–OPTION ( HASH JOIN )
–Listing 32
–FAST n
SELECT *
FROM [Sales].[SalesOrderDetail] sod
JOIN [Sales].[SalesOrderHeader] soh
ON [sod].[SalesOrderID] = [soh].[SalesOrderID]
OPTION (FAST 1)
–Listing 33
— FORCER ORDER
SELECT pc.[Name] ProductCategoryName
,psc.[Name] ProductSubCategoryName
,p.[Name] ProductName
,pd.[Description]
,pm.[Name] ProductModelName
,c.[Name] CultureName
,d.[FileName]
,pri.[Quantity]
,pr.[Rating]
,pr.[Comments]
FROM [Production].[Product] p
LEFT JOIN [Production].[ProductModel] pm
ON [p].[ProductModelID] = [pm].[ProductModelID]
LEFT JOIN [Production].[ProductDocument] pdo
ON p.[ProductID] = pdo.[ProductID]
LEFT JOIN [Production].[ProductSubcategory] psc
ON [p].[ProductSubcategoryID] = [psc].[ProductSubcategoryID]
LEFT JOIN [Production].[ProductInventory] pri
ON p.[ProductID] = pri.[ProductID]
LEFT JOIN [Production].[ProductReview] pr
ON p.[ProductID] = pr.[ProductID]
LEFT JOIN [Production].[Document] d
ON pdo.[DocumentID] = d.[DocumentID]
LEFT JOIN [Production].[ProductCategory] pc
ON [pc].[ProductCategoryID] = [psc].[ProductCategoryID]
LEFT JOIN [Production].[ProductModelProductDescriptionCulture] pmpd
ON pmpd.[ProductModelID] = pm.[ProductModelID]
LEFT JOIN [Production].[ProductDescription] pd
ON pmpd.[ProductDescriptionID] = pd.[ProductDescriptionID]
LEFT JOIN [Production].[Culture] c
ON c.[CultureID] = pmpd.[CultureID]
OPTION (FORCE ORDER)
–Listing 34
— MAXDOP
sp_configure ‘cost threshold for parallelism’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
SELECT wo.[DueDate]
,MIN(wo.[OrderQty]) MinOrderQty
,MIN(wo.[StockedQty]) MinStockedQty
,MIN(wo.[ScrappedQty]) MinScrappedQty
,MAX(wo.[OrderQty]) MaxOrderQty
,MAX(wo.[StockedQty]) MaxStockedQty
,MAX(wo.[ScrappedQty]) MaxScrappedQty
FROM [Production].[WorkOrder] wo
GROUP BY wo.[DueDate]
ORDER BY wo.[DueDate]
OPTION (MAXDOP 1);
GO
sp_configure ‘cost threshold for parallelism’, 5;
GO
RECONFIGURE WITH OVERRIDE;
GO
–Listing 35
— OPTIMIZE FOR
DECLARE @City NVARCHAR(30);
SET @City = ‘Newark’
SELECT * FROM [Person].[Address]
WHERE [City] = @City;
SET @City = ‘London’
SELECT * FROM [Person].[Address]
WHERE [City] = @City
OPTION (OPTIMIZE FOR (@City = ‘Newark’));
–Listing 36
— RECOMPILE
DECLARE @PersonId INT
SET @PersonId = 277
SELECT [soh].[SalesOrderNumber]
,[soh].[OrderDate]
,[soh].[SubTotal]
,[soh].[TotalDue]
FROM [Sales].[SalesOrderHeader] soh
WHERE [soh].[SalesPersonID] = @PersonId
OPTION(RECOMPILE) ;
SET @PersonId = 288
SELECT [soh].[SalesOrderNumber]
,[soh].[OrderDate]
,[soh].[SubTotal]
,[soh].[TotalDue]
FROM [Sales].[SalesOrderHeader] soh
WHERE [soh].[SalesPersonID] = @PersonId
OPTION(RECOMPILE) ;
–Listing 37
— EXPAND VIEWS
SELECT *
FROM [Person].[vStateProvinceCountryRegion]
OPTION ( EXPAND VIEWS );
–Listing 38
— Join Hints
SELECT [pm].[Name]
,[pm].[CatalogDescription]
,p.[Name] AS ProductName
,i.[Diagram]
FROM [Production].[ProductModel] pm
LEFT MERGE JOIN [Production].[Product] p
ON [pm].[ProductModelID] = [p].[ProductModelID]
LEFT JOIN [Production].[ProductModelIllustration] pmi
ON [pm].[ProductModelID] = [pmi].[ProductModelID]
LEFT JOIN [Production].[Illustration] i
ON [pmi].[IllustrationID] = [i].[IllustrationID]
WHERE [pm].[Name] LIKE ‘%Mountain%’
ORDER BY [pm].[Name] ;
–Table Hints
–Listing 39
–NOEXPAND
SELECT a.[City]
,v.[StateProvinceName]
,v.[CountryRegionName]
FROM [Person].[Address] a
JOIN [Person].[vStateProvinceCountryRegion] v WITH (NOEXPAND)
ON [a].[StateProvinceID] = [v].[StateProvinceID]
WHERE [a].[AddressID] = 22701 ;
–Listing 40
–INDEX
SELECT [de].[Name]
,[e].[Title]
,[c].[LastName] + ‘, ‘ + [c].[FirstName]
FROM [HumanResources].[Department] de –WITH(INDEX(PK_Department_DepartmentID))
JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON [de].[DepartmentID] = [edh].[DepartmentID]
JOIN [HumanResources].[Employee] e
ON [edh].[EmployeeID] = [e].[EmployeeID]
JOIN [Person].[Contact] c
ON [e].[ContactID] = [c].[ContactID]
WHERE [de].[Name] LIKE ‘P%’
–Listing 41
–FASTFIRSTROW
SELECT [pm].[Name] AS ProductModelName
,[p].[Name] AS ProductName
,SUM([pin].[Quantity])
FROM [Production].[ProductModel] pm
JOIN [Production].[Product] p WITH ( FASTFIRSTROW )
ON [pm].[ProductModelID] = [p].[ProductModelID]
JOIN [Production].[ProductInventory] pin
ON [p].[ProductID] = [pin].[ProductID]
GROUP BY [pm].[Name]
,[p].[Name]
–Listing 42
–Simple Cursor
DECLARE CurrencyList CURSOR FOR
SELECT CurrencyCode FROM [Sales].[Currency]
WHERE Name LIKE ‘%Dollar%’
OPEN CurrencyList
FETCH NEXT FROM CurrencyList
WHILE @@FETCH_STATUS = 0
BEGIN
–Normally there would be operations here using data from cursor
FETCH NEXT FROM CurrencyList
END
CLOSE CurrencyList
DEALLOCATE CurrencyList
GO
–Listing 43
— Big Query
CREATE PROCEDURE [Sales].[uspGetDiscountRates]
(
@ContactId INT
,@SpecialOfferId int
)
AS
BEGIN TRY
— determine if sale using special offer exists
IF EXISTS ( SELECT *
FROM [Sales].[Individual] i
INNER JOIN [Sales].[Customer] c
ON i.CustomerID = c.CustomerID
INNER JOIN [Sales].[SalesOrderHeader] soh
ON soh.CustomerID = c.CustomerID
INNER JOIN [Sales].[SalesOrderDetail] sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
INNER JOIN [Sales].[SpecialOffer] spo
ON sod.[SpecialOfferID] = spo.[SpecialOfferID]
WHERE i.[ContactID] = @ContactId
AND spo.[SpecialOfferID] = @SpecialOfferId )
BEGIN
SELECT c.[LastName] + ‘, ‘ + c.[FirstName]
,c.[EmailAddress]
,i.[Demographics]
,spo.[Description]
,spo.[DiscountPct]
,sod.[LineTotal]
,p.[Name]
,p.[ListPrice]
,sod.[UnitPriceDiscount]
FROM [Person].[Contact] c
INNER JOIN [Sales].[Individual] i
ON c.[ContactID] = i.[ContactID]
INNER JOIN [Sales].[Customer] cu
ON i.[CustomerID] = cu.[CustomerID]
INNER JOIN [Sales].[SalesOrderHeader] soh
ON cu.[CustomerID] = soh.[CustomerID]
INNER JOIN [Sales].[SalesOrderDetail] sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
INNER JOIN [Sales].[SpecialOffer] spo
ON sod.[SpecialOfferID] = spo.[SpecialOfferID]
INNER JOIN [Production].[Product] p
ON sod.[ProductID] = p.[ProductID]
WHERE c.ContactID = @ContactId
AND sod.[SpecialOfferID] = @SpecialOfferId;
END
— use different query to return other data set
ELSE
BEGIN
SELECT c.[LastName] + ‘, ‘ + c.[FirstName]
,c.[EmailAddress]
,i.[Demographics]
,soh.SalesOrderNumber
,sod.[LineTotal]
,p.[Name]
,p.[ListPrice]
,sod.[UnitPrice]
,st.[Name] AS StoreName
,ec.[LastName] + ‘, ‘ + ec.[FirstName] AS SalesPersonName
FROM [Person].[Contact] c
INNER JOIN [Sales].[Individual] i
ON c.[ContactID] = i.[ContactID]
INNER JOIN [Sales].[SalesOrderHeader] soh
ON i.[CustomerID] = soh.[CustomerID]
INNER JOIN [Sales].[SalesOrderDetail] sod
ON soh.[SalesOrderID] = sod.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON sod.[ProductID] = p.[ProductID]
LEFT JOIN [Sales].[SalesPerson] sp
ON soh.SalesPersonID = sp.SalesPersonID
LEFT JOIN [Sales].[Store] st
ON sp.SalesPersonID = st.SalesPersonID
LEFT JOIN [HumanResources].[Employee] e
ON sp.SalesPersonID = e.[EmployeeID]
LEFT JOIN Person.[Contact] ec
ON e.[ContactID] = ec.[ContactID]
WHERE i.[ContactID] = @ContactId;
END
–second result set
IF @SpecialOfferId = 16
BEGIN
SELECT p.[Name]
,p.[ProductLine]
FROM [Sales].[SpecialOfferProduct] sop
INNER JOIN [Production].[Product] p
ON sop.[ProductID] = p.[ProductID]
WHERE sop.[SpecialOfferID] = 16;
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage ;
RETURN ERROR_NUMBER() ;
END CATCH
RETURN 0 ;
;
–Listing 44
–Execute Big Query
EXEC [Sales].[uspGetDiscountRates]
@ContactId = 12298, — int
@SpecialOfferId = 16 — int
–Listing 45
–FOR XML AUTO
SELECT c.[FirstName]
,c.[LastName]
,c.[EmailAddress]
,c.[Phone]
,e.[EmployeeID]
,e.[Gender]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,a.[StateProvinceID]
,a.[PostalCode]
FROM [Person].[Contact] c
INNER JOIN [HumanResources].[Employee] e
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
FOR XML RAW
–Listing 46
–XML FOR XML EXPLICIT UNION
SELECT 1 as Tag,
NULL as Parent,
s.Name as [Store!1!StoreName],
NULL as [Contact!2!ContactID],
NULL as [Contact!2!ContactTypeID]
FROM Sales.Store s
JOIN [Sales].[StoreContact] c
ON s.[CustomerID] = c.[CustomerID]
UNION ALL
SELECT 2 as Tag,
1 as Parent,
s.Name AS StoreName,
c.ContactID,
c.ContactTypeID
FROM Sales.Store s
JOIN [Sales].[StoreContact] c
ON s.[CustomerID] = c.[CustomerID]
ORDER BY [Store!1!StoreName],[Contact!2!ContactID]
FOR XML EXPLICIT
–Listing 47
–XML FOR XML EXPLICIT NESTED
SELECT s.[Name] AS StoreName,
(SELECT c.ContactID,c.ContactTypeID
FROM [Sales].[StoreContact] c
WHERE c.[CustomerID] = s.[CustomerID]
FOR XML AUTO, TYPE, ELEMENTS)
FROM [Sales].[Store] s
ORDER BY s.[Name]
FOR XML AUTO, TYPE
–Listing 48
–XML FOR XML PATH
SELECT s.[Name] AS “@StoreName”
,c.[ContactID] AS “StoreContact/@ContactId”
,c.[ContactTypeID] AS “StoreContact/@ContactTypeID”
FROM [Sales].[Store] s
JOIN [Sales].[StoreContact] c
ON s.[CustomerID] = c.[CustomerID]
ORDER BY s.[Name]
FOR XML PATH
–Listing 49
–OPEN XML
BEGIN TRAN
DECLARE @iDoc AS INTEGER
DECLARE @Xml AS NVARCHAR(max)
SET @Xml = ‘<ROOT>
<Currency CurrencyCode=”UTE” CurrencyName=”Universal Transactional Exchange”>
<CurrencyRate FromCurrencyCode=”USD” ToCurrencyCode=”UTE” CurrencyRateDate=”1/1/2007″ AverageRate=”.553″ EndOfDayRate= “.558″ />
<CurrencyRate FromCurrencyCode=”USD” ToCurrencyCode=”UTE” CurrencyRateDate=”6/1/2007″ AverageRate=”.928″ EndOfDayRate= “1.057” />
</Currency>
</ROOT>’
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Xml
INSERT INTO [Sales].[Currency]
(
[CurrencyCode]
,[Name]
,[ModifiedDate]
)
SELECT CurrencyCode
,CurrencyName
,GETDATE()
FROM OPENXML (@iDoc, ‘ROOT/Currency’,1) WITH ( CurrencyCode NCHAR(3), CurrencyName NVARCHAR(50) )
INSERT INTO [Sales].[CurrencyRate]
(
[CurrencyRateDate]
,[FromCurrencyCode]
,[ToCurrencyCode]
,[AverageRate]
,[EndOfDayRate]
,[ModifiedDate]
)
SELECT CurrencyRateDate
,FromCurrencyCode
,ToCurrencyCode
,AverageRate
,EndOfDayRate
,GETDATE()
FROM OPENXML(@iDoc , ‘ROOT/Currency/CurrencyRate’,2) WITH ( CurrencyRateDate DATETIME ‘@CurrencyRateDate’, FromCurrencyCode NCHAR(3) ‘@FromCurrencyCode’, ToCurrencyCode NCHAR(3) ‘@ToCurrencyCode’, AverageRate MONEY ‘@AverageRate’, EndOfDayRate MONEY ‘@EndOfDayRate’ )
EXEC sp_xml_removedocument @iDoc
ROLLBACK TRAN
–Listing 50
–XML XQuery query
SELECT s.Demographics.query(‘
declare namespace ss=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey”;
for $s in /ss:StoreSurvey
where ss:StoreSurvey/ss:SquareFeet > 20000
return $s
‘) as Demographics
FROM [Sales].[Store] s
WHERE s.[SalesPersonID] = 279
–Listing 51
–XML XQuery exists
SELECT c.[LastName]
,c.[FirstName]
,e.[HireDate]
,e.[Title]
FROM [Person].[Contact] c
INNER JOIN [HumanResources].[Employee] e
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[JobCandidate] jc
ON e.[EmployeeID] = jc.[EmployeeID]
and jc.[Resume].exist(‘
declare namespace res=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume”;
/res:Resume/res:Employment/res:Emp.JobTitle[contains(.,”Sales Manager”)]’) = 1
–Listing 52
–FORCED PARAMETERIZATION
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION FORCED
GO
DBCC freeproccache
GO
SELECT 42 AS TheAnswer
,c.[EmailAddress]
,e.[BirthDate]
,a.[City]
FROM [Person].[Contact] c
JOIN [HumanResources].[Employee] e
ON c.[ContactID] = e.[ContactID]
JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
JOIN [Person].[StateProvince] sp
ON a.[StateProvinceID] = sp.[StateProvinceID]
WHERE c.[EmailAddress] LIKE ‘david%’
AND sp.[StateProvinceCode] = ‘WA’ ;
ALTER DATABASE AdventureWorks
SET PARAMETERIZATION SIMPLE
GO
SELECT [cp].[refcounts]
,[cp].[usecounts]
,[cp].[objtype]
,[st].[dbid]
,[st].[objectid]
,[st].[text]
,[qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp ;
–Listing 53
–Plan Guide Object
EXEC sp_create_plan_guide @name = N’MyFirstPlanGuide’,
@stmt = N’WITH [EMP_cte]([EmployeeID], [ManagerID], [FirstName], [LastName], [RecursionLevel]) — CTE name and columns
AS (
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], 0 — Get the initial list of Employees for Manager n
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID]
WHERE [ManagerID] = @ManagerID
UNION ALL
SELECT e.[EmployeeID], e.[ManagerID], c.[FirstName], c.[LastName], [RecursionLevel] + 1 — Join recursive member to anchor
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[ManagerID] = [EMP_cte].[EmployeeID]
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID]
)
— Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[ManagerID], c.[FirstName] AS ”ManagerFirstName”, c.[LastName] AS ”ManagerLastName”,
[EMP_cte].[EmployeeID], [EMP_cte].[FirstName], [EMP_cte].[LastName] — Outer select from the CTE
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[ManagerID] = e.[EmployeeID]
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.[ContactID]
ORDER BY [RecursionLevel], [ManagerID], [EmployeeID]
OPTION (MAXRECURSION 25) ‘, @type = N’OBJECT’,
@module_or_batch = N’dbo.uspGetManagerEmployees’, @params = NULL,
@hints = N’OPTION(RECOMPILE,MAXRECURSION 25)’
–Listing 54
–Plan Guide SQL
EXEC sp_create_plan_guide @name = N’MySecondPlanGuide’,
@stmt = N’SELECT * FROM [Person].[Address] WHERE [City] = @0′,
@type = N’SQL’,
@module_or_batch = NULL,
@params = N’@0 VARCHAR(8000)’,
@hints = N’OPTION(OPTIMIZE FOR (@0 = ”Newark”))’
DECLARE @my_templatetext nvarchar(max)
DECLARE @my_parameters nvarchar(max)
EXEC sp_get_query_template @templatetext = N’SELECT * FROM [Person].[Address]
WHERE [City] = ”LONDON”’,
@templatetext = @my_templatetext OUTPUT,
@paremeters = @my_parameters OUTPUT
–Listing 55
–Plan Guide TEMPLATE
EXEC sp_create_plan_guide @name = N’MyThirdPlanGuide’,
@stmt = N’SELECT 42 AS TheAnswer
,c.[EmailAddress]
,e.[BirthDate]
,a.[City]
FROM [Person].[Contact] c
JOIN [HumanResources].[Employee] e
ON c.[ContactID] = e.[ContactID]
JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
JOIN [Person].[StateProvince] sp
ON a.[StateProvinceID] = sp.[StateProvinceID]
WHERE c.[EmailAddress] LIKE ”david%”
AND sp.[StateProvinceCode] = ”WA”’,
@type = N’TEMPLATE’,
@module_or_batch = NULL,
@params = N’@0 VARCHAR(8000)’,
@hints = N’OPTION(PARAMETERIZATION FORCED)’
–Listing 56
–Plan Forcing — USE PLAN
CREATE PROCEDURE Sales.uspGetCreditInfo ( @SalesPersonID INT )
AS
SELECT soh.[AccountNumber]
,soh.[CreditCardApprovalCode]
,soh.[CreditCardID]
,soh.[OnlineOrderFlag]
FROM [Sales].[SalesOrderHeader] soh
WHERE soh.[SalesPersonID] = @SalesPersonID
GO
SET STATISTICS XML ON
GO
SELECT soh.[AccountNumber]
,soh.[CreditCardApprovalCode]
,soh.[CreditCardID]
,soh.[OnlineOrderFlag]
FROM [Sales].[SalesOrderHeader] soh
WHERE soh.[SalesPersonID] = 288;
GO
SET STATISTICS XML OFF
GO
EXEC [Sales].uspGetCreditInfo @SalesPersonID = 288 –277
EXEC sp_create_plan_guide
@name = N’UsePlanPlanGuide’,
@stmt = N’SELECT soh.[AccountNumber]
,soh.[CreditCardApprovalCode]
,soh.[CreditCardID]
,soh.[OnlineOrderFlag]
FROM [Sales].[SalesOrderHeader] soh
WHERE soh.[SalesPersonID] = @SalesPersonID –288 –277′,
@type = N’OBJECT’,
@module_or_batch = N’Sales.uspGetCreditInfo’,
@params = NULL,
@hints = N’OPTION(USE PLAN N”<ShowPlanXML xmlns=”http://schemas.microsoft.com/sqlserver/2004/07/showplan” Version=”1.0″ Build=”9.00.3042.00″>
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText=”SELECT [soh].[AccountNumber],[soh].[CreditCardApprovalCode],[soh].[CreditCardID],[soh].[OnlineOrderFlag] FROM [Sales].[SalesOrderHeader] [soh] WHERE [soh].[SalesPersonID]=@1″ StatementId=”1″ StatementCompId=”1″ StatementType=”SELECT” StatementSubTreeCost=”0.0523046″ StatementEstRows=”16″ StatementOptmLevel=”FULL” StatementOptmEarlyAbortReason=”GoodEnoughPlanFound”>
<StatementSetOptions QUOTED_IDENTIFIER=”false” ARITHABORT=”true” CONCAT_NULL_YIELDS_NULL=”false” ANSI_NULLS=”false” ANSI_PADDING=”false” ANSI_WARNINGS=”false” NUMERIC_ROUNDABORT=”false” />
<QueryPlan DegreeOfParallelism=”1″ CachedPlanSize=”16″ CompileTime=”7″ CompileCPU=”7″ CompileMemory=”192″>
<RelOp NodeId=”0″ PhysicalOp=”Nested Loops” LogicalOp=”Inner Join” EstimateRows=”16″ EstimateIO=”0″ EstimateCPU=”6.688e-005″ AvgRowSize=”40″ EstimatedTotalSubtreeCost=”0.0523046″ Parallel=”0″ EstimateRebinds=”0″ EstimateRewinds=”0″>
<OutputList>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”OnlineOrderFlag” />
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”AccountNumber” />
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”CreditCardID” />
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”CreditCardApprovalCode” />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread=”0″ ActualRows=”16″ ActualEndOfScans=”1″ ActualExecutions=”1″ />
</RunTimeInformation>
<NestedLoops Optimized=”0″>
<OuterReferences>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”SalesOrderID” />
</OuterReferences>
<RelOp NodeId=”1″ PhysicalOp=”Index Seek” LogicalOp=”Index Seek” EstimateRows=”16″ EstimateIO=”0.003125″ EstimateCPU=”0.0001746″ AvgRowSize=”11″ EstimatedTotalSubtreeCost=”0.0032996″ Parallel=”0″ EstimateRebinds=”0″ EstimateRewinds=”0″>
<OutputList>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”SalesOrderID” />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread=”0″ ActualRows=”16″ ActualEndOfScans=”1″ ActualExecutions=”1″ />
</RunTimeInformation>
<IndexScan Ordered=”1″ ScanDirection=”FORWARD” ForcedIndex=”0″ NoExpandHint=”0″>
<DefinedValues>
<DefinedValue>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”SalesOrderID” />
</DefinedValue>
</DefinedValues>
<Object Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Index=”[IX_SalesOrderHeader_SalesPersonID]” Alias=”[soh]” />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType=”EQ”>
<RangeColumns>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”SalesPersonID” />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString=”(288)”>
<Const ConstValue=”(288)” />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp NodeId=”3″ PhysicalOp=”Clustered Index Seek” LogicalOp=”Clustered Index Seek” EstimateRows=”1″ EstimateIO=”0.003125″ EstimateCPU=”0.0001581″ AvgRowSize=”40″ EstimatedTotalSubtreeCost=”0.0489381″ Parallel=”0″ EstimateRebinds=”15″ EstimateRewinds=”0″>
<OutputList>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”OnlineOrderFlag” />
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”AccountNumber” />
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”CreditCardID” />
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”CreditCardApprovalCode” />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread=”0″ ActualRows=”16″ ActualEndOfScans=”0″ ActualExecutions=”16″ />
</RunTimeInformation>
<IndexScan Lookup=”1″ Ordered=”1″ ScanDirection=”FORWARD” ForcedIndex=”0″ NoExpandHint=”0″>
<DefinedValues>
<DefinedValue>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”OnlineOrderFlag” />
</DefinedValue>
<DefinedValue>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”AccountNumber” />
</DefinedValue>
<DefinedValue>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”CreditCardID” />
</DefinedValue>
<DefinedValue>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”CreditCardApprovalCode” />
</DefinedValue>
</DefinedValues>
<Object Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Index=”[PK_SalesOrderHeader_SalesOrderID]” Alias=”[soh]” TableReferenceId=”-1″ />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType=”EQ”>
<RangeColumns>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”SalesOrderID” />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString=”[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] as [soh].[SalesOrderID]”>
<Identifier>
<ColumnReference Database=”[AdventureWorks]” Schema=”[Sales]” Table=”[SalesOrderHeader]” Alias=”[soh]” Column=”SalesOrderID” />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column=”@1″ ParameterCompiledValue=”(288)” ParameterRuntimeValue=”(288)” />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>”)’