Home    Sample Chapters    Addison-Wesley Professional    Chapter 7: T-SQL Enhancements from the book A First Look at SQL Server 2005 for Developers

Chapter 7 - T-SQL Enhancements Wednesday, July 21, 2004

To order this book or to find additional details, go to www.awprofessional.com.

Few technologies have been as eagerly anticipated as Microsoft SQL Server 2005 ("Yukon"). Now, three SQL Server insiders deliver the definitive hands-on preview--accurate, comprehensive, and packed with examples.

A First Look at SQL Server 2005 for Developers starts where Microsoft's white papers and Web articles leave off, showing working developers how to take full advantage of Yukon's key innovations. It draws on exceptional cooperation from Microsoft's Yukon developers and the authors' hands-on access to Yukon since its earliest alpha releases.

You'll find practical explanations of Yukon's new data model, built-in .NET hosting, improved programmability, SQL-99 compliance, and much more. Virtually every key concept is illuminated via sample code tested with Microsoft's public beta.

Copyright © 2004 Pearson Education, Addison-Wesley Professional
www.awprofessional.com

Improvements to Transact-SQL

• SNAPSHOT isolation- Additional isolation level that does not use write locks
• Statement-level recompile- More efficient recompilation of stored procedures
• Event notifications- Integration of Data Definition Language (DDL) and DML operations with Service Broker
• Large data types- New data types that deprecate TEXT and IMAGE
• DDL triggers- Triggers that fire on DDL operations
• Common Table Expressions- Declarative syntax that makes a reusable expression part of a query
• Hierarchical queries- Declarative syntax for tree-based queries
• PIVOT- Declarative syntax aggregations across columns and converting columns to rows
• APPLY- New JOIN syntax made for use with user-defined functions and XML
• TOP- Row count based on an expression
• Transaction abort- TRY/CATCH syntax for handling errors

SNAPSHOT Isolation

ALTER DATABASE pubs
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE pubs
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANS
-- SQL Expressions
COMMIT TRANS

-- alter the database
ALTER DATABASE pubs
SET ALLOW_SNAPSHOT_ISOLATION ON
SET READ_COMMITTED_SNAPSHOT ON
GO

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN
-- SQL expression will be executed as READ COMMITTED using versioning
END TRAN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
-- SQL expression will be executed as READ COMMITTED using versioning
END TRAN

SELECT DATABASEPROPERTYEX ('pubs', 'SnapshotIsolationFramework')

--it is necessary to run
--SET ALLOW_SNAPSHOT_ISOLATION ON
--if that's not done already
CREATE TABLE snapTest ([id] INT IDENTITY, 
		col1 VARCHAR(15))

--insert some data
INSERT INTO snapTest VALUES(1,'Niels')

1. We start a transaction under SNAPSHOT isolation and update one column in one row. This causes SQL Server to store a copy of the original value in TEMPDB. Notice that we do not commit or roll back at this stage, so locks are held. If we were to run sp_lock, we would see an exclusive lock on the primary key.
2. We start a new transaction under a new session and try to read from the same row that is being updated at the moment. This is the row with an exclusive lock. If this had been previous versions of SQL Server (running under at least READ COMMITTED), we would be locked out. However, running in SNAPSHOT mode, SQL Server looks in the version store in TEMPDB to retrieve the latest committed value and returns "Niels".
3. We commit the transaction, so the value is updated in the database and another version is put into the version store.
4. User 2 does a new SELECT (from within his original transaction) and will now receive the original value, "Niels".
5. User 2 finally commits the transaction.
6. User 2 does a new SELECT (after his transaction commits) and will now receive the new value, "NewNiels".

Drawbacks of Versioning

Msg 3960, Level 16, State 1, Line 1. Cannot use snapshot isolation
to access table 'tab' in database 'pubs'. Snapshot transaction aborted
due to update conflict. Retry transaction.

Monitoring Versioning

• DATABASEPROPERTYEX - Tells us if SNAPSHOT is on
• sys.fn_top_version_generators()-Tables with most versions
• sys.fn_transaction_snapshot()-Transaction active when a SNAPSHOT transaction starts
• sys.fn_transactions()-Includes information about SNAPSHOT transaction (or not), if SNAPSHOT includes information about version chains and SNAPSHOT timestamps

• Average version store data-generation rate (kilobytes per minute)
• Size of current version store (kilobytes)
• Free space in TEMPDB (kilobytes)
• Space used in the version store for each database (kilobytes)
• Longest running time in any SNAPSHOT transaction (seconds)

Statement-Level Recompilation

• Compiled plan (a.k.a. query plan) - A read-only data structure used by any number of users. The plan is reentrant, which implies that all users share the plan and no user context information (such as data variable values) is stored in the compiled plan. There are never more than one or two copies of the query plan in memory-one copy for all serial executions and another for all parallel executions.
• Executable plan - A data structure for each user that concurrently executes the query. This data structure, which is called the executable plan or execution context, holds the data specific to each user's execution, such as parameter values.

• Schema changes
• Threshold changes in rows
• Certain SET options

CREATE PROCEDURE test2
AS
SELECT 'before set option'
--//change a set option
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT 'after set option'

1. Catalog the procedure in Listing 7-1.
2. Open the SQL Server Profiler and from the File menu, select New | Trace.
3. When the Trace Properties dialog comes up, choose the Events tab.
4. In the Stored Procedures event group, choose the SP:Recompile event, click the Add button, as shown in Figure 7-2, and then click Run.
5. Execute the procedure a couple of times from Query Analyzer and view the trace output.
6. The output from the trace will show a couple of entries in the Event Class column with the value of SP:Recompile, as in Figure 7-3. This indicates that the procedure has been recompiled.

SELECT * FROM syscacheobjects
WHERE dbid = db_id('pubs')
AND objid = object_id('test2')

DDL Triggers

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH ENCRYPTION ]
{ FOR | AFTER } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
{ AS
{ sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }
}
< method_specifier > ::=
assembly_name:class_name[::method_name]

• The ON clause in a DDL trigger refers to either the scope of the whole database server (ALL SERVER) or the current database (DATABASE).
• ADDL trigger cannot be an INSTEAD OF trigger.
• The event for which the trigger fires is defined in the event_type argument, which for several events is a comma-delimited list. Alternatively, you can use the blanket argument DDL_DATABASE_LEVEL_EVENTS.

--first create a table to log to
CREATE TABLE ddlLog (id INT PRIMARY KEY IDENTITY,
	logTxt VARCHAR(MAX))
GO
--create our test table
CREATE TABLE triTest (id INT PRIMARY KEY)
GO

-- create the trigger
CREATE TRIGGER ddlTri
ON DATABASE
AFTER DROP_TABLE
AS
	INSERT INTO ddlLog VALUES('table dropped')

DROP TABLE triTest
SELECT * FROM ddlLog

Eventdata

• The time of the event
• The SPID of the connection that caused the trigger to fire
• The login name and user name of the user who executed the statement
• The type of the event

• Database
• Schema
• Object
• ObjectType
• TSQLCommand

-- alter the trigger
ALTER TRIGGER ddlTri
ON DATABASE
AFTER DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO ddlLog VALUES CONVERT(VARCHAR(max)eventdata())

--delete all entries in ddlLog
DELETE ddlLog

--create a new table
CREATE TABLE evtTest (id INT PRIMARY KEY)

--select the logTxt column with the XML
SELECT logTxt
FROM ddlLog

<EVENT_INSTANCE>
	<PostTime>2004-01-30T11:58:47.217</PostTime>
	<SPID>57</SPID>
	<EventType>CREATE_TABLE</EventType>
	<ServerName>ZMV44</ServerName>
	<LoginName>ZMV44\Administrator</LoginName>
	<UserName>ZMV44\Administrator</UserName>
	<DatabaseName>pubs</DatabaseName>
	<SchemaName>dbo</SchemaName>
	<ObjectName>foo</ObjectName>
	<ObjectType>TABLE</ObjectType>
	<TSQLCommand>
		<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
			ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
			ENCRYPTED="FALSE" />
		<CommandText>
			CREATE TABLE evtTest (id int primary key)
		</CommandText>
	</TSQLCommand>
</EVENT_INSTANCE>

DECLARE @data XML
SELECT @data = logTxt FROM ddlLog
WHERE id = 11

SELECT
	CONVERT(NVARCHAR(100), @data.query('data(//EventType)')) EventType,
	CONVERT(NVARCHAR(100), @data.query('data(//Object)')) Object,
	CONVERT(NVARCHAR(100), @data.query('data(//TSQLCommand/CommandText)')) Command

Event Notifications

CREATE EVENT NOTIFICATION event_notification_name
ON { SERVER | DATABASE |
[ ENABLED | DISABLED ]
{ FOR { event_type |
DDL_DATABASE_LEVEL_EVENTS } [ ,...n ]
TO broker_service

• event_notification_name-This is the name of the event notification.
• SERVER-The scope of the event notification is the current server.
• DATABASE-The scope of the event notification is the current database.
• ENABLED-This specifies that the event notification is active when the CREATE statement has executed.
• DISABLED-This specifies that the event notification is inactive until the notification is activated by executing an ALTER EVENT NOTIFICATION statement.
• event_type-This is the name of an event that, after execution, causes the event notification to execute. SQL Server Books Online has the full list of events included in event_type.
• DDL_DATABASE_LEVEL_EVENTS-The event notification fires after any of the CREATE, ALTER, or DROP statements that can be indicated in event_type execute.
• broker_service-This is the SSB service to which SQL Server posts the data about an event.

--first we need a queue
CREATE QUEUE queue evtDdlNotif
WITH STATUS = ON

--then we can create the service
CREATE SERVICE evtDdlService
ON QUEUE evtDdlNotif

--this is a MS supplied contract
--which uses an existing message type
--{http://schemas.microsoft.com/SQL/Notifications}EventNotification
(http://schemas.microsoft.com/SQL/Notifications/PostEventNotification)

CREATE EVENT NOTIFICATION ddlEvents
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
TO SERVICE evtDdlService

WAITFOR(RECEIVE * FROM evtDdlNotif)

DECLARE @msgtypeid INT
DECLARE @msg VARBINARY(MAX)
WAITFOR(RECEIVE TOP(1)
	@msgtypeid = message_type_id,
	@msg = message_body
	FROM evtDdlNotif
)
--check if this is the correct message type
IF @msgtypeid = 20
BEGIN
	--do something useful WITH the message
	--here we just select it as a result
	SELECT CONVERT(NVARCHAR(MAX), @msg)
END

CREATE EVENT NOTIFICATION loginEvents ON SERVER
FOR audit_login TO SERVICE evtLoginService

<EVENT_INSTANCE>
	<PostTime>2003-06-29T09:46:23.623</PostTime>
	<SPID>51</SPID>
	<EventType>AUDIT_LOGIN</EventType>
	<ServerName>ZMV44</ServerName>
	<LoginName>ZMV44\Administrator</LoginName>
	<UserName>ZMV44\Administrator</UserName>
	<Database>eventstest</Database>

	<!- additional information elided ->

</EVENT_INSTANCE>

Large Value Data Types

CREATE TABLE largeValues (
lVarchar VARCHAR(MAX),
lnVarchar NVARCHAR(MAX),
lVarbinary VARBINARY(MAX)
)

CREATE FUNCTION dovmax(@in VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
	-- supports concatenation
	RETURN @in + '12345'
END

T-SQL Language Enhancements

TOP

--create a table and insert some data
CREATE TABLE toptest (col1 VARCHAR(150))

INSERT INTO toptest VALUES('Niels1')
INSERT INTO toptest VALUES('Niels2')
INSERT INTO toptest VALUES('Niels3')
INSERT INTO toptest VALUES('Niels4')
INSERT INTO toptest VALUES('Niels5')

--this returns 'Niels1' and 'Niels2'
SELECT TOP(2) * FROM toptest

--this sets 'Niels1' and 'Niels2' to 'hi'
UPDATE TOP(2) toptest SET col1 = 'hi'
SELECT * FROM toptest

--the two rows with 'hi' are deleted
DELETE TOP(2) toptest
SELECT * FROM toptest

--create a new table and insert some data
CREATE TABLE toptest2 (col1 VARCHAR(150))
INSERT INTO toptest2 VALUES('Niels1')
INSERT INTO toptest2 VALUES('Niels2')
INSERT INTO toptest2 VALUES('Niels3')
INSERT INTO toptest2 VALUES('Niels4')
INSERT INTO toptest2 VALUES('Niels5')

--'Niels1' and 'Niels2' are inserted
INSERT top(2) toptest
SELECT * FROM toptest2
SELECT * FROM toptest

--declare 3 variables
DECLARE @a INT
DECLARE @b INT
DECLARE @c INT

--set values
SET @a = 10
SET @b = 5
SELECT @c = @a/@b

--use the calculated expression
SELECT TOP(@c)* FROM toptest

--insert some more data in toptest
INSERT INTO toptest VALUES('Niels6')
INSERT INTO toptest VALUES('Niels7')
INSERT INTO toptest VALUES('Niels8')

--use a SELECT statement as expression
--this should return 5 rows
SELECT TOP(SELECT COUNT(*) FROM toptest2) *
FROM toptest

OUTPUT

--create table and insert data
CREATE TABLE outputtbl
	(id INT IDENTITY, col1 VARCHAR(15))
go

INSERT INTO outputtbl VALUES('row1')
INSERT INTO outputtbl VALUES ('row2')
INSERT INTO outputtbl VALUES ('row5')
INSERT INTO outputtbl VALUES ('row6')
INSERT INTO outputtbl VALUES ('row7')
INSERT INTO outputtbl VALUES ('row8')
INSERT INTO outputtbl VALUES ('row9')
INSERT INTO outputtbl VALUES ('row10')

-- make a table variable to hold the results of the OUTPUT clause
DECLARE @del AS TABLE (deletedId INT, deletedValue VARCHAR(15))

--delete two rows and return through
--the output clause
DELETE outputtbl
OUTPUT DELETED.id, DELETED.col1 INTO @del
WHERE id < 3

SELECT * FROM @del
GO

deletedId		deletedValue
----------	------------
1		row1
2		row2
(2 row(s) affected)

--update records, this populates
--both the inserted and deleted tables
DECLARE @changes TABLE
	(id INT, oldValue VARCHAR(15), newValue VARCHAR(15))

UPDATE outputtbl
SET col1 = 'updated'
OUTPUT inserted.id, deleted.col1, inserted.col1
INTO @changes
WHERE id < 5

SELECT * FROM @changes
GO


id		oldValue		newValue
-----------	---------------	---------------
3		row5		updated
4		row6		updated
(2 row(s) affected)

Common Table Expressions and Recursive Queries

[WITH <common_table_expression> [,...n] ]
<common_table_expression>::=
expression_name
[(column_name [,...n])]
AS
(<CTE_query_expression>)

WITH MathConst(PI, Avogadro)
AS
(SELECT 3.14159, 6.022e23)
SELECT * FROM MathConst
GO

PI		Avogadro
---------------	----------------------
3.14159		6.022E+23
(1 row(s) affected)

WITH MathConst(PI, Avogadro)
AS
(SELECT 3.14159, 6.022e23),
-- second table
Package(Length, Width)
AS (SELECT 2, 5)
SELECT * FROM MathConst, Package


PI		Avogadro			Length		Width
-----------------	----------------------	-----------	----
3.14159		6.022E+23			2		5
(1 row(s) affected)

SELECT DISTINCT SH.SalesPersonId FROM SalesOrderHeader SH JOIN
SalesOrderDetail SD ON SH.SalesOrderId = SD.SalesOrderId
AND SD.ProductID = 90
SalesPersonId
GO

SalesPersonId
------------------
14
21
22
more rows
(14 row(s) affected)

Action		SalesPersonID	SalesQuota		Value
--------		-------------	 ------------------	 ----------
MakeCall		22		250000.0000		2332.7784
... more lines
Relax		35		250000.0000		0

SELECT 'MakeCall' AS Action, S.SalesPersonID, S.SalesQuota,
(SELECT SUM(SD.UnitPrice * SD.OrderQty) FROM SalesOrderHeader SH
JOIN SalesOrderDetail SD ON
SH.SalesOrderId = SD.SalesOrderId
AND SD.ProductID=90 AND SH.SalesPersonID=S.SalesPersonID
)
FROM SalesPerson S
WHERE EXISTS
(
SELECT * FROM SalesOrderHeader SH JOIN SalesOrderDetail SD ON
SH.SalesOrderID = SD.SalesOrderID AND SD.ProductID = 90
AND SH.SalesPersonID = S.SalesPersonID
)
UNION
SELECT 'Relax' AS Action, S.SalesPersonID, S.SalesQuota, 0
FROM SalesPerson S
WHERE NOT EXISTS
(
SELECT * FROM SalesOrderHeader SH JOIN SalesOrderDetail SD ON
SH.SalesOrderID = SD.SalesOrderID AND SD.ProductID = 90
AND SH.SalesPersonID = S.SalesPersonID
)

WITH Missing(SP, AMT)
AS(
SELECT SH.SalesPersonID, SUM(SD.UnitPrice * SD.OrderQty) FROM
SalesOrderHeader SH
JOIN SalesOrderDetail SD ON SH.SalesOrderId = SD.SalesOrderId
AND SD.ProductID=90 GROUP BY SH.SalesPersonID
)
SELECT 'MakeCall' AS Action, S.SalesPersonID, S.SalesQuota,
Missing.AMT
FROM Missing JOIN SalesPerson S ON Missing.SP = S.SalesPersonID
UNION
SELECT 'Relax' AS Action, S.SalesPersonID, S.SalesQuota, 0
FROM SalesPerson S WHERE S.SalesPersonID NOT IN (SELECT SP FROM
Missing)

CREATE TABLE DetailAccount(id INT PRIMARY KEY, parent INT, balance FLOAT)

CREATE TABLE RollupAccount(id INT PRIMARY KEY, parent INT)

INSERT INTO DetailAccount VALUES (3001, 2001, 10)
INSERT INTO DetailAccount VALUES(4001, 3002, 12)
INSERT INTO DetailAccount VALUES(4002, 3002, 14)
INSERT INTO DetailAccount VALUES(3004, 2002, 17)
INSERT INTO DetailAccount VALUES(3005, 2002, 10)
INSERT INTO DetailAccount VALUES(3006, 2002, 25)
INSERT INTO DetailAccount VALUES(3007, 2003, 7)
INSERT INTO DetailAccount VALUES(3008, 2003, 9)

INSERT INTO RollupAccount VALUES(3002, 2001)
INSERT INTO RollupAccount VALUES(2001, 1000)
INSERT INTO RollupAccount VALUES(2002, 1000)
INSERT INTO RollupAccount VALUES(2003, 1000)
INSERT INTO RollupAccount VALUES(1000, 0)

SELECT id, balance FROM Rollup -- a handy view

SELECT id, balance FROM Rollup WHERE id = 2001

id		balance
----------	---------
2001		36

(1 row(s) affected)

WITH Rollup(id, parent)
AS
(
	-- anchor
	SELECT id, parent FROM RollupAccount WHERE id = 1000
	UNION ALL
	-- recursive call
	SELECT R1.id, R1.parent FROM
	(
		SELECT id, parent FROM DetailAccount
		UNION ALL
		SELECT id, parent FROM RollupAccount
	) R1
	JOIN Rollup R2 ON R2.id = r1.parent
	)
-- selecting results
SELECT id, parent FROM Rollup
GO

WITH Rollup(id, parent, balance)
AS
(
	-- anchor
	SELECT id, parent, balance FROM DetailAccount
	UNION ALL
	-- recursive call
	SELECT R1.id, R1.parent, R2.balance
	FROM RollupAccount R1
	JOIN Rollup R2 ON R1.id = R2.parent
)

SELECT id, SUM(balance) balance FROM Rollup GROUP BY id
GO

id		balance
-----------	----------------------
2001		14
2001		12
2001		10

CREATE VIEW Rollup
AS
WITH Rollup(id, parent, balance)
AS
(
	SELECT id, parent, balance FROM DetailAccount
	UNION ALL
	SELECT R1.id, R1.parent, R2.balance
	FROM RollupAccount R1
	JOIN Rollup R2 ON R1.id = R2.parent
)

SELECT id, SUM(balance) balance FROM Rollup GROUP ID id
GO

-- get the balance for account 2001
SELECT balance FROM rollup WHERE id = 2001
GO


balance
----------------------
36
(1 row(s) affected)

WITH Rollup(id, parent, balance)
AS
(
	-- body of CTE removed for clarity
)

SELECT id, SUM(balance) balance FROM Rollup GROUP BY id
OPTION (MAXRECURSION 10)
GO

APPLY Operators

CREATE TABLE T1
(
	ID int
)

CREATE TABLE T2
(
	ID it
)
GO

INSERT INTO T1 VALUES (1)
INSERT INTO T1 VALUES (2)
INSERT INTO T2 VALUES (3)
INSERT INTO T2 VALUES (4)
GO

SELECT COUNT(*) FROM T1 CROSS APPLY T2

-----------------
4

CREATE TABLE Belt
(
	model VARCHAR(20),
	length FLOAT
)
GO

-- fill table with some data
DECLARE @index INT
SET @index = 5
WHILE(@index > 0)
	BEGIN
		INSERT INTO BELT VALUES ('B' + CONVERT(VARCHAR, @index), 10 * @index)
		SET @index = @index – 1
	END
GO

-- make a table-valued function
CREATE FUNCTION Stretch (@length FLOAT)
RETURN @T TABLE
(
	MinLength FLOAT,
	MaxLength FLOAT
)
AS BEGIN
	IF (@length > 20)
	INSERT @T VALUES (@length – 4, @length + 5)
	RETURN
END
GO

SELECT B.* S.MinLength, S.MaxLength FROM Belt AS B
CROSS APPLY Stretch(B.Length) AS S
GO

------------------------
B30, 26, 35
B40, 36, 45
B50, 46, 55	

SELECT B.* S.MinLength, S.MaxLength FROM Belt AS B
CROSS APPLY Stretch(B.Length) AS S
GO
------------------------
B10, 6, 15
B20, 16, 25
B30, 26, 35
B40, 36, 45
B50, 46, 55

PIVOT Command

Using PIVOT for Analysis

CREATE TABLE SALES
(
	[Year] INT,
	Quarter CHAR(2),
	Amount FLOAT
)
GO

INSERT INTO SALES VALUES (2001, 'Q2', 70)
INSERT INTO SALES VALUES (2001, 'Q3', 55)
INSERT INTO SALES VALUES (2001, 'Q3', 110)
INSERT INTO SALES VALUES (2001, 'Q4', 90)
INSERT INTO SALES VALUES (2002, 'Q1', 200)
INSERT INTO SALES VALUES (2002, 'Q2', 150)
INSERT INTO SALES VALUES (2002, 'Q2', 40)
INSERT INTO SALES VALUES (2002, 'Q2', 60)
INSERT INTO SALES VALUES (2002, 'Q3', 120)
INSERT INTO SALES VALUES (2002, 'Q3', 110)
INSERT INTO SALES VALUES (2002, 'Q4', 180)
GO

SELECT * FROM SALES
PIVOT
(SUM (Amount) -- Aggregate the Amount column using SUM
FOR [Quarter] -- Pivot the Quarter column into column headings
IN (Q1, Q2, Q3, Q4)) -- use these quarters
AS P
GO

SELECT * FROM SALES
PIVOT
(SUM (Amount)
FOR [Quarter]
IN (Q2))
AS P
GO


Year		Q2
-----------	----------------------
2001		190
2002		250

CREATE TABLE SALES2
(
	[Year] INT,
	Quarter CHAR(2),
	Amount FLOAT,
	Other INT
)

INSERT INTO SALES2 VALUES (2001, 'Q2', 70, 1)
INSERT INTO SALES2 VALUES (2001, 'Q3', 55, 1)
INSERT INTO SALES2 VALUES (2001, 'Q3', 110, 2)
INSERT INTO SALES2 VALUES (2001, 'Q4', 90, 1)
INSERT INTO SALES2 VALUES (2002, 'Q1', 200, 1)
INSERT INTO SALES2 VALUES (2002, 'Q2', 150, 1)
INSERT INTO SALES2 VALUES (2002, 'Q2', 40, 1)
INSERT INTO SALES2 VALUES (2002, 'Q2', 60, 1)
INSERT INTO SALES2 VALUES (2002, 'Q3', 120, 1)
INSERT INTO SALES2 VALUES (2002, 'Q3', 110, 1)
INSERT INTO SALES2 VALUES (2002, 'Q4', 180, 1)

SELECT * FROM Sales2
PIVOT
	(SUM (Amount)
	FOR Quarter
	IN (Q3))
	AS P
GO


Year		Other		Q3
-----------	-----------	----------------------
2001		1		55
2002		1		115
2001		2		110

SELECT * FROM
	(Select Amount, Quarter, Year from Sales2) AS A
	PIVOT
	(SUM (Amount)
	FOR Quarter
	IN (Q3))
	AS P
GO

Year		Q3
-----------	----------------------
2001		165
2002		230

SELECT * FROM SALES
PIVOT
(SUM (Amount)
FOR [Quarter]
IN (Q2, LastQ))
As P
GO


Year		Q2		LastQ
-------		--------------	----------------------
2001		190		NULL
2002		250		NULL

Using PIVOT for Open Schemas

Ranking and Windowing Functions

SELECT orderid, customerid,
ROW_NUMBER() OVER(ORDER BY orderid) AS num
FROM orders
WHERE orderid < 10400
AND customerid <= 'BN'

SELECT orderid, customerid,
ROW_NUMBER() OVER(ORDER BY customerid) AS num
FROM orders
WHERE orderid < 10400
AND customerid <= 'BN'

SELECT orderid, customerid,
RANK() OVER(ORDER BY customerid) AS [rank]
FROM orders
WHERE orderid < 10400
AND customerid <= 'BN'

SELECT orderid, customerid,
ROW_NUMBER() OVER(ORDER BY customerid) AS num,
RANK() OVER(ORDER BY customerid) AS [rank],
DENSE_RANK() OVER(ORDER BY customerid) AS [denserank],
NTILE(5) OVER(ORDER BY customerid) AS ntile5
FROM orders
WHERE orderid < 10400
AND customerid <= 'BN'

SELECT *,
RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank]
FROM
(SELECT lastname, country,
DATEDIFF(yy,birthdate,getdate())AS age
FROM employees
) AS a

SELECT lastname, country,
DATEDIFF(yy,birthdate,getdate())AS age,
RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank]
FROM employees

-- 10 rows to a page, we want page 40
-- this won't work
SELECT
ROW_NUMBER() OVER (ORDER BY customerid, requireddate) AS num,
customerid, requireddate, orderid
FROM orders
WHERE num BETWEEN 400 AND 410

-- this will
SELECT * FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY customerid, requireddate) AS num,
customerid, requireddate, orderid
FROM orders
) AS a
WHERE num BETWEEN 400 AND 410

SELECT *,
RANK() OVER(PARTITION BY COUNTRY ORDER BY age)) AS [rank]
FROM
(
SELECT lastname, country,
DATEDIFF(yy,birthdate,getdate())AS age
FROM employees
) AS a
ORDER BY RANK() OVER(PARTITION BY COUNTRY ORDER BY age), COUNTRY

-- there is one oldest employee age for each country
SELECT *,
RANK() OVER(PARTITION BY COUNTRY ORDER BY age) AS [rank],
MAX(age) OVER(PARTITION BY COUNTRY) AS [oldest age in country]
FROM
(
SELECT lastname, country,
DATEDIFF(yy,birthdate,getdate())AS age
FROM employees
) AS a

Transaction Abort Handling

BEGIN TRY
{ sql_statement | statement_block }
END TRY

BEGIN CATCH TRAN_ABORT
{ sql_statement | statement_block }
END CATCH

--make sure we catch all errors
SET XACT_ABORT ON
BEGIN TRY
--start the tran
BEGIN TRAN
--do something here
COMMIT TRAN
END TRY
BEGIN CATCH TRAN_ABORT
ROLLBACK
--cleanup code
END CATCH

Transaction is doomed and cannot make forward progress. Rollback
Transaction.

Where Are We?