Sheppy Technical Blog: 2008

Select Title From Employees

/*
rank()

Returns the rank of each row within the partition of a result set.
The rank of a row is one plus the number of ranks that come before the row in question.
*/

Select
Rank() OVER (ORDER BY Title) As [Rank]
,Title
From
Employees

/*
dense_rank()

Returns the rank of rows within the partition of a result set, WITHOUT ANY GAPS in the ranking.
The rank of a row is one plus the number of distinct ranks that come before the row in question.
*/

Select
Dense_Rank() OVER (ORDER BY Title) As [DenseRank]
,Title
From
Employees

/*
row_number()

Returns the sequential number of a row within a partition of a result set,
starting at 1 for the first row in each partition.
*/

Select
Row_Number() OVER (ORDER BY Title) As [RowNumber]
,Title
From
Employees

/*
row_number (PARTION BY)

Divides the result set produced by the FROM clause into partitions
to which the ROW_NUMBER function is applied.
*/

Select
Row_Number() OVER (PARTITION BY Title ORDER BY Title) As [RowNumber]
,Title
From
Employees

/*
derived table vs common table expression
*/

Select * From
(Select
FirstName
,LastName
From Employees) As [x];

-- common table expression

With [x] As
(Select
FirstName
,LastName
From Employees)
Select * From [x];

-- reuse of cte

With [x] As
(Select
FirstName
,LastName
From Employees)
Select * From [x]
Union All
Select * From [x]

-- a cte can reference itself

With [x] As
(Select
[e].EmployeeId
,[e].ReportsTo
,[e].Title
,0 As [Level]
From
Employees As [e]
Where
[e].ReportsTo is null
Union All
Select
[e].EmployeeId
,[e].ReportsTo
,[e].Title
,[x].Level + 1
From
Employees As [e] JOIN [x] ON [e].ReportsTo = [x].EmployeeId)
Select * From [x]
Order By ReportsTo

-- can cause infinate loop / limit recursion

OPTION (MAXRECURSION 2);

-- more than one common table

With [x] As
(Select
CustomerId
,ContactName
From Customers)
,[y] As
(Select
CustomerId
,Max(OrderDate) As [LastOrder]
From Orders
Group By CustomerId)
Select TOP (10)
[x].ContactName
,[y].LastOrder
From
[x] JOIN [y] ON [x].CustomerId = [y].CustomerId

-- named columns

With [x] (LastName, FirstName, Title) As
(Select
LastName As [Name]
,FirstName As [Name]
,Title
From Employees)
Select
[x].*
From
[x]

-- cte to access calculations

With [x] As
(
Select
Row_Number() OVER (ORDER BY Title) As [RowNumber]
,Title
From
Employees
)
Select
[x].[RowNumber]
,[x].Title
,1 As [Page]
From
[x]
Where
[RowNumber] between 1 and 3
Union
Select
[x].[RowNumber]
,[x].Title
,2 As [Page]
From
[x]
Where
[RowNumber] between 3 and 5

/*
NTile()

Distributes the rows in an ordered partition into a specified number of groups.
The groups are numbered, starting at one. For each row, NTILE returns the number
of the group to which the row belongs.
*/

Select
NTile(4) OVER (ORDER BY ContactName) [Tile]
,ContactName
From
Customers
Where
ContactTitle = 'Owner'

-- use NTile for "percentile" - fills one tile at a time, one record at a time

Select
NTile(10) OVER (ORDER BY ContactName) [Tile]
,ContactName
From
Customers
Where
ContactTitle = 'Owner'

-- ntile is based on full result set

Select TOP (20)
NTile(10) OVER (ORDER BY ContactName) [Tile]
,ContactName
From
Customers

TOP Insert, Update, Delete

SET NOCOUNT ON

CREATE TABLE [t] (id uniqueidentifier)
Declare @i int; Set @i = 0
While @i < i =" @i">

SET NOCOUNT OFF

-- delete 10 when 10+ is possible

Delete Top (10) From [t];
Select Count(*) From [t]

-- update 10 when 10+ is possible

Update Top (10) [t]
Set id = null;
Select * From [t]

-- insert 10 when 10+ is possible

Insert Top (10) [t]
Select id From [t] where id is not null;
Select * From [t]

DROP TABLE [t]

TOP Select (simple)

Select Top (15)
ProductName
,UnitPrice
From
Products

TOP PERCENT Select WITH TIES

Note: WITH TIES makes sure that any products with price equal to the lowest price
returned are also included in the result set, even if doing this exceeds 10 percent

SET NOCOUNT ON

-- how many show be 10 percent?

Select Convert(real, Count(*))/Convert(real, 10) From Products

-- "tie" to NTILE calculations

Select Top (10) PERCENT
ProductName
,UnitPrice
From
Products
Order By
UnitPrice Desc

Select Top (10) PERCENT WITH TIES
ProductName
,UnitPrice
From
Products
Order By
UnitPrice Desc

SET ROWCOUNT

Declare @i int; Set @i = 5
SET ROWCOUNT @i

-- limit returned rows

Select
ProductName
,UnitPrice
From
Products

SET ROWCOUNT 0

TOP Expressions

Declare @i int; Set @i = 5

-- use a variable

Select TOP (@i)
ProductName
,UnitPrice
From
Products

-- use an equation

Select TOP (10 - 4)
ProductName
,UnitPrice
From
Products

OUTPUT Clause

SET NOCOUNT ON

CREATE TABLE [t] (id uniqueidentifier, name varchar(25))

Insert Into [t]
Select NewId(), 'Jerry Nixon'

Select * From [t]

Select * From [t] Where name = 'Jerry Nixon'

-- cannot use identity

Insert Into [t]
Select NewId(), 'Jerry Nixon'
Select Scope_Identity()
Select @@identity

-- output directly

Insert Into [t]
OUTPUT INSERTED.id As [NewId]
Select NewId(), 'Jerry Nixon'

-- output to a table

Declare @Output Table(id uniqueidentifier)
Insert Into [t]
OUTPUT INSERTED.id Into @Output
Select NewId(), 'Jerry Nixon'
Select * from @Output

-- with Delete

Delete Top (5) from [t]
OUTPUT DELETED.id As [OldId]

-- with Update

Update Top (5) [t]
Set id = NewId()
OUTPUT DELETED.id As [OldId], INSERTED.id As [NewId]

DROP TABLE [t]

-- unreliable subquery, order by potentially ignored

SELECT TOP 99 PERCENT * FROM Customers
ORDER BY CompanyName

SELECT * FROM
(SELECT TOP 99 PERCENT * FROM Customers ORDER BY CompanyName) AS SubTable

Table-Valued User-Defined-Functions

CREATE FUNCTION MyTableFunction (@id int)
RETURNS @Return Table(EmployeeId int, Title varchar(25))
Begin
Insert Into @Return
Select
EmployeeId
,Title
From Employees
Where
EmployeeId = IsNull(@id, EmployeeId);
Return;
End

-- simple use (no dbo prefix)

Select * From MyTableFunction(null)

Select * From MyTableFunction(1)

-- fails with join

Select
[e].EmployeeId
,[f].Title As [TitleFromFunction]
,[e].FirstName
,[e].LastName
From
Employees [e]
JOIN MyTableFunction([e].EmployeeId) As [f] ON [f].EmployeeId = [e].EmployeeId

-- success with apply

Select
[e].EmployeeId
,[f].Title As [TitleFromFunction]
,[e].FirstName
,[e].LastName
From
Employees As [e]
CROSS APPLY MyTableFunction([e].EmployeeId) As [f]

DROP FUNCTION MyTableFunction

/*
.WRITE in the update clause like substring
*/

CREATE TABLE TempDemoTable (BigCol varchar(max))
Insert into TempDemoTable (BigCol)
Select 'Now is the time for all good men to come to the aid of their country';
Select * from TempDemoTable;

-- note: partial updates to large value data types using the .WRITE clause are minimally logged

Update TempDemoTable
Set BigCol .WRITE ('women', 29, 3);
Select * from TempDemoTable;

-- length is null

Update TempDemoTable
Set BigCol .WRITE ('women', 29, null);
Select * from TempDemoTable;

-- expression is null

Update TempDemoTable
Set BigCol .WRITE (null, 29, 3);
Select * from TempDemoTable;

-- offset is null

Update TempDemoTable
Set BigCol .WRITE (getdate(), null, null);
Select * from TempDemoTable;

DROP TABLE TempDemoTable

PIVOT

SET NOCOUNT ON

CREATE TABLE [t] (Teacher varchar(25), Student varchar(25), Grade int, Gender char(1))

Insert Into [t] Select 'Ms. Smith', 'Johnny', 2, 'm';
Insert Into [t] Select 'Ms. Smith', 'Jimmy', 1, 'm';
Insert Into [t] Select 'Ms. Smith', 'Sally', 4, 'f';
Insert Into [t] Select 'Ms. Smith', 'Susan', 4, 'f';
Insert Into [t] Select 'Ms. Smith', 'Martin', 2, 'm';
Insert Into [t] Select 'Ms. Smith', 'Mary', 3, 'f';
Insert Into [t] Select 'Ms. Jones', 'Anna', 3, 'f';
Insert Into [t] Select 'Ms. Jones', 'Jenny', 3, 'f';
Insert Into [t] Select 'Ms. Jones', 'Waldo', 4, 'm';
Insert Into [t] Select 'Ms. Jones', 'Karl', 2, 'm';
Insert Into [t] Select 'Ms. Jones', 'Lucy', 4, 'f';
Insert Into [t] Select 'Ms. Smith', 'Kelly', 3, 'f';

print 'Raw data'

Select Teacher, Student, Grade, Gender from [t]

print 'Average grade by teacher'

Select
Teacher
,Avg(Convert(decimal, Grade)) As [Average Grade]
From [t]
Group By Teacher

print 'Classroom size by teacher'

Select
Teacher
,Count(Student) As [Classroom Size]
From [t]
Group By Teacher

print 'Grade distribution by teacher'

Select Teacher, [1] As [D], [2] As [C], [3] As [B], [4] As [A] From
(Select Teacher, Grade From [t]) As Data
Pivot (Count(Grade) For grade in ([1], [2], [3], [4])) As PivotTable
Order by Teacher

print 'Gender distribution'

Select Gender, [Ms. Smith] As [Ms. Smith], [Ms. Jones] As [Ms. Jones] From
(Select Teacher, Grade, Case Gender When 'f' Then 'Girls' When 'm' Then 'Boys' End As Gender From [t]) As Data
Pivot (Count(grade) For teacher in ([Ms. Smith], [Ms. Jones])) As PivotTable
Order by Gender

print 'Average gender grade '

Select Teacher, [m] As [Boys], [f] As [Girls] From
(Select Teacher, Convert(decimal, Grade) Grade, Gender From [t]) As Data
Pivot (Avg(grade) For gender in ([m], [f])) As PivotTable
Order by Teacher

print 'Grade distribution by gender'

Select Gender, [1] As [D], [2] As [C], [3] As [B], [4] As [A] From
(Select Teacher, Grade, Case Gender When 'f' Then 'Girls' When 'm' Then 'Boys' End As Gender From [t]) As Data
Pivot (Count(Teacher) For grade in ([1], [2], [3], [4])) As PivotTable
Order by Gender

DROP TABLE [t]

/*
FAST hint
*/

SELECT * FROM Customers
OPTION(FAST 10)

Error Handling

-- old school, could not prevent exception display

select 4/0
if (@@error = 8134)
print 'Error Encountered'
print 'Execution Continues'

-- try catch

begin try
select 4/0
print 'Execution Halts'
end try
begin catch
print @@error
print error_message()
print ' error caught'
end catch

-- compile error

BEGIN TRY
PRINT 'Inside Try-Block'
SELECT ** FROM T /* will cause syntax error */
END TRY
BEGIN CATCH
print @@error
print error_message()
print ' error caught'
END CATCH

-- "lower" error (compile not until execution)

BEGIN TRY
PRINT 'Inside Try-Block'
EXEC ('SELECT ** FROM T ') /* compile error in the lower scope */
END TRY
BEGIN CATCH
print @@error
print error_message()
print ' error caught'
END CATCH

Sheppy Technical Blog

Pages

Thursday, March 6, 2008

New SQL 2005 T-SQL