Chapter 15) Views

USE AdventureWorks2012;
GO
--Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;

GO
--Check to see if a view with the same name already exists
IF(OBJECT_ID('Purchasing.vwPurchaseOrders')) IS NOT NULL
DROP VIEW Purchasing.vwPurchaseOrders
GO
--Create the view
CREATE VIEW Purchasing.vwPurchaseOrders
WITH SCHEMABINDING
AS
SELECT
poh.OrderDate,
pod.ProductID,
SUM(poh.TotalDue) TotalDue,
COUNT_BIG(*) POCount
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
GROUP BY poh.OrderDate, pod.ProductID
GO
--Add a unique clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_vwPurchaseOrders_OrderDateProductID
ON Purchasing.vwPurchaseOrders(OrderDate, ProductID)

Chapter 16) User-defined functions

USE AdventureWorks2012
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Patrick LeBlanc
-- Create date: 7/8/2012
-- Description: Scalar function that will be used to return employee age
-- =============================================
CREATE FUNCTION dbo.GetEmployeeAge
(
@BirthDate datetime
)
RETURNS int
AS
BEGIN
-- Declare the return variable here
DECLARE @Age int
-- Add the T-SQL statements to compute the return value here
SELECT @Age = DATEDIFF(DAY, @BirthDate, GETDATE())
-- Return the result of the function
RETURN @Age
END
GO

USE AdventureWorks2012;
SELECT TOP(10)
p.FirstName, p.LastName, e.BirthDate,
dbo.GetEmployeeAge(BirthDate) EmployeeAge
FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON e.BusinessEntityID = p.BusinessEntityID

USE AdventureWorks2012;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Patrick LeBlanc
-- Create date: 6/8/2012
-- Description: Returns the line items for a given orderid
-- =============================================
CREATE FUNCTION dbo.GetOrderDetails
(
@SalesOrderID int
)
RETURNS TABLE
AS
RETURN
(
SELECT
sod.SalesOrderID,
sod.SalesOrderDetailID,
sod.CarrierTrackingNumber,
p.Name ProductName,
so.Description
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p
ON sod.ProductID = p.ProductID
INNER JOIN Sales.SpecialOffer so
ON sod.SpecialOfferID = so.SpecialOfferID
WHERE
sod.SalesOrderID = @SalesOrderID
)
GO

USE AdventureWorks2012;
SELECT *
FROM dbo.GetOrderDetails(43659);

Chapter 17) Stored procedures

USE AdventureWorks2012;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Patrick LeBlanc
-- Create date: 6/9/2012
-- Description: <Description,,> Get PurchaseOrder Information
-- =============================================
CREATE PROCEDURE dbo.PurchaseOrderInformation
AS
BEGIN
SELECT
poh.PurchaseOrderID, pod.PurchaseOrderDetailID,
poh.OrderDate, poh.TotalDue, pod.ReceivedQty, p.Name ProductName
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
INNER JOIN Production.Product p
ON pod.ProductID = p.ProductID
END
GO

USE AdventureWorks2012;
EXEC dbo.PurchaseOrderInformation
WITH RESULT SETS
(
(
[Purchase Order ID] int,
[Purchase Order Detail ID] int,
[Order Date] datetime,
[Total Due] Money,
[Received Quantity] float,
[Product Name] varchar(50)
)
)

USE AdventureWorks2012;
GO
--Create Proc with OUTPUT param
CREATE PROC dbo.SampleOutput
@Parameter2 int OUTPUT
as
SELECT @Parameter2 = 10
--Execute Proc with OUTPUT param
DECLARE @HoldParameter2 INT
EXEC dbo.SampleOutput
@HoldParameter2 OUTPUT
SELECT @HoldParameter2

USE [AdventureWorks2012]
GO
/****** Object:  StoredProcedure [dbo].[PurchaseOrderInformation]    Script Date: 8/5/2016 10:14:00 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Patrick LeBlanc
-- Create date: 6/9/2012
-- Description: <Description,,> Get PurchaseOrder Information
-- =============================================
ALTER PROCEDURE [dbo].[PurchaseOrderInformation]
@EmployeeID int,
@OrderYear int = 2005
AS
BEGIN
SELECT
poh.PurchaseOrderID, pod.PurchaseOrderDetailID,
poh.OrderDate, poh.TotalDue, pod.ReceivedQty, p.Name ProductName
FROM Purchasing.PurchaseOrderHeader poh
INNER JOIN Purchasing.PurchaseOrderDetail pod
ON poh.PurchaseOrderID = pod.PurchaseOrderID
INNER JOIN Production.Product p
ON pod.ProductID = p.ProductID
WHERE
poh.EmployeeID = @EmployeeID AND
YEAR(poh.OrderDate) = @OrderYear
END

Chapter 18) Data manipulation triggers

USE AdventureWorks2012;
GO
CREATE TRIGGER HumanResources.iCheckModifedDate
ON HumanResources.Department
FOR INSERT
AS
BEGIN
DECLARE @modifieddate datetime, @DepartmentID int
SELECT @modifieddate = modifieddate, @DepartmentID = departmentid FROM inserted;
IF(DATEDIFF(Day, @modifiedDate, getdate()) > 0)
BEGIN
UPDATE HumanResources.Department
SET ModifiedDate = GETDATE()
WHERE DepartmentID = @DepartmentID
END
END

USE AdventureWorks2012;
INSERT INTO HumanResources.Department
VALUES('Executive Marketing', 'Executive General and Administration', '2/12/2011');

Chapter 19) Replication

CHAPTER 21) Managing and maintaining indexes and statistics

USE AdventureWorks2012
GO
SELECT
DB_NAME(ips.database_id) DBName,
OBJECT_NAME(ips.object_id) ObjName,
i.name InxName,
ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id('AdventureWorks2012'),
default, default, default, default) ips
INNER JOIN sys.indexes i
ON ips.index_id = i.index_id AND
ips.object_id = i.object_id
WHERE
ips.object_id > 99 AND
ips.avg_fragmentation_in_percent >= 10 AND
ips.index_id > 0