New SQL 2005 T-SQL

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

GO

/*
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

GO

/*
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

GO

/*
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

GO

/*
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

GO

/*
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

GO

/*

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]

GO

/*

TOP Select (simple)

*/

Select Top (15)
ProductName
,UnitPrice
From
Products

GO

/*

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

GO

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

GO

/*

SET ROWCOUNT

*/

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

-- limit returned rows

Select
ProductName
,UnitPrice
From
Products

SET ROWCOUNT 0

GO

/*

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

GO

/*

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

GO

/*

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

GO

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

/*

Disable a login
Rename a login

*/

ALTER LOGIN sa DISABLE;
Select * from sys.sql_logins

ALTER LOGIN sa WITH NAME = [sys-admin];
Select * from sys.sql_logins

ALTER LOGIN [sys-admin] WITH NAME = sa;
Select * from sys.sql_logins

ALTER LOGIN sa ENABLE;
Select * from sys.sql_logins

GO