/************************************
Chapter 14 Scripts
For MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

*************************************/

-- Query Window 1

/************************************/

USE AdventureWorks;
GO
BEGIN Transaction
  INSERT INTO Person.Contact
    (FirstName, LastName, PasswordHash, PasswordSalt)
      VALUES ('Old', 'McDonald', 'P@ssw0rd','P@$$S@lt')

/************************************/

-- Query Window 2

/************************************/

USE AdventureWorks;
GO
SELECT * 
FROM Person.Contact

/************************************/

SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0

/************************************/

SELECT * FROM sys.dm_tran_locks

/************************************/

-- Query Window 3

/************************************/

Kill 54

/************************************/

SELECT * FROM sys.dm_tran_locks

/************************************/

SELECT * from sys.dm_exec_requests

/************************************/

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

/************************************/

Sp_who 64

/************************************/

--Joe Query Window 1

/************************************/

USE AdventureWorks;
GO
BEGIN TRANSACTION
INSERT INTO Sales.CurrencyRate
   (CurrencyRateDate, FromCurrencyCode,ToCurrencyCode,
          AverageRate, EndOfDayRate)
VALUES (GetDate(), 'USD', 'CAD',1.585, 1.583)

/************************************/

--Sally Query Window 2

/************************************/

USE AdventureWorks;
GO
BEGIN TRANSACTION 
INSERT INTO Sales.Currency
   (CurrencyCode, Name)
VALUES ('VUC', 'Vulcan Cha')
--
UPDATE Sales.CurrencyRate
   SET EndOfDayRate = 1.584
   WHERE Year(ModifiedDate) = Year(GetDate()) AND
         Month(ModifiedDate) = Month(GetDate()) and 
             Day(ModifiedDate) = Day(GetDate())

/************************************/

UPDATE Sales.Currency
   SET Name = 'Vulcan Sha'
   WHERE CurrencyCode = 'VUC'

/************************************/

--Deadlock 1 query window

/************************************/

USE AdventureWorks;
GO
BEGIN TRANSACTION
INSERT INTO Sales.CurrencyRate
   (CurrencyRateDate, FromCurrencyCode,ToCurrencyCode,
          AverageRate, EndOfDayRate)
VALUES (GetDate(), 'USD', 'CAD',1.585, 1.583)

/************************************/

--Deadlock 2
USE AdventureWorks;
GO
BEGIN TRANSACTION 
INSERT INTO Sales.Currency
   (CurrencyCode, Name)
VALUES ('VUC', 'Vulcan Cha')
--
UPDATE Sales.CurrencyRate
   SET EndOfDayRate = 1.584
   WHERE Year(ModifiedDate) = Year(GetDate()) AND
         Month(ModifiedDate) = Month(GetDate()) and 
             Day(ModifiedDate) = Day(GetDate())

/************************************/

UPDATE Sales.Currency
   SET Name = 'Vulcan Sha'
   WHERE CurrencyCode = 'VUC'

/************************************/

USE AdventureWorks;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRANSACTION;
-- Transaction work here
COMMIT TRANSACTION;

/************************************/

SELECT Author, Title, Price, PublishDate
FROM Books WITH (INDEX(1))
WHERE PublishDate > '2001'

/************************************/

CREATE PROCEDURE Sales.uspGetSalesOrderByCountry (@Country char(2))
AS
SELECT * 
FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t 
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID 
AND CountryRegionCode = @Country

/************************************/

sp_create_plan_guide N'PlanGuideGetSalesOrderByCountry', 
--The following is the query to optimize
N'SELECT * 
FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t 
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID 
AND CountryRegionCode = @Country', 
--Object indicates the query is embedded in an object (such as a stored procedure)
N'OBJECT', 
N'Sales.uspGetSalesOrderByCountry', 
NULL, 
--The OPTION with OPTIMIZE FOR specifies the parameter to use when optimizing the query
N'OPTION (OPTIMIZE FOR(@Country = N''US''))'

/************************************/

SET SHOWPLAN_XML ON
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader h, Sales.Customer c,
    Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
    AND CountryRegionCode = N'US'
GO
SET SHOWPLAN_XML OFF

/************************************/

EXEC sp_create_plan_guide N'UsePlan_PlanGuide',
   N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader h, Sales.Customer c,
         Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country',
   N'OBJECT',
   N'Sales.GetSalesOrderByCountry',
   NULL,
   N'OPTION (USE PLAN
             N''<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5" Build="9.00.1079">
  <BatchSequence>
. . . – XML plan guide document goes here.
  </BatchSequence>

/************************************/

--Step 1. Declare variables used by cursor
DECLARE @ContactID int, @First varchar(50), @Last varchar(50), @Email varchar(50)
--Step 2. Declare the cursor and associate with a SELECT statement
DECLARE EmpCursor CURSOR FOR
SELECT pc.FirstName, pc.LastName, pc.EmailAddress
 FROM Person.Contact pc INNER JOIN
      HumanResources.Employee e ON pc.ContactID = e.ContactID
--Step 3. Open cursor
OPEN EmpCursor;
--Step 4. Use FETCH to get first row.  WHILE END loops through all rows in cursor
FETCH NEXT FROM EmpCursor INTO @ContactID, @First, @Last, @Email;
WHILE(@@FETCH_STATUS = 0)
BEGIN
  --do code to process the cursor
  -- get next row from cursor
  FETCH NEXT FROM EmpCursor INTO @ContactID, @First, @Last, @Email;
END
--Step  5. Cleanup by closing and deallocating the cursor
CLOSE EmpCursor
DEALLOCATE EmpCursor

/************************************/

SELECT ContactID, FirstName, LastName, EmailAddress
FROM Person.Contact

/************************************/

IF EXISTS (SELECT @ContactID 
   FROM HumanResources.Employee)

/************************************/

SELECT pc.FirstName, pc.LastName, pc.EmailAddress
FROM Person.Contact pc INNER JOIN
HumanResources.Employee e ON pc.ContactID = e.ContactID

/************************************/

USE AdventureWorks;
GO
--Step 1. Declare variables used by the cursor
DECLARE @Start datetime, @end datetime
SET @Start = GetDate()
DECLARE @FirstName varchar(50), @MiddleName varchar(50),
        @LastName varchar(50), @EmailAddress varChar(50)
--Step 2. Declare the cursor and associate it with the SELECT statement
DECLARE curMidName CURSOR FOR
SELECT FirstName, MiddleName, LastName, EmailAddress FROM Person.Contact
--Step 3. Open the cursor
OPEN curMidName;
--Step 4. Use FETCH NEXT to get the first row
FETCH NEXT FROM curMidName
  INTO @FirstName, @MiddleName, @LastName, @EmailAddress
--The WHILE .. END construct will loop until we’ve retrieved every row
WHILE (@@FETCH_STATUS = 0)
--@@FETCH_STATUS = 0 indicates success.  It will be -1 after the last row
BEGIN
  IF (@MiddleName is NOT Null)
   BEGIN
  	  PRINT @FirstName + ' ' + @MiddleName + ' ' + 
              @LastName + ' ' + @EmailAddress
   END
  ELSE
   BEGIN
  	  PRINT @FirstName + ' ' + @LastName + ' ' + @EmailAddress
   END
  FETCH NEXT FROM curMidName INTO
	  @FirstName, @MiddleName, @LastName, @emailAddress
END
--Step 5. Cleanup by closing and deallocating the cursor
CLOSE curMidName
DEALLOCATE curMidName
SET @end = GetDate()
PRINT 'Query took ' + CONVERT(varchar(10), datediff(ms, @start, @end)) + ' milliseconds'

/************************************/

USE AdventureWorks;
GO
DECLARE @Start datetime, @end datetime
SET @Start = GetDate()
SELECT FirstName, ISNULL(MiddleName,'') AS MiddleName, LastName, EmailAddress 
FROM Person.Contact
SET @end = GetDate()
PRINT 'Query took ' + CONVERT(varchar(10), datediff(ms, @start, @end)) + ' milliseconds'

/************************************/