Making the Leap into Advanced TSQL

1
Making the Leap into Advanced T-SQL

• Tony Rogerson, SQL Server MVP
• SQL Know How Ltd
• http//sqlblogcasts.com/blogs/tonyrogerson
• http//sqlserverfaq.com
• tonyrogerson_at_torver.net

2
Who Am I

• Developing since 86, IBM mainframe PL/1, CICS,
  DB2, System W, AS Client server since 94 VB,
  VB.NET, C, SQL Server.
• Independent consultant specialising in SQL Server
  since 98
• Founded the UK SQL Server User Group
  (http//sqlserverfaq.com) in 98
• Founded SQLBlogCasts.com
• Founder member of SQLBits Community Conferences
• Partner of SQL Know How a group of leading SQL
  experts providing Training, Consultancy and
  Mentoring.

3
Session Goal

• Better understanding of Set theory
• Utilise and understand
• Derived Tables
• Common Table Expressions
• CASE Expression
• Temporary Tables or Table Variables
• Make you stand back and think before writing your
  SQL

4
Agenda

• Set Theory
• INNER JOIN, OUTER JOIN, CROSS JOIN
• How a SQL Statement is Logically executed
• Table Expression Concepts
• Derived Tables
• Common Table Expressions
• Temporary Table and Table Variables
• Putting Table Expressions to Use
• Aggregations
• Running Totals, Cumulative Totals, Proportions

5
Set Theory

• A little reminder

6
Set Theory Common Joins
INNER JOIN
FULL OUTER
LEFT OUTER
CROSS JOIN
UNIONDistinct rows between the two sets
A
B
UNION ALL All rows between the two sets
For each row in A return each row in B (10 x 10
100)
7
Set Practice Logical Query Construction
SELECT so.name, sc.user_type_id FROM sys.objects
as so INNER JOIN sys.columns as sc on
sc.object_id so.object_id WHERE so.type
'U' GROUP BY so.name, sc.user_type_id HAVING
COUNT() gt 1
FROM sys.objects as so INNER JOIN sys.columns
as sc on sc.object_id so.object_id
1
2
WHERE so.type 'U'
3
GROUP BY so.name, sc.user_type_id
4
HAVING COUNT() gt 1
5
SELECT so.name, sc.user_type_id
8
Set Practice Logical Outer Join Filter
SELECT so.name, sc.user_type_id FROM sys.objects
as so LEFT OUTER JOIN sys.columns as sc on
sc.object_id so.object_id WHERE sc.user_type_id
167 GROUP BY so.name, sc.user_type_id HAVING
COUNT() gt 1
SELECT so.name, sc.user_type_id FROM sys.objects
as so LEFT OUTER JOIN sys.columns as sc on
sc.object_id so.object_id AND
sc.user_type_id 167 GROUP BY so.name,
sc.user_type_id HAVING COUNT() gt 1
9
Set Practice Logical Sub Queries
SELECT so.name, sc.user_type_id, column_cnt (
SELECT COUNT()
FROM sys.columns as sc2
WHERE sc2.user_type_id
sc.user_type_id ) FROM sys.objects as so
INNER JOIN sys.columns as sc on sc.object_id
so.object_id WHERE so.type 'U' GROUP BY
so.name, sc.user_type_id HAVING COUNT() gt 1
5
SELECT so.name, sc.user_type_id, column_cnt (
SELECT COUNT()
FROM sys.columns as sc2
WHERE sc2.user_type_id
sc.user_type_id )
This bit is executed once per row on result set
10
Virtual / Work Table Concepts

• What is a Common Table Expression and hows it
  work?
• What is a Derived Table and hows it work?
• What is a Temporary Table and hows it work?

11
Derived Table - Basics

• Example -gt Intro Derived Table.sql
• Logically a Virtual Table
• Physically expanded into the main query
• Bound to plan no recompilation
• Saves on use of or tables
• No statistics held on result of Virtual Table
• Derived Table self-contained
• Cannot self join

12
Derived Table - Workings

• In 6.5 they used to be materialised as a work
  table
• 7.0 onwards they are expanded into the main query
• lt2000 UDFs are executed multiple times on
  certain query constructions beware!
• No stats held because it isnt really there
  (expanded into main query!)

13
Common Table Expression

• Example -gt Intro Common Table Expression.sql
• Logically a Virtual Table
• Bound to plan no recompilation
• Physically expanded into the main query (to a
  point)
• Saves on use of or tables
• Can self join and can also recursively self join
• I see it as a replacement for the derived table
• MAXRECURSION

14
Temporary Tables / Table Variables

• Example -gt Intro - Temp Table and Table
  Variable.sql
• Physically materialised in tempdb
• Not bound to plan requires re-compilation on
  use
• (local) or (global)
• Statistics are held and can be used by the
  optimiser
• Can create indexes and constraints
• DECLARE _at_tb TABLE
• No statistics
• Only create indexes via UNIQUE or PRIMARY KEY
  constraints but only clustered index if exists
  used

15
Temporary Tables / Table Variables

• Using them causes writes to tempdb
• or Tables
• Causes plan recompilation which causes compile
  locks which hinders scalabilty
• http//support.microsoft.com/kb/263889
• Stats held which a recompile of the query
  statement can lead to a better query plan
• DECLARE _at_Customers TABLE (
• Bound to plan no recompilation required
• Does not cause compile locks
• Can lead to poor general plans because no stats
  held

16
Putting Table Expressions to Use

• Cumulative Aggregates
• Running Totals
• OVER clause instead of Sub-Queries
• Using with BCP and BULK INSERT

17
Links / Useful Info

• My Blog http//sqlblogcasts.com/blogs/tonyrogerso
  n
• UK SQL Server User Group http//sqlserverfaq.com
• My Personal Profile http//www.sql-server.co.uk
• SQLBits http//www.sqlbits.com
• Partner of SQL Know How a group of leading SQL
  experts providing Training, Consultancy and
  Mentoring.

18
Bonus Material
19
Tip History Validation(Preventing Overlapping
Dates)

• Window overlap two comm rates active (we should
  only have one)
• On joining to our transaction table each
  transaction will get each comm rate applied thus
  giving an incorrect result
• Use a trigger or preferably a CHECK constraint
  and UDF!

20
First tip History Validation(Using a UDF in a
CHECK Constraint)
create function dbo.fn_check_sales_commission_wind
ow ( _at_sales_commission_id int,
_at_individual_id int, _at_comm_start_date
smalldatetime, _at_comm_end_date smalldatetime
) returns varchar(3) as begin declare _at_status
varchar(3) if exists ( select
from sales_commission where
individual_id _at_individual_id and id
ltgt _at_sales_commission_id and (
_at_comm_start_date between comm_start_date and
coalesce( comm_end_date, _at_comm_start_date )
or _at_comm_end_date between comm_start_date
and coalesce( comm_end_date, _at_comm_end_date ) )
) set _at_status 'BAD' else
set _at_status 'OK' return _at_status end
create table sales_commission ( id int not
null identity constraint pk_sales_commission
primary key clustered, individual_id int
not null, comm_rate decimal( 5, 2 ) not
null, comm_start_date smalldatetime not null
check( comm_start_date cast( convert(
char(8), comm_start_date, 112 ) as smalldatetime
) ) constraint ck_sales_commission_window
check( dbo.fn_check_sales_commission_window(
id, individual_id, comm_start_date,
comm_end_date ) 'OK' ), comm_end_date
smalldatetime null check( comm_end_date is
null or comm_end_date cast( convert(
char(8), comm_end_date, 112 ) as smalldatetime )
), constraint uk_sales_commission unique
( individual_id, comm_start_date ), )
21
First tip History Validation(Preventing
Overlapping Dates)
create table sales_commission ( id int not
null identity constraint pk_sales_commission
primary key clustered, individual_id int
not null, comm_rate decimal( 5, 2 ) not
null, comm_start_date smalldatetime not null
check( comm_start_date cast( convert(
char(8), comm_start_date, 112 ) as smalldatetime
) ) comm_end_date smalldatetime null
check( comm_end_date is null or
comm_end_date cast( convert( char(8),
comm_end_date, 112 ) as smalldatetime ) ) ) go