Making the Leap into Advanced T-SQL

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@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 FROMsys.objectsas so INNERJOINsys.columnsas sc on sc.object_id= so.object_id WHERE so.type ='U' GROUPBY so.name, sc.user_type_id HAVINGCOUNT(*)> 1 1 FROMsys.objectsas so INNERJOINsys.columnsas sc onsc.object_id=so.object_id 2 WHEREso.type='U' 3 GROUPBY so.name, sc.user_type_id 4 HAVINGCOUNT(*)> 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 JOINsys.columnsas sc onsc.object_id=so.object_id WHEREsc.user_type_id=167 GROUP BY so.name, sc.user_type_id HAVING COUNT(*) > 1 SELECT so.name, sc.user_type_id FROM sys.objects as so LEFT OUTER JOINsys.columnsas sc onsc.object_id=so.object_id AND sc.user_type_id=167 GROUP BY so.name, sc.user_type_id HAVING COUNT(*) > 1

9. Set Practice – Logical – Sub Queries SELECT so.name, sc.user_type_id,column_cnt=(SELECTCOUNT(*) FROMsys.columnsas 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(*) > 1 5 SELECT so.name, sc.user_type_id,column_cnt=(SELECTCOUNT(*) FROMsys.columnsas 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 how’s it work? What is a Derived Table and how’s it work? What is a Temporary Table and how’s it work?

11. Derived Table - Basics • Example -> 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 • <=2000 UDF’s are executed multiple times on certain query constructions – beware! • No stats held because it isn’t really there (expanded into main query!)

13. Common Table Expression • Example -> 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 -> 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 @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 @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/tonyrogerson • 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) createfunction dbo.fn_check_sales_commission_window ( @sales_commission_id int, @individual_id int, @comm_start_date smalldatetime, @comm_end_date smalldatetime) returnsvarchar(3) as begin declare @status varchar(3) ifexists( select* from sales_commission where individual_id = @individual_id and id <> @sales_commission_id and( @comm_start_date between comm_start_date andcoalesce( comm_end_date, @comm_start_date ) or @comm_end_date between comm_start_date andcoalesce( comm_end_date, @comm_end_date )) ) set @status ='BAD' else set @status ='OK' return @status end createtablesales_commission( id intnotnullidentityconstraintpk_sales_commissionprimarykeyclustered, individual_idintnotnull, comm_ratedecimal( 5, 2 )notnull, comm_start_datesmalldatetimenotnull check(comm_start_date=cast(convert(char(8),comm_start_date, 112 )assmalldatetime)) constraintck_sales_commission_window check(dbo.fn_check_sales_commission_window( id,individual_id, comm_start_date,comm_end_date)='OK'), comm_end_datesmalldatetimenull check(comm_end_dateisnull orcomm_end_date=cast(convert(char(8),comm_end_date, 112 )assmalldatetime)), constraintuk_sales_commissionunique(individual_id,comm_start_date),)

21. First tip – History Validation(Preventing Overlapping Dates) createtable sales_commission ( id intnotnullidentityconstraint pk_sales_commission primarykeyclustered, individual_id intnotnull, comm_rate decimal( 5, 2 )notnull, comm_start_date smalldatetimenotnull check( comm_start_date =cast(convert(char(8), comm_start_date, 112 )assmalldatetime)) comm_end_date smalldatetimenull check( comm_end_date isnull or comm_end_date =cast(convert(char(8), comm_end_date, 112 )assmalldatetime)) ) go