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 < 100
Begin
	Insert Into [t] Select NewId()
	Set @i = @i + 1 
End
Select Count(*) From [t]

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