Transactins

Posted in Labels: | at 10:20 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 5 - Transactions, Locking and Blocking
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Dirty Read Transactional Fault

-- Transaction 1
USE CHA2

go
BEGIN TRANSACTION
UPDATE Customer
SET Nickname = 'Transaction Fault'
WHERE CustomerID = 1

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
USE CHA2
SELECT NickName
FROM Customer
WHERE CustomerID = 1

-- Transaction 1
COMMIT TRANSACTION


go

-----------------------------------------------------------
-- Non-Repeatable Read Transactional Fault

-- Transaction 1
USE CHA2

UPDATE Customer
SET Nickname = 'Non-Repeatable Read'
WHERE CustomerID = 1

-- Transaction 2
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION
SELECT NickName
FROM Customer
WHERE CustomerID = 1

COMMIT TRANSACTION





-----------------------------------------------------------
-- Phantom Row Transactional Fault

-- Transaction 1
USE CHA2;

UPDATE Customer
SET NickName = 'Missy'
WHERE CustomerID = 3;


-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED;

USE CHA2;

BEGIN TRANSACTION;

SELECT CustomerID, FirstName, LastName, Nickname
FROM Customer
WHERE NickName = 'Missy';

COMMIT TRANSACTION;




-----------------------------------------------------------
-- Read Committed - Default Behavior

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION

SELECT NickName
FROM Customer
WHERE CustomerID = 1

UPDATE Customer
SET Nickname = 'Pepper'
WHERE CustomerID = 1

COMMIT TRANSACTION;

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL
READ COMMITTED

USE CHA2
BEGIN TRANSACTION

SELECT NickName
FROM Customer
WHERE CustomerID = 1

UPDATE Customer
SET Nickname = 'Pepper'
WHERE CustomerID = 1

COMMIT TRANSACTION;








-------------------------------------------------
-- Controlling SQL Server Locking

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DBCC USEROPTIONS


--Locking Hints

USE OBXKites
UPDATE Product WITH (RowLock)
SET ProductName = ProductName + ' Updated'

EXEC sp_help ProductCategory

EXEC sp_indexoption 'ProductCategory.PK__ProductCategory__79A81403', 'AllowRowlocks', FALSE
EXEC sp_indexoption 'ProductCategory.PK__ProductCategory__79A81403', 'AllowPagelocks', FALSE





--------------------------------------------------
-- DeadLocks!

--Transaction 1
-- Step 1
USE OBXKites
BEGIN TRAN;
UPDATE Contact
SET LastName = 'Jorgenson2'
WHERE ContactCode = '101'

--Transaction 2
--Step 2
USE OBXKites
BEGIN TRAN
UPDATE Product
SET ProductName
= 'DeadLock Repair Kit'
WHERE Code = '1001'
UPDATE Contact
SET FirstName = 'Neals'
WHERE ContactCode = '101'
COMMIT

-- Step 3
UPDATE Product
SET ProductName = 'DeadLock Identification Tester'
WHERE Code = '1001'

COMMIT


---------------------------------------------
-- Deadlock with error handling

Use OBXKites
TranStart:

Begin Tran

Update Contact
Set LastName = '2Jorgenson2'
Where ContactCode = '101'

WaitFor Delay '00:00:05'

Update Product
Set ProductName = '2DeadLock Identification Tester'
Where ProductCode = '1001'
IF @@ERROR = 1205
BEGIN
PRINT 'Deadlock'
GOTO TranStart
END

COMMIT



----------------------------------------------------------------------------
-- Using Snapshot Isolation

USE Aesop;
ALTER DATABASE Aesop
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE Aesop
SET READ_COMMITTED_SNAPSHOT ON

BEGIN TRAN
UPDATE Fable
SET Title = 'Rocking with Snapshots test'
WHERE FableID = 2;

SELECT Title FROM FABLE WHERE FableID = 2

COMMIT TRAN
---Transaction 2
USE Aesop;

SELECT Title
FROM FABLE
WHERE FableID = 2

Read Users' Comments (0)

Indexing

Posted in Labels: | at 10:20 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 4 - Indexing for Performance
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Examining Query Execution Plans

USE AdventureWorks

-- 1 Clustered Index Seek
SELECT *
FROM Production.TransactionHistory
WHERE TransactionID = 100777

-- 2 Bookmark Lookup
SELECT *
FROM Production.TransactionHistory
WHERE ReferenceOrderID = 55555

-- 3 Covering Index
SELECT TransactionID, ProductID, TransactionDate
FROM Production.TransactionHistory
WHERE ProductID = 319
AND Quantity >10 -- non SARG, uses Filter

Read Users' Comments (0)

UDF

Posted in Labels: | at 10:20 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 3 - User Defined Functions
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Creating Scalar Functions

USE Tempdb
go
CREATE FUNCTION dbo.Multiply (@A INT, @B INT)
RETURNS INT
AS
BEGIN
RETURN @A * @B
End
go
SELECT dbo.Multiply (3,4)


USE ObxKites
SELECT dbo.fGetPrice('1006',GetDate(),DEFAULT)
SELECT dbo.fGetPrice('1001','5/1/2001',NULL)

---------------------------------------------------------
-- Working with Inline Table-Valued Functions


USE CHA2
go
CREATE FUNCTION fEventList ()
RETURNS Table
AS
RETURN(
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID)

SELECT LastName, Code, DateBegin
FROM dbo.fEventList()


-- Parameters
USE OBXKites
go

-- View with Where clause
CREATE VIEW vPricelist
AS
SELECT Code, Price.Price
FROM dbo.Price
JOIN dbo.Product P
ON Price.ProductID = P.ProductID
WHERE EffectiveDate =
(SELECT MAX(EffectiveDate)
FROM dbo.Price
WHERE ProductID = P.ProductID
AND EffectiveDate <= GetDate())

SELECT *
FROM vPriceList
WHERE Code = '1001'

-- UDF with Parameters
CREATE FUNCTION dbo.fPriceList (
@Code CHAR(10) = Null, @PriceDate DateTime)
RETURNS Table
AS
RETURN(
SELECT Code, Price.Price
FROM dbo.Price
JOIN dbo.Product P
ON Price.ProductID = P.ProductID
WHERE EffectiveDate =
(SELECT MAX(EffectiveDate)
FROM dbo.Price
WHERE ProductID = P.ProductID
AND EffectiveDate <= @PriceDate)
AND (Code = @Code
OR @Code IS NULL) )

SELECT * FROM dbo.fPriceList(DEFAULT, '2/20/2002')

SELECT * FROM dbo.fPriceList('1001', '2/20/2002')

------------------------------------------------------
-- Using Multi-Statement Table-Valued UDFs

CREATE FUNCTION fPriceAvg()
RETURNS @Price TABLE
(Code CHAR(10),
EffectiveDate DATETIME,
Price MONEY)
AS
BEGIN
INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, EffectiveDate, Price
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID

INSERT @Price (Code, EffectiveDate, Price)
SELECT Code, Null, Avg(Price)
FROM Product
JOIN Price
ON Price.ProductID = Product.ProductID
GROUP BY Code
RETURN
END


SELECT *
FROM dbo.fPriceAvg()


---------------------------------------------------
-- Navigating a Hierarchy

Use Family

Select * from Person order by PersonID;

go
CREATE
-- alter
FUNCTION dbo.FamilyTree
(@PersonID CHAR(25))
RETURNS @Tree TABLE (PersonID INT, LastName VARCHAR(25), FirstName VARCHAR(25), Lv INT)
AS
BEGIN
DECLARE @LC INT
SET @LC = 1
-- insert the top level
INSERT @Tree
SELECT PersonID, LastName, FirstName, @LC
FROM dbo.Person with (NoLock)
WHERE PersonID = @PersonID

-- Loop through sub-levels
WHILE @@RowCount > 0
BEGIN
SET @LC = @LC + 1
-- insert the Class level
INSERT @Tree
SELECT Tree.PersonID, Tree.LastName, Tree.FirstName, @LC
FROM dbo.Person FamilyNode with (NoLock)
JOIN dbo.Person Tree with (NoLock)
ON FamilyNode.PersonID = Tree.MotherID
OR FamilyNode.PersonID = Tree.FatherID
JOIN @Tree CC
ON CC.PersonID = FamilyNode.PersonID
WHERE CC.Lv = @LC - 1
END
RETURN
END ;

-- end of function

Select * From dbo.FamilyTree(1);


-- Query With Recursive CTE (Common Table Expression)

WITH FamilyTree( LastName, FirstName, PersonID, lv)
AS (
-- Anchor
SELECT LastName, FirstName, PersonID, 1
FROM Person A
WHERE PersonID = 10

-- Recursive Call
UNION ALL
SELECT Node.LastName, Node.FirstName, Node.PersonID, lv + 1
FROM Person Node
JOIN FamilyTree ft
ON Node.MotherID = ft.PersonID
OR Node.FatherID = ft.PersonID
)
SELECT PersonID, LastName, FirstName, lv
FROM FamilyTree;

Select * From dbo.FamilyTree(1);


-----------------------------------------------------------------------
-- Having Fun with Apply

USE CHA2
go
CREATE FUNCTION fEventList2 (@CustomerID INT)
RETURNS Table
AS
RETURN(
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID
WHERE Customer.CustomerID = @CustomerID
)

SELECT C.LastName, Code, DateBegin, Tour
FROM Customer C
CROSS APPLY fEventList2(C.CustomerID)
ORDER BY C.LastName

Read Users' Comments (0)

T-Sql and Stored Procedures

Posted in Labels: | at 10:18 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - Developing Solutions
-- Lesson 1 - T-SQL and Stored Procedures
--
-- Paul Nielsen
-----------------------------------------------------------


-----------------------------------------------------------
-- Introducing T-SQL

-- the is an ANSI style comment
USE Family;

Select FirstName, LastName -- selects the columns
FROM Person -- the source table
Where LastName Like 'Hal%'; -- the row restriction

-- C style comments
/*
Order table Insert Trigger
Paul Nielsen
ver 1.0 July 21, 2006
Logic: etc.
ver 1.1: July 31, 2006, added xyz
*/

-- Debug Commands
Select 3;
Print 6;

Print 'Begining';
waitfor delay '00:00:02';
Print 'Done';

---------------------------------
-- Working with Variables

-- Variable Default and Scope
DECLARE @Test INT,
@TestTwo NVARCHAR(25);
SELECT @Test, @TestTwo ;

SET @Test = 1;
SET @TestTwo = 'a value';
SELECT @Test, @TestTwo;
go --terminates the variables

SELECT @Test as BatchTwo, @TestTwo;

-- Using the Set and Select Commands
USE Family

-- multiple rows & multiple columns
Declare @TempID INT,
@TempLastName VARCHAR(25);
SET @TempID = 99
SELECT @TempID = PersonID,
@TempLastName = LastName
FROM Person
ORDER BY PersonID
SELECT @TempID, @TempLastName;
-- No rows returned
Declare @TempID INT,
@TempLastName VARCHAR(25);
SET @TempID = 99;
SELECT @TempID = PersonID,
@TempLastName = LastName
FROM Person
WHERE PersonID = 100
ORDER BY PersonID;
SELECT @TempID, @TempLastName;

-- Using Variables Within SQL Queries
USE OBXKites;

DECLARE @ProductCode CHAR(10);
SET @ProductCode = '1001';

SELECT ProductName
FROM Product
WHERE Code = @ProductCode;



-- Multiple Assignment Variables
USE CHA2
DECLARE
@EventDates VARCHAR(1024)
SET @EventDates = ''

SELECT @EventDates = @EventDates
+ CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (select DateBegin as [d]
from Event
join Tour
on Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a;

SELECT Left(@EventDates, Len(@EventDates)-1)
AS 'Outer Banks Lighthouses Events';

--------------------------------
-- Controlling Procedural Flow

-- If
IF 1 = 0
PRINT 'Line One';
PRINT 'Line Two';

-- If w/ begin/end block
IF 1 = 0
BEGIN
PRINT 'Line One';
PRINT 'Line Two';
END

-- IF Exists()
USE OBXKITES;
IF EXISTS(SELECT * FROM [ORDER] WHERE Closed = 0)
BEGIN;
PRINT 'Process Orders';
END;

-- While
DECLARE @Temp int;
SET @Temp = 0;

WHILE @Temp <3
BEGIN;
PRINT 'tested condition' + Str(@Temp);
SET @Temp = @Temp + 1;
END;

-- goto
GOTO ErrorHandler;
PRINT 'more code';
ErrorHandler:;
PRINT 'Logging the error';

-------------------------------
-- Examining SQL Server with Code

-- sp_help
USE OBXKites;
sp_help price;

-- Global Variables
Select @@Connections;
Select @@CPU_Busy;
Select @@Cursor_Rows;
Select @@DateFirst;
Select @@DBTS;
Select @@Error;
Select @@Fetch_Status;
Select @@Identity;
Select @@Idle;
Select @@IO_Busy;
Select @@LangID;
Select @@Language;
Select @@Lock_TimeOut;
Select @@Max_Connections;
Select @@Max_Precision;
Select @@Nestlevel;
Select @@Options;
Select @@Pack_Received;
Select @@Pack_Sent;
Select @@Packet_Errors;
Select @@ProcID;
Select @@RemServer;
Select @@RowCount;
Select @@ServerName;
Select @@ServiceName;
Select @@SPID;
Select @@TextSize;
Select @@TimeTicks;
Select @@Total_Errors;
Select @@Total_Read;
Select @@Total_Write;
Select @@TranCount;
Select @@Version;

-----------------------
-- Using Temp Tables

CREATE TABLE #ProductTemp (
ProductID INT PRIMARY KEY
);

INSERT INTO #ProductTemp VALUES ( 1);

SELECT *
FROM #ProductTemp

SELECT Name
FROM TempDB.dbo.SysObjects
WHERE Name Like '#Pro%'

-- Global Temp Table
IF NOT EXISTS(
SELECT * FROM Tempdb.dbo.Sysobjects
WHERE Name = '##TempWork')
CREATE TABLE ##TempWork(
PK INT,
Col1 INT
);

-- Table Variable
DECLARE @WorkTable TABLE (
PK INT PRIMARY KEY,
Col1 INT NOT NULL);

INSERT INTO @WorkTable (PK, Col1)
VALUES ( 1, 101);

SELECT PK, Col1
FROM @WorkTable;


--------------------------
-- Developing Dynamic SQL

-- Simple EXEC
USE Family;
EXEC ('Select LastName from Person Where PersonID = 12');

-- sp_executeSQL
EXEC sp_executeSQL
N'Select LastName from Person Where PersonID = @PersonSelect',
N'@PersonSelect INT',
@PersonSelect = 12;


-- Developing Dynamic SQL Code
USE OBXKites;

DECLARE
@SQL NVARCHAR(1024),
@SQLWhere NVARCHAR(1024),
@NeedsAnd BIT,

-- User Parameters
@ProductName VARCHAR(50),
@ProductCode VARCHAR(10),
@ProductCategory VARCHAR(50);

-- Initilize Variables
SET @NeedsAnd = 0;
SET @SQLWhere = '';

-- Simulate User's Requirements
SET @ProductName = NULL;
SET @ProductCode = 1001;
SET @ProductCategory = NULL;

-- Assembling Dynamic SQL

-- Set up initial SQL Select
IF @ProductCategory IS NULL
SET @SQL = 'Select ProductName from Product';
ELSE
SET @SQL = 'Select ProductName from Product
Join ProductCategory
on Product.ProductCategoryID
= ProductCategory.ProductCategoryID';

-- Build the Dynamic Where Clause
IF @ProductName IS NOT NULL
BEGIN;
SET @SQLWhere = 'ProductName = ' + @ProductName;
SET @NeedsAnd = 1;
END;

IF @ProductCode IS NOT NULL
BEGIN;
IF @NeedsAnd = 1
SET @SQLWhere = @SQLWhere + ' and ';
SET @SQLWhere = 'Code = ' + @ProductCode;
SET @NeedsAnd = 1;
END;

IF @ProductCategory IS NOT NULL
BEGIN;
IF @NeedsAnd = 1
SET @SQLWhere = @SQLWhere + ' and ';
SET @SQLWhere = 'ProductCategory = ' + @ProductCategory ;
SET @NeedsAnd = 1;
END;

-- Assemble the select and the where portions of the dynamic SQL
IF @NeedsAnd = 1
SET @SQL = @SQL + ' where ' + @SQLWhere;

Print @SQL;

EXEC sp_executeSQL @SQL
WITH RECOMPILE;


---------------------------------------------
-- Introducing Stored Procedures

-- Create, Alter, Drop
USE OBXKites;
go

CREATE PROCEDURE CategoryList
AS
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory;
RETURN;
go

-- Executing the Sproc & Returning a Result Set
EXEC CategoryList;

-- Calling Remote Stored Procedures
EXEC [HPPRESENT\SECOND].OBXKites.dbo.pProductCategory_AddNew 'Food', 'Eat-ables'

-- Compiling Stored Procedures
CREATE PROCEDURE CategoryList
WITH RECOMPILE
AS
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory;
RETURN;
go

EXEC sp_recompile CategoryList;

-------------------
-- Passing Parameters

-- Input Parameters
USE OBXKites;

go
CREATE PROCEDURE CategoryGet
(@CategoryName NVARCHAR(35))
AS
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory
WHERE ProductCategoryName = @CategoryName;
go

EXEC CategoryGet @CategoryName='Kite';


-- Parameter Defaults
CREATE PROCEDURE pProductCategory_Fetch2(
@Search NVARCHAR(50) = NULL
)
-- If @Search = null then return all ProductCategories
-- If @Search is value then try to find by Name
AS
SET NOCOUNT ON;
SELECT ProductCategoryName, ProductCategoryDescription
FROM dbo.ProductCategory
WHERE ProductCategoryName = @Search
OR @Search IS NULL;
IF @@RowCount = 0
RAISERROR('Product Category ''%s'' Not Found.',14,1,@Search);
go

EXEC pProductCategory_Fetch2 'OBX';

EXEC pProductCategory_Fetch2;

--------------------------------------------
-- Returning Parameters

-- Output Parameters
USE OBXKites;
go
CREATE PROC GetProductName (
@ProductCode CHAR(10),
@ProductName VARCHAR(25) OUTPUT )
AS
SELECT @ProductName = ProductName
FROM dbo.Product
WHERE Code = @ProductCode;
go

--
USE OBXKITES;
DECLARE @ProdName VARCHAR(25);
EXEC GetProductName '1001', @ProdName OUTPUT;
PRINT @ProdName;

-- Using the Return Command
CREATE PROC IsItOK (
@OK VARCHAR(10) )
AS
IF @OK = 'OK'
RETURN 0
ELSE
RETURN -100;
go


DECLARE @ReturnCode INT;
EXEC @ReturnCode = IsITOK 'OK';
PRINT @ReturnCode;
EXEC @ReturnCode = IsItOK 'NotOK';
PRINT @ReturnCode;



----------------------
-- Iterating through rows


USE Family
DECLARE @Name VARCHAR(100)

-- Fast Forward Cursor / Function
-- 1
DECLARE cDetail CURSOR FAST_FORWARD READ_ONLY
FOR SELECT FirstName + ' ' + LastName
FROM Person
ORDER BY DateofBirth
-- 2
OPEN cDetail
-- 3
FETCH cDetail INTO @Name -- prime the cursor
PRINT @Name

WHILE @@Fetch_Status = 0
BEGIN
PRINT @Name
-- 3
FETCH cDetail INTO @Name -- fetch next
END

-- 4
CLOSE cDetail

-- 5
DEALLOCATE cDetail



----------------------
-- Handling Errors

-- Try...Catch
BEGIN TRY;
SET NOCOUNT ON;
SELECT 'Try One';
RAISERROR('Simulated Error', 16, 1);
Select 'Try Two';
END TRY
BEGIN CATCH;
SELECT 'Catch Block';
END CATCH ;
SELECT 'Try Three';


BEGIN TRY;
SET NOCOUNT ON;
SELECT 'Try One';
RAISERROR('Simulated Error', 16, 1) ;
Select 'Try Two';
END TRY

BEGIN CATCH
SELECT
ERROR_MESSAGE() AS [Message],
ERROR_PROCEDURE() AS [Procedure],
ERROR_LINE() AS Line,
ERROR_NUMBER() AS Number,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State;
END CATCH;
SELECT 'Try Three';

-- Without a Catch Block the Functions Return a Null
SELECT
ERROR_MESSAGE() AS [Message],
ERROR_PROCEDURE() AS [Procedure],
ERROR_LINE() AS Line,
ERROR_NUMBER() AS Number,
ERROR_SEVERITY() AS Severity,
ERROR_STATE() AS State;
-- Legacy @@Error Global Variable
USE Family;
UPDATE Person
SET PersonID = 1
Where PersonID = 2;
Print @@Error;
Print @@Error;

-- Saving @@Error to a Local Variable
USE Family;
DECLARE @err INT;

UPDATE Person
SET PersonID = 1
Where PersonID = 2;
SET @err = @@Error;

IF @err <> 0
Begin;
-- error handling code
Print @err;
End;

-- Legacy @@RowCount Global Variable
USE FAMILY;
UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 100;

IF @@RowCount = 0
Begin;
-- error handling code
Print 'no rows affected';
End;

-- Raiserror

-- The Simple Raiserror Form
RAISERROR 5551212 'Unable to update Customer';

-- The Complete Raiserror Form
RAISERROR('Unable to update Customer', 14, 1);

-- Error Severity
RAISERROR('Print', 10,1);
RAISERROR('Info', 14,1);
RAISERROR('Warning', 15,1);
RAISERROR('Critical', 16,1);

-- Adding Variable Parameters to Message
RAISERROR ('Unable to update %s.', 14, 1, 'Customer');

-- Stored Messages
EXEC sp_addmessage 50001, 16, 'Unable to update %s';

EXEC sp_addmessage 50001, 16, 'Still unable to update %s', @Replace = 'Replace';


SELECT *
FROM sys.messages
WHERE message_id > 50000;

SELECT 'EXEC sp_addmessage '
+ Cast(message_id as VARCHAR(7))
+ ', ' + Cast(Severity as VARCHAR(2))
+ ', ''' + [text] + ''';'
FROM sys.messages
WHERE message_id > 50000;

-- code test
EXEC sp_addmessage 50001, 16, 'Still unable to update %s';

EXEC sp_dropmessage 50001;

-- With Log
RAISERROR ('Unable to update %s.', 14, 1, 'Customer')
WITH LOG;

-- Fatal Errors
SELECT Error, Severity, Description
FROM Master.dbo.SysMessages
WHERE Severity > 16;


---------------------------------
-- Controlling Transactions

/*
BEGIN TRANSACTION;
code;
COMMIT TRANSACTION; or ROLLBACK TRANSACTION;
*/

-- sample code only
BEGIN TRAN;
BEGIN TRY;
-- debit account 1
INSERT Account (AccountID, Amount, TransactionDate)
VALUES(123, 100, '12/11/2006');
-- credit account 1
INSERT Account (AccountID, Amount, TransactionDate)
VALUES(456, -100, '12/11/2006');
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
RAISERROR('Transaction incomplete - not committed to disk', 14, 1);
END CATCH;




Read Users' Comments (0)

Triggers

Posted in Labels: | at 10:17 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 3 - T-SQL Programming
-- Lesson 2 - Triggers and Audit Trail
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Introducing After Triggers

USE FAMILY
go
CREATE TRIGGER TriggerOne ON Person
AFTER Insert -- old syntax FOR
AS
PRINT 'In the After Trigger'
go

INSERT Person(PersonID, LastName, FirstName, Gender)
VALUES (50, 'Flanigan', 'Brandie','F')

-----------------------------------------------------------------------
-- Using "Instead of" Triggers

USE FAMILY

CREATE TRIGGER TriggerTwo ON Person
INSTEAD OF Insert
AS
PRINT 'In the Instead-of Trigger'
go

INSERT Person(PersonID, LastName, FirstName, Gender)
VALUES (99, 'McDoogle', 'John','M')

SELECT *
FROM Person
WHERE PersonID = 99

DROP TRIGGER TriggerTwo

/*
Transaction Flow
1 - Identity Insert Check
2 - Nullability Constraint
3 - Data Type Check
4 - Instead of Trigger
5 - Primary Key Constraint
6 - Check Constraints
7 - Foreign Key Constraint
8 - DML execution and update to Transaction Log
9 - After Trigger
10 - Commit Transaction
*/

--------------------------------------------------------
-- Disabling Triggers

ALTER TABLE Person
DISABLE TRIGGER TriggerOne

SELECT OBJECTPROPERTY(OBJECT_ID('TriggerOne'),'ExecIsTriggerDisabled')


ALTER TABLE Person
ENABLE TRIGGER TriggerOne

SELECT OBJECTPROPERTY(OBJECT_ID('TriggerOne'),'ExecIsTriggerDisabled')


-- Viewing Trigger Status
SELECT SubString(S2.Name,1,30) as [Table],
SubString(S.Name, 1,30) as [Trigger],
CASE (SELECT -- Correlated subquery
OBJECTPROPERTY(OBJECT_ID(S.Name),
'ExecIsTriggerDisabled'))
WHEN 0 THEN 'Enabled'
WHEN 1 THEN 'Disabled'
END AS Status
FROM Sysobjects S
JOIN Sysobjects S2
ON S.parent_obj = S2.ID
WHERE S.Type = 'TR'
ORDER BY [Table], [Trigger]


-------------------------------------------------------
-- Using the Update() function

ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
IF Update(LastName)
PRINT 'You modified the LastName column'
ELSE
PRINT 'The LastName column is untouched.'


UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 25

UPDATE Person
SET FirstName = 'Joe'
WHERE PersonID = 25


-----------------------------------------------
-- Selecting Transaction Data

USE Family

ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
SET NoCount ON
IF Update(LastName)
SELECT 'You modified the LastName column to ' + Inserted.LastName
FROM Inserted


UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 32

ALTER TRIGGER TriggerOne ON Person
AFTER Insert, Update
AS
SELECT CAST(D.PersonID as VARCHAR(4)) + ': ' + D.LastName + ' changed to ' + I.LastName
FROM Inserted I
JOIN Deleted D
ON I.PersonID = D.PersonID

UPDATE Person
SET LastName = 'Carter'
WHERE LastName = 'Johnson'



-------------------------------------------------
-- Nesting and Recursion

-- Nested - Server wide setting
EXEC sp_configure 'Nested Triggers', 1
Reconfigure



-- Recursive Trigger - Database wide setting
USE OBXKites

ALTER DATABASE OBXKites SET RECURSIVE_TRIGGERS ON

ALTER TABLE Product
ADD Created DATETIME NOT NULL DEFAULT GetDate()
ALTER TABLE Product
ADD Modified DATETIME NOT NULL DEFAULT GetDate()



-- assumes created and modified date columns have been added to the Product table.
CREATE TRIGGER Products_ModifiedDate ON dbo.Product
AFTER UPDATE
AS
SET NoCount ON
-- Check nest level
PRINT Trigger_NestLevel() -- for demo purposes, remove from production code
If Trigger_NestLevel() > 1
RETURN
-- block manual update of created/modified columns
IF (UPDATE(Created) or UPDATE(Modified))
AND Trigger_NestLevel() = 1
BEGIN
RAISERROR('Update failed.', 16, 1)
ROLLBACK
RETURN
END
-- Update the Modified date
UPDATE Product
SET modified = getdate()
FROM Product
JOIN Inserted
ON Product.ProductID = Inserted.ProductID
-- end of trigger
go

UPDATE PRODUCT
SET ProductName = 'Modifed Trigger'
WHERE Code = '1002'

SELECT Code, ProductName, Created, Modified
FROM Product
WHERE Code = '1002'


--------------------------------------------------------
-- Enforcing Complex RI

-- Complex Business Rules Validation
-- Custom RI

-- ensure the guide's qual date is good and the revoke date is null
-- for the given guideID and TourID

USE CHA2
go
CREATE TRIGGER LeadQualied ON Event_mm_Guide
AFTER INSERT, UPDATE
AS
SET NoCount ON
IF EXISTS(
SELECT *
FROM Inserted
JOIN dbo.Event
ON Inserted.EventID = Event.EventID
LEFT JOIN dbo.Tour_mm_Guide
ON Tour_mm_Guide.TourID = Event.TourID
AND Inserted.GuideID = Tour_mm_Guide.GuideID
WHERE
Inserted.IsLead = 1
AND
(QualDate > Event.DateBegin
OR
RevokeDate IS NOT NULL
OR
QualDate IS NULL )
)
BEGIN
RAISERROR('Lead Guide is not Qualified.',16,1)
ROLLBACK TRANSACTION
END
go


-- test

INSERT Event_mm_Guide (EventID, GuideID, IsLead)
VALUES (10, 1, 1)

INSERT Event_mm_Guide (EventID, GuideID, IsLead)
VALUES (10, 2, 1)


-------------------------------------------------------
-- Auditing Data Changes

USE OBXKites

CREATE TABLE dbo.Audit (
AuditID UNIQUEIDENTIFIER RowGUIDCol NOT NULL
CONSTRAINT DF_Audit_AuditID DEFAULT (NEWID())
CONSTRAINT PK_Audit PRIMARY KEY NONCLUSTERED (AuditID),
AuditDate DATETIME NOT NULL,
SysUser VARCHAR(50) NOT NULL,
Application VARCHAR(50) NOT NULL,
TableName VARCHAR(50)NOT NULL,
Operation CHAR(1) NOT NULL,
PrimaryKey VARCHAR(50) NOT NULL,
RowDescription VARCHAR(50) NULL,
SecondaryRow VARCHAR(50) NULL,
[Column] VARCHAR(50) NOT NULL,
OldValue VARCHAR(50) NULL,
NewValue VARCHAR(50) NULL
)

--------------------------------------------------------------
-- Fixed Audit Trail Trigger

Use OBXKites

Go

CREATE TRIGGER Product_Audit
ON dbo.Product
AFTER Insert, Update
NOT FOR REPLICATION
AS

DECLARE @Operation CHAR(1)

IF EXISTS(SELECT * FROM Deleted)
SET @Operation = 'U'
ELSE
SET @Operation = 'I'

IF UPDATE(ProductCategoryID)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ProductCategoryID',
OPC.ProductCategoryName, NPC.ProductCategoryName
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductCategoryID
<> Deleted.ProductCategoryID
-- fetch ProductCategory Names
LEFT OUTER JOIN dbo.ProductCategory OPC
ON Deleted.ProductCategoryID = OPC.ProductCategoryID
JOIN dbo.ProductCategory NPC
ON Inserted.ProductCategoryID = NPC.ProductCategoryID
IF UPDATE(Code)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'Code',
Deleted.Code, Inserted.Code
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.Code <> Deleted.Code

IF UPDATE(ProductName)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'Name',
Deleted.ProductName, Inserted.ProductName
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductName <> Deleted.ProductName

IF UPDATE(ProductDescription)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ProductDescription',
Deleted.ProductDescription, Inserted.ProductDescription
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ProductDescription <> Deleted.ProductDescription

IF UPDATE(ActiveDate)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'ActiveDate',
Deleted.ActiveDate, Inserted.ActiveDate
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.ActiveDate != Deleted.ActiveDate

IF UPDATE(DiscontinueDate)
INSERT dbo.Audit
(AuditDate, SysUser, Application, TableName, Operation,
PrimaryKey, RowDescription, SecondaryRow, [Column],
OldValue, NewValue)
SELECT GetDate(), suser_sname(), APP_NAME(), 'Product', @Operation,
Inserted.ProductID, Inserted.Code, NULL, 'DiscontinueDate',
Deleted.DiscontinueDate, Inserted.DiscontinueDate
FROM Inserted
LEFT OUTER JOIN Deleted
ON Inserted.ProductID = Deleted.ProductID
AND Inserted.DiscontinueDate != Deleted.DiscontinueDate

go

-- Test the Fixed Audit Trail

EXEC pProduct_AddNew 'Kite', 200, 'The MonstaKite', 'Man what a big Kite!'

SELECT TableName, RowDescription, [Column], NewValue
FROM Audit

SELECT *
FROM Audit

UPDATE dbo.Product
SET ProductDescription = 'Biggie Sized'
WHERE Code = 200

SELECT AuditDate, OldValue, NewValue
FROM dbo.Audit
WHERE TableName = 'Product'
AND RowDescription = '200'
AND [Column] = 'ProductDescription'

--Rolling back an Audit Trail change

CREATE PROCEDURE pAudit_RollBack (
@AuditID UNIQUEIDENTIFIER)
AS
SET NoCount ON

DECLARE
@SQLString NVARCHAR(4000),
@TableName NVARCHAR(50),
@PrimaryKey NVARCHAR(50),
@Column NVARCHAR(50),
@NewValue NVARCHAR(50)

SELECT
@TableName = TableName,
@PrimaryKey = PrimaryKey,
@Column = [Column],
@NewValue = OldValue
FROM dbo.Audit
WHERE AuditID = @AuditID

SET @SQLString =
'UPDATE ' + @TableName
+ ' SET ' + @Column + ' = ''' + @NewValue +''''
+ ' WHERE ' + @TableName + 'ID = ''' + @PrimaryKey + ''''

EXEC sp_executeSQL @SQLString
go

--Test RollBack

DECLARE @AuditRollBack UNIQUEIDENTIFIER

SELECT @AuditRollBack = AuditID
FROM dbo.Audit
WHERE TableName = 'Product'
AND RowDescription = '200'
AND OldValue = 'Man what a big Kite!'

EXEC pAudit_RollBack @AuditRollBack

SELECT ProductDescription
FROM dbo.Product
WHERE Code = 200

Read Users' Comments (0)

Modifing Data

Posted in Labels: | at 10:16 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 8 - Modifying Data
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Inserting Data

USE CHA2;

-- INSERT/VALUES
INSERT INTO dbo.Guide (LastName, FirstName, Qualifications)
VALUES ('Smith', 'Dan', 'Diver, Whitewater Rafting');

INSERT INTO dbo.Guide (FirstName, LastName, Qualifications)
VALUES ('Jeff', 'Davis', 'Marine Biologist, Diver');

INSERT INTO dbo.Guide (FirstName, LastName)
VALUES ('Tammie', 'Commer');

SELECT * from dbo.Guide;

-- INSERT/VALUES without a insert column listing
INSERT dbo.Guide
VALUES ('Jones', 'Lauren', 'First Aid, Rescue/Extraction','6/25/59','4/15/01');

SELECT GuideID, LastName, FirstName, Qualifications
FROM dbo.Guide;


-- INSERT/VALUES w/ expression
INSERT dbo.Guide (FirstName, LastName, Qualifications)
VALUES ('Greg', 'Wilson' , 'Rock Climbing' + ', ' + 'First Aid');

SELECT * from dbo.Guide;


-- INSERT/SELECT

USE OBXKites;
-- Using a fresh copy of OBXKites without population

--(Corrected from the text - added ContactCode)
INSERT dbo.Contact (FirstName, ContactCode, LastName, CompanyName)
SELECT FirstName, LastName, GuideID, 'Cape Hatteras Adv.'
FROM CHA2.dbo.Guide;

SELECT ContactID, FirstName AS FIRST, LastName AS LAST , CompanyName
FROM dbo.Contact;


-- INSERT/Exec

Use CHA2
Go
-- create the sample stored procedure
CREATE PROC ListGuides
AS
SET NOCOUNT ON

-- result set 1
SELECT FirstName, LastName
FROM dbo.Guide;

-- result set 2
SELECT FirstName, LastName
FROM OBXKites.dbo.Contact
RETURN; -- end of sproc
go

-- test the sample stored procedure
Exec ListGuides;

go
-- create a table for the insert
CREATE TABLE dbo.GuideSample
(FirstName VARCHAR(20),
LastName VARCHAR(20) );

-- the insert / exec statement
INSERT GuideSample ( FirstName, LastName)
EXEC ListGuides;

-- check to see that the insert worked
SELECT * FROM GuideSample;

-- SELECT INTO
USE CHA2;

-- sample code for setting the bulk-logged behavior
Alter DATABASE CHA2 SET RECOVERY FULL
EXEC SP_DBOPTION 'CHA2', 'select into/bulkcopy', 'TRUE';
go

-- the select/into statement
SELECT * INTO dbo.GuideList
FROM dbo.Guide
ORDER BY Lastname, FirstName;

Go
-- viewing the data structure of the new table
sp_help GuideList;

--testing the identity column of the new table
INSERT dbo.Guidelist (LastName, FirstName, Qualifications)
VALUES('Nielsen', 'Paul','trainer');

SELECT GuideID, LastName, FirstName
FROM dbo.GuideList;

-- INSERTing Identity Column Primary Keys
USE CHA2;

INSERT dbo.Guide (GuideID, FirstName, LastName)
VALUES (10, 'Bill', 'Wilson');

SET IDENTITY_INSERT Guide On;

INSERT dbo.Guide (GuideID, FirstName, LastName)
VALUES (10, 'Bill', 'Wilson');

INSERT dbo.Guide (GuideID, FirstName, LastName)
VALUES (7, 'Sue', 'Atlas');

SET IDENTITY_INSERT Guide Off;

INSERT dbo.Guide ( FirstName, LastName)
VALUES ( 'Arnold', 'Bistier');

SELECT GuideID, FirstName, LastName from dbo.Guide;

-----------------------------------------------------------
-- Updating Data

-- Updating a single column of a single row
USE CHA2
UPDATE dbo.Guide
SET Qualifications = 'Spelunking, Cave Diving, Rock Climbing, First Aid, Navigation'
Where GuideID = 6;

SELECT GuideID, LastName, Qualifications
FROM dbo.Guide
WHERE GuideID = 6;

-- Global Search and Replace

USE Family;

UPDATE Person
SET LastName = REPLACE(Lastname, 'll', 'qua');

SELECT lastname
FROM Person;


-- A complex update with expression

USE Tempdb;

CREATE TABLE dbo.Dept (
DeptID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
DeptName VARCHAR(50) NOT NULL,
RaiseFactor NUMERIC(4,2)
)
ON [Primary];
go

Create TABLE dbo.Employee (
EmployeeID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
DeptID INT FOREIGN KEY REFERENCES Dept,
LastName VARCHAR(50) NOT NULL,
FirstName VARCHAR(50) NOT NULL,
Salary INT,
PerformanceRating NUMERIC(4,2),
DateHire DATETIME,
DatePosition DATETIME
)
ON [Primary];
go
-- build the sample data
INSERT dbo.Dept VALUES ('Engineering', 1.2);
INSERT dbo.Dept VALUES ('Sales',.8);
INSERT dbo.Dept VALUES ('IT',2.5);
INSERT dbo.Dept VALUES ('Manufacturing',1.0);
go
INSERT dbo.Employee VALUES( 1, 'Smith', 'Sam', 54000, 2.0, '1/1/97', '4/1/2001' );
INSERT dbo.Employee VALUES( 1, 'Nelson', 'Slim', 78000, 1.5, '9/1/88', '1/1/2000' );
INSERT dbo.Employee VALUES( 2, 'Ball', 'Sally', 45000, 3.5, '2/1/99', '1/1/2001' );
INSERT dbo.Employee VALUES( 2, 'Kelly', 'Jeff', 85000, 2.4, '10/1/83','9/1/1998' );
INSERT dbo.Employee VALUES( 3, 'Guelzow', 'Dave', 120000, 4.0, '7/1/95', '6/1/2001' );
INSERT dbo.Employee VALUES( 3, 'Cliff', 'Melissa', 95000, 1.8, '2/1/99', '9/1/1997' );
INSERT dbo.Employee VALUES( 4, 'Reagan', 'Frankie', 75000, 2.9, '4/1/00', '4/1/2000' );
INSERT dbo.Employee VALUES( 4, 'Adams', 'Hank', 34000, 3.2, '9/1/98', '9/1/1998' );
go

-- assume raise date is 5/1/2002
SELECT * from dbo.Dept;
SELECT * from dbo.Employee;

-- test required data
SELECT LastName, Salary,
DateDiff(yy, DateHire, '5/1/2002') as YearsCompany,
DateDiff(mm, DatePosition, '5/1/2002') as MonthPosition,
CASE
WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating
ELSE 0
END as Performance,
Dept.RaiseFactor
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptID;

-- Test the raise amount
SELECT LastName,
(2 + (((DateDiff(yy, DateHire, '5/1/2002') * .1)
+ (DateDiff(mm, DatePosition, '5/1/2002') * .02)
+ (CASE
WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating
ELSE 0
END * .5 ))
* Dept.RaiseFactor))/100 as EmpRaise
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptID;

-- Perform the Update
Update Employee Set Salary = Salary * (1 +
(2 + (((DateDiff(yy, DateHire, '5/1/2002') * .1)
+ (DateDiff(mm, DatePosition, '5/1/2002') * .02)
+ (CASE
WHEN Employee.PerformanceRating >= 2 THEN Employee.PerformanceRating
ELSE 0
END * .5 ))
* Dept.RaiseFactor))/100 )
FROM dbo.Employee
JOIN dbo.Dept
ON Employee.DeptID = Dept.DeptID;


SELECT FirstName, LastName, Salary
FROM dbo.Employee;
Go

Drop Table dbo.Employee;
Go
Drop table dbo.Dept ;

-----------------------------------------------------------
-- Deleting Data

USE OBXKites;

DELETE FROM OBXKites.dbo.Product;
-- w/o WHERE clause deletes all rows

DELETE FROM dbo.Product
WHERE ProductID = 'DB8D8D60-76F4-46C3-90E6-A8648F63C0F0';

-- delete all the product in the books category
DELETE Product
FROM dbo.Product
JOIN ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductcategoryName = 'Video';

-- Building a foreign key with referential integrity that cascades deletes
USE CHA2;

CREATE TABLE dbo.Event_mm_Guide (
EventGuideID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED,
EventID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Event ON DELETE CASCADE,
GuideID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Guide ON DELETE CASCADE,
LastName VARCHAR(50) NOT NULL,
)
ON [Primary];
go


--ANSI Standard alternative to Delete From
DELETE FROM Table1 a
WHERE EXISTS (SELECT *
FROM Table2 b
WHERE
EMPL_STATUS = 'A'
AND
a.EMPLID = b.EMPLID
);

-- Truncate Table
TRUNCATE TABLE dbo.InventoryTransaction;


-----------------------------------------------------------
-- Using the Output clause

USE CHA2

INSERT dbo.Guide (LastName, FirstName, Qualifications)
OUTPUT Inserted.*
VALUES('Nielsen', 'Paul', 'diver');

UPDATE dbo.Guide
SET Qualifications = 'Scuba'
OUTPUT Deleted.Qualifications as OldQuals, Inserted.Qualifications as NewQuals
Where GuideID = 3;

-- with table variable
DECLARE @DeletedGuides TABLE (
GuideID INT,
LastName VARCHAR(50),
FirstName VARCHAR(50)
);

DELETE dbo.Guide
OUTPUT Deleted.GuideID, Deleted.LastName, Deleted.FirstName
INTO @DeletedGuides
WHERE GuideID = 2;

SELECT * FROM @DeletedGuides;



Read Users' Comments (0)

Distributing Queries

Posted in Labels: | at 10:15 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 7 - Distributed Queries
--
-- Paul Nielsen
-----------------------------------------------------------

-------------------------------------------------
-- Linking to External Data Sources

EXEC sp_addlinkedserver
@server = 'HPPRESENT\SECOND',
@srvproduct = 'SQL Server';

EXEC sp_addlinkedserver
@server = 'nyack',
@datasrc = 'HPPRESENT\SECOND',
@srvproduct = '',
@provider='SQLOLEDB';

-- Viewing Linked Servers
SELECT [Name], Product, Provider, Data_source
FROM sys.servers
WHERE Is_Linked = 1;

-- Drop a linked server
EXEC sp_DropServer @server = 'Nyack';

-- Distributed Security and Logins
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'HPPRESENT\SECOND',
@useself = 'false',
@locallogin = 'HPPRESENT\total',
@rmtuser = 'sa',
@rmtpassword = 'P@s$w0rD';

-- Linking with Excel
EXEC sp_droplinkedsrvlogin 'CHA1_Schedule', NULL
EXEC sp_DropServer @server = 'CHA1_Schedule';

EXEC sp_addlinkedserver
@server = 'CHA1_Schedule',
@srvproduct = 'Excel',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\SQLData\CHA1_Schedule.xls',
@provstr = 'Excel 5.0'
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'CHA1_Schedule',
@useself = 'false';

-- Linking with Microsoft Access
EXEC sp_DropServer @server = 'CHA1_Customers';
EXEC sp_addlinkedserver
'CHA1_Customers',
'Access 2003',
'Microsoft.Jet.OLEDB.4.0',
'C:\SQLData\CHA1_Customers.mdb';


----------------------------------------
-- Executing Distributed Queries

-- Accessing a Local SQL Server Database
USE CHA2;

SELECT LastName, FirstName
FROM OBXKites.dbo.Contact;

SELECT LastName, FirstName
FROM OBXKites..Contact;


-- Four Part Name
-- to SQL Server
SELECT LastName, FirstName
FROM [HPPresent\Second].Family.dbo.person;

-- to Excel
SELECT DISTINCT [Base Camp]
FROM CHA1_Schedule...[Base_Camp]
WHERE [Base Camp] IS NOT NULL;

-- to Access
SELECT *
FROM CHA1_Customers...Customers

UPDATE CHA1_Customers...Customers
SET ContactLastName = 'Wilson'
WHERE CustomerID = 1;

-- Four Part Name Black Box
Select o.orderid, c.contactcode, o.ordernumber, quantity
from [HPPresent\Second].obxkites.dbo.orderdetail od
join [HPPresent\Second].obxkites.dbo.[order] o
on o.orderid = od.orderid
join [HPPresent\Second].obxkites.dbo.contact c
on o.contactid = c.contactid
where
-- comment out combinations of where clause conditions
c.contactcode = '102'
and o.ordernumber = 3
and o.Orderid = '289623A7-09B8-4E9B-A2A3-075F9E6C34AC';

-- without linked server - SQL Server
SELECT FirstName, Gender
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=HPPresent\Second;Trusted_Connection=yes;'
).Family.dbo.Person;

-- without linked server - Access
SELECT ContactFirstName, ContactLastName
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source = C:\SQLData\CHA1_Customers.mdb')...Customers;
-----------------------------------------------
-- Using Pass-Through Queries

--OpenQuery()
SELECT *
FROM OPENQUERY(CHA1_Schedule,
'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"');

UPDATE -- to Excel
OPENQUERY(CHA1_Schedule, 'SELECT * FROM Tour WHERE Tour = "Gauley River Rafting"')
SET [Base Camp] = 'Ashville'
WHERE Tour = 'Gauley River Rafting';

-- OpenRowSet
SELECT *
FROM OPENROWSET ('SQLNCLI', 'Server=hppresent\second;Trusted_Connection=yes;',
'SELECT LastName, FirstName
FROM Family.dbo.person;')


-------------------------------------------------------
-- Controlling Distributed Transactions

USE Family;
SET xact_abort on;
BEGIN DISTRIBUTED TRANSACTION;

UPDATE Person
SET LastName = 'Johnson'
WHERE PersonID = 10;

UPDATE [hppresent\second].Family.dbo.Person
SET LastName = 'Johnson'
WHERE PersonID = 10;

COMMIT;









Read Users' Comments (0)

Views

Posted in Labels: | at 10:15 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 6 - Selecting Data through Views
--
-- Paul Nielsen
-----------------------------------------------------------


-- Creating the View

USE CHA2

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vEventList')
DROP VIEW dbo.vEventList

go
CREATE VIEW dbo.vEventList
AS
SELECT dbo.CustomerType.Name AS Customer,
dbo.Customer.LastName, dbo.Customer.FirstName,
dbo.Customer.Nickname,
dbo.Event_mm_Customer.ConfirmDate, dbo.Event.Code,
dbo.Event.DateBegin, dbo.Tour.Name AS Tour,
dbo.BaseCamp.Name, dbo.Event.Comment
FROM dbo.Tour
INNER JOIN dbo.Event
ON dbo.Tour.TourID = dbo.Event.TourID
INNER JOIN dbo.Event_mm_Customer
ON dbo.Event.EventID = dbo.Event_mm_Customer.EventID
INNER JOIN dbo.Customer
ON dbo.Event_mm_Customer.CustomerID
= dbo.Customer.CustomerID
LEFT OUTER JOIN dbo.CustomerType
ON dbo.Customer.CustomerTypeID
= dbo.CustomerType.CustomerTypeID
INNER JOIN dbo.BaseCamp
ON dbo.Tour.BaseCampID = dbo.BaseCamp.BaseCampID
go

SELECT * FROM dbo.vEventList


-- Where
SELECT *
FROM dbo.vEventList
WHERE (Code = '01-016')


-- Order By

SELECT *
FROM vEventList
ORDER BY LastName, FirstName
go

-----------------------------------------------------------
-- Preventing Disappearing Rows

go
CREATE VIEW vCapeHatterasTour
AS
SELECT [Name], BaseCampID
FROM Tour
WHERE BaseCampID = 2

go
SELECT * FROM vCapeHatterasTour

INSERT vCapeHatterasTour ([Name], BaseCampID)
VALUES ('Blue Ridge Parkway Hike', 1)

SELECT * FROM vCapeHatterasTour

-- with check option

DELETE vCapeHatterasTour
WHERE [Name] = 'Blue Ridge Parkway Hike'
go
ALTER VIEW vCapeHatterasTour
AS
SELECT [Name], BaseCampID
FROM Tour
WHERE BaseCampID = 2
WITH CHECK OPTION
go

INSERT vCapeHatterasTour ([Name], BaseCampID)
VALUES ('Blue Ridge Parkway Hike', 1)
-----------------------------------------------------------
-- Protecting from Schema Changes

-- With Schema Binding

Use Tempdb
go
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vTest')
DROP View dbo.vTest
go
IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'Test')
DROP TABLE dbo.Test

go
CREATE TABLE dbo.Test (
[Name] NVARCHAR(50)
)
go

CREATE VIEW dbo.vTest
WITH SCHEMABINDING
AS
SELECT [Name] FROM dbo.Test

go
ALTER TABLE dbo.Test
ALTER COLUMN [Name] NVARCHAR(100)

-- Correcting schema/view out-of-sync
USE CHA2
sp_refreshview 'vCapeHatterasTour'

-----------------------------------------------------------
-- Nesting Views
USE CHA2
go

IF EXISTS(SELECT * FROM SysObjects WHERE Name = 'vEventList30days')
DROP VIEW dbo.vEventList30days
go

CREATE VIEW dbo.vBaseCampCount
AS
SELECT dbo.vEventList.Name as BaseCamp, Count(*) as [Attendees]
FROM dbo.vEventList
GROUP BY dbo.vEventList.Name





Read Users' Comments (0)

Full text Search

Posted in Labels: | at 10:14 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 5 - Using Full-Text Search
--
-- Paul Nielsen
-----------------------------------------------------------


-----------------------------------------------------------
-- Creating a Full-Text Index

USE AESOP

-- SQL Where Like
SELECT Title
FROM Fable
WHERE Fabletext LIKE '%lion%'
AND Fabletext LIKE '%bold%'

CREATE FULLTEXT CATALOG AesopFT

CREATE FULLTEXT INDEX ON dbo.Fable(Title, Moral, Fabletext)
KEY INDEX FablePK ON AesopFT
WITH CHANGE_TRACKING AUTO
GO


-----------------------------------------------------
-- Searching for Words

USE AESOP

-- Contains
SELECT Title
FROM Fable
WHERE CONTAINS (Fable.*,'Lion')

-- Joining with Full-Text Search
SELECT *
FROM CONTAINSTABLE (Fable, *, 'Lion')

SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'Lion') FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC

SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'Lion', 2) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank

-- Advanced Options

-- Phrases
SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Wolf! Wolf!" ')

-- Or, And
SELECT Title
FROM Fable
WHERE CONTAINS (FableText,'Tortoise AND Hare')

SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Thrifty AND supperless" ')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'Thrifty')
AND CONTAINS(*,'supperless')

-- Wildcards *
SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Hunt*" ')

-- Near
SELECT Title
FROM Fable
WHERE CONTAINS (*,'pardoned NEAR forest')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'lion NEAR paw NEAR bleeding')

SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *,'life NEAR death') FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC

-- Weighting
SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, fabletext, 'ISABOUT (Lion weight (.5), Brave weight (.5), Eagle weight (.5))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, fabletext, 'ISABOUT (Lion weight (.2), Brave weight (.2), Eagle weight (.8))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'ISABOUT (Lion weight (.2), Brave weight (.2), Eagle weight (.8))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

-- Inflection
SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,pity)')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,geese)')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,carry)')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,fly)')


----------------------------------------------------
-- Using Fuzzy Searches

-- FreeText

SELECT Title
FROM Fable
WHERE FREETEXT (*,'The Tortoise beat the Hare in the big race')

SELECT Title
FROM Fable
WHERE FREETEXT (*,'The eagle was shot by an arrow')

SELECT Title
FROM Fable
WHERE FREETEXT (*,'The brave hunter kills the lion')

-- FreeText Table

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN FREETEXTTABLE (Fable, *, 'The brave hunter kills the lion',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN FREETEXTTABLE (Fable, *, 'The eagle was shot by an arrow',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

Read Users' Comments (0)

Aggregation Functions

Posted in Labels: | at 10:14 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 4 - Aggregating Data
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Using the new Windowing Functions

Use AdventureWorks;

SELECT ProductID, TransactionDate
FROM Production.TransactionHistory
ORDER BY TransactionDate;

SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNumDate,
Row_Number() OVER(ORDER BY ProductID) + 5000 as RowNumiD
FROM Production.TransactionHistory
-- not permitted
Where Row_Number() OVER(ORDER BY TransactionDate) Between 10001 AND 20000
ORDER BY TransactionDate;

-- Select mid-range of rownumbers & every 5th row
SELECT RowNum, ProductID, TransactionDate
FROM (SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNum
FROM Production.TransactionHistory) sq
WHERE RowNum Between 10001 AND 20000
AND RowNum%5 = 0
ORDER BY RowNum ;

-- Ranking and Windowing
SELECT SalesOrderId, CustomerId, TerritoryID,
ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNum,
RANK() OVER(ORDER BY CustomerId) AS [Rank],
DENSE_RANK() OVER(ORDER BY CustomerId) AS [DenseRank],
NTILE(5) OVER(ORDER BY CustomerId) AS NTile5,
RANK() OVER(PARTITION BY TerritoryID ORDER BY CustomerId) AS [Partition]
FROM Sales.SalesOrderHeader;

-- Percentile Ranking
SELECT SalesOrderId, CustomerId,
NTILE(100) OVER(ORDER BY CustomerId) AS Percentile
FROM Sales.SalesOrderHeader
ORDER BY CustomerId

-- Sample Data
SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10000 rows)

SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10 percent)
ORDER BY CustomerId


--------------------------------------------------------------------
-- Using Aggregate Functions
go

-- Build the sample data
USE tempdb

IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'RawData')
DROP TABLE RawData
go

CREATE TABLE RawData (
Region VARCHAR(10),
Category CHAR(1),
Amount INT,
SalesDate DateTime
)

go

INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 12, '11/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 24, '11/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 15, '12/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', 28, '12/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'X', 11, '1/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'MidWest', 'X', 24, '1/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'West', 'X', 36, '2/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 47, '2/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'MidWest', 'Y', 38, '3/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', 62, '3/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Z', 33, '4/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'MidWest', 'Z', 83, '4/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'West', 'Z', 44, '5/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Z', 55, '5/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'X', 68, '6/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'X', 86, '6/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 54, '7/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 63, '7/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 72, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', 91, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')

-- check the Amount
SELECT * FROM RawData

-- Simple Aggregations
SELECT
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData

-- How Nulls affect Averages
SELECT Avg(Cast((Amount)as Numeric(9,5))) as [Numeric Avg],
Avg(Amount) as [Int Avg],
Sum(Amount) / Count(*) as [Manual Avg]
FROM RawData



-- Statistical Functions
SELECT
Count(*) as Count,
StDevP(Amount) as [StDevP],
VarP(Amount) as [VarP]
FROM RawData

SELECT
Count(*) as Count,
StDev(Amount) as [StDev],
Var(Amount) as [Var]
FROM RawData
WHERE Year(SalesDate) = 2006


---------------------------------------------
-- Grouping and Aggregating

SELECT Category,
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
GROUP BY Category

-- Group by occurs after the where clause
SELECT Category,
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
WHERE Year(SalesDate) = 2006
GROUP BY Category

-- Grouping By multiple columns
SELECT Year(SalesDate) as [Year], DatePart(q,SalesDate) as [Quarter],
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
GROUP BY Year(SalesDate), DatePart(q,SalesDate)

---------------------------------------------
-- Including Descriptions

/*
-- This example SHOULD generate an error...
SELECT Region, Count(*)
FROM RawData
*/

IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'RawCategory')
DROP TABLE RawCategory

CREATE TABLE RawCategory (
RawCategoryID CHAR(1),
CategoryName VARCHAR(25)
)

INSERT RawCategory (RawCategoryID, CategoryName)
VALUES ('X', 'Sci-Fi')
INSERT RawCategory (RawCategoryID, CategoryName)
VALUES ('Y', 'Philosophy')
INSERT RawCategory (RawCategoryID, CategoryName)
VALUES ('Z', 'Zoology')

-- including Amount outside the aggregate function or group by will cause an error
/*
SELECT Category, CategoryName,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData R
JOIN RawCategory C
ON R.Category = C.RawCategoryID
GROUP BY Category
*/


-- Solution 1: include all Amount in the Group By
SELECT Category, CategoryName,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData R
JOIN RawCategory C
ON R.Category = C.RawCategoryID
GROUP BY Category, CategoryName
ORDER BY Category, CategoryName

-- Solution 2: Aggregate in Subquery, description in outer query
SELECT sq.Category, CategoryName, sq.[Sum], sq.[Avg], sq.[Min], sq.[Max]
FROM (SELECT Category,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
GROUP BY Category ) sq
JOIN RawCategory C
ON sq.Category = C.RawCategoryID
ORDER BY Category, CategoryName


--------------------------------------------------------------
-- Including all Group By Values

-- Left Outer Join Group Bys
SELECT Year(SalesDate) AS [Year],
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
WHERE Year(SalesDate) = 2006 -- (There are a few 2005 rows in the RawData table)
GROUP BY ALL Year(SalesDate)



------------------------------------------------------------------
-- Nesting Aggregations

-- Which Category sold the most in each quarter?

-- Can't nest aggregate function - error:
/*
Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, max(Sum(Amount)) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
ORDER BY Y,Q
*/

-- Solution: Including Detail description

SELECT MaxQuery.Y, MaxQuery.Q, AllQuery.Category, MaxQuery.MaxSum as sales
FROM (-- Find Max Sum Per Year/Quarter
Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
) MaxQuery
JOIN (-- All Amount Query
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
)AllQuery
ON MaxQuery.Y = AllQuery.Y
AND MaxQuery.Q = AllQuery.Q
AND MaxQuery.MaxSum = AllQuery.Sum
ORDER BY MaxQuery.Y, MaxQuery.Q


---------------------------------------------
-- Filtering Group By Results

SELECT Year(SalesDate) as [Year],
DatePart(q,SalesDate) as [Quarter],
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg]
FROM RawData
GROUP BY Year(SalesDate), DatePart(q,SalesDate)
HAVING Avg(Amount) > 25
ORDER BY [Year], [Quarter]


--------------------------------------------------------
-- Adding Grand Totals

-- Sum of single column Group By (Roll-Up)
SELECT Grouping(Category), Category,
CASE Grouping(Category)
WHEN 0 THEN Category
WHEN 1 THEN 'All Categories'
END AS Category,
Count(*) as Count
FROM RawData
GROUP BY Category
WITH ROLLUP

-- Sum of Mulitple column Group By (Roll-Up)
-- Second Group by Column w/ subtotals

SELECT
CASE Grouping(Category)
WHEN 0 THEN Category
WHEN 1 THEN 'All Categories'
END AS Category,
CASE Grouping(Year(SalesDate))
WHEN 0 THEN Cast(Year(SalesDate) as CHAR(8))
WHEN 1 THEN 'All Years'
END AS Year,
Count(*) as Count
FROM RawData
GROUP BY Category, Year(SalesDate)
WITH ROLLUP

-- Multidimensional column Group By (Cube)
SELECT
CASE Grouping(Category)
WHEN 0 THEN Category
WHEN 1 THEN 'All Categories'
END AS Category,
CASE Grouping(Year(SalesDate))
WHEN 0 THEN Cast(Year(SalesDate) as CHAR(8))
WHEN 1 THEN 'All Years'
END AS Year, Count(*) as Count
FROM RawData
GROUP BY Category, Year(SalesDate)
WITH CUBE

-- Compute
SELECT Category, SalesDate, Amount
FROM RawData
WHERE Year(SalesDate) = '2006'
COMPUTE Avg(Amount), sum(Amount)

SELECT Category, SalesDate, Amount
FROM RawData
WHERE Year(SalesDate) = '2006'
ORDER BY Category
COMPUTE Avg(Amount), sum(Amount)
BY Category

SELECT Category, SalesDate, Amount
FROM RawData
WHERE Year(SalesDate) = '2006'
ORDER BY Category
COMPUTE avg(Amount), sum(Amount)
COMPUTE sum(Amount)
BY Category


-------------------------------------------------------------------
-- Generating Crosstab(Pivot) Tables

-- Fixed Column CrossTab with Category Subtotal- CASE Method
SELECT Category,
SUM(Case Region WHEN 'South' THEN Amount ELSE 0 END) AS South,
SUM(Case Region WHEN 'NorthEast' THEN Amount ELSE 0 END) AS NorthEast,
SUM(Case Region WHEN 'MidWest' THEN Amount ELSE 0 END) AS MidWest,
SUM(Case Region WHEN 'West' THEN Amount ELSE 0 END) AS West,
SUM(Amount) as Total
FROM RawData
GROUP BY Category
ORDER BY Category


-- Fixed Column Crosstab - PIVOT Method
SELECT Category, SalesDate, South, NorthEast, MidWest, West
FROM RawData
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt

SELECT Category, South, NorthEast, MidWest, West
FROM (Select Category, Region, Amount from RawData) sq
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt


-- Fixed Column Crosstab with Category Subtotal - PIVOT Method
SELECT Category, South, NorthEast, MidWest, West,
IsNull(South,0) + IsNull(NorthEast,0) + IsNull(MidWest,0) + IsNull(West,0) as Total
FROM (Select Category, Region, Amount from RawData) sq
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt

-- Fixed Column Crosstab with Filter - PIVOT Method
-- Must filter within the FROM clause (using subquery) prior to Pivot operation
SELECT Category, South, NorthEast, MidWest, West,
IsNull(South,0) + IsNull(NorthEast,0) + IsNull(MidWest,0) + IsNull(West,0) as Total
FROM (Select Region, Category, Amount
From RawData
Where Category = 'Z') sq
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt

-- Dynamic CrossTabs with Multiple Assignment Variable and Pivot Method
-- Appending to a variable within a query to dynamically generate the column names

DECLARE @SQLStr NVARCHAR(1024)
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as a

SET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)

SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM (Select Category, Region, Amount from RawData) sq PIVOT (Sum (Amount) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStr

EXEC sp_executesql @SQLStr

---------------------------------------------------------------
-- Calculating Cummulative Sums (Running Totals)

-- Simple Correlated Subquery Method
-- Groups same DateTime data together
SELECT SalesDate, Amount,
(SELECT SUM(Amount)
FROM RawData
WHERE SalesDate <= R.SalesDate ) as Balance
FROM RawData R
ORDER BY SalesDate


-- Cursor based - Improved Solution
ALTER TABLE RawData
ADD Balance INT
ALTER TABLE RawData
ADD ID INT IDENTITY NOT NULL PRIMARY KEY

-- Post some balances
UPDATE RawData
SET Balance =
(SELECT SUM(Amount)
FROM RawData
WHERE SalesDate <= R.SalesDate )
FROM RawData R
WHERE SalesDate <= '4/1/2006'


-- Cursor - 5 steps
DECLARE
@BeginningBalanceDate DateTime,
@BeginningBalanceAmount INT,
@CurrentBalance INT,
@CurrentID INT,
@CurrentAmount INT

SELECT
@BeginningBalanceDate = SalesDate,
@CurrentBalance = Balance
FROM RawData
WHERE SalesDate =
(SELECT Max(SalesDate) -- Identify the last good balance date
FROM RawData
WHERE SalesDate < -- row prior to earliest row with null balance
(SELECT Min(SalesDate)
FROM RawData
WHERE Balance IS NULL))

-- 1 Declare the cursor
-- Select all rows following last row with a good balance
-- (this will handle gaps in the balance)
DECLARE cBalance CURSOR FAST_FORWARD READ_ONLY
FOR SELECT ID, Amount
FROM RawData
WHERE SalesDate > @BeginningBalanceDate
ORDER BY SalesDate
-- 2 Open the Cursor
OPEN cBalance
-- 3 Prime the Cursor, then loop
FETCH cBalance INTO @CurrentID, @CurrentAmount -- prime the cursor
WHILE @@Fetch_Status = 0
BEGIN
SET @CurrentBalance = @CurrentBalance + IsNull(@CurrentAmount, 0)
UPDATE RawData
SET Balance = @CurrentBalance
WHERE ID = @CurrentID
PRINT @CurrentBalance
-- 3
FETCH cBalance INTO @CurrentID, @CurrentAmount -- fetch next
END
-- 4 Close
CLOSE cBalance
-- 5 Clean up Memory
DEALLOCATE cBalance

-- view the result
SELECT * FROM RawData ORDER BY SalesDate







Read Users' Comments (0)

Subscribe to: Posts (Atom)