SQL Querying Tips & Techniques

SQL Querying Tips & Techniques Richard Campbell

Who Am I? • Consultant in development methodology and high scaling systems • Microsoft Regional Director • Partner in PWOP Productions • Author of Advisor Answers • www.campbellassociates.ca

PWOP Productions • .NET Rocks! • Internet Audio Talkshow for .NET Developers • www.dotnetrocks.com • dnrTV • Screencast (see the code!) • www.dnrtv.com

Agenda • Subqueries & CTEs • Self-Joins & Recursion • Ranking Functions • The Crosstab Problem • Deadlocks Tricks

Advanced Queries • No teaching the basics here • We presume you already understand SELECT, JOIN, WHERE, GROUP BY, ORDER BY… • And now you’re looking for more

Subqueries • Can act much like a join • More efficient at finding general matches • Good for comparing a set to a set • Locating ranged values • Finding duplicates

Common Table Expressions • Uses ANSI syntax and semantics • Very similar to a view, but embedded in the query • May be used in front of SELECT, UPDATE, DELETE, INSERT

Self-Joins • Joining a Table to Itself • Classic scenario: the organizational chart • Each employee record has an employee_ID and a ReportsTo_ID that points to another employee • There can be one (and only one) report path

Recursive Queries • This is the main reason MSFT introduced the CTEs • Recursion is achieved by allowing the CTE refer to itself • Such CTE must have special syntax • WITH REC_CTE as • (SELECT1 UNION ALL SELECT2)

Ranking Queries • Self-joins can be used to rank data • Join on a >= expression • Use COUNT(*) to set rank • When two rows are ranked the same, they both get the greater value

Ranking Functions • SQL Server adds 4 ranking functions: • Row_Number() • RANK() • DENSE_RANK() • nTile(n) • Ability to partition and “window” these functions • Ability to perform aggregate operations over the ranking functions • Can be used with User Defined Functions

The Crosstab Problem • What’s a Crosstab? • Turning rows into columns • For example: • Sales by Product Per Month • Sales by Product Per Salesperson • Etc…

The Rozenshtein Method • Taken from Optimizing Transact-SQL: Advanced Programming Techniques • Uses boolean aggregates • Each column has a numeric expression that resolves each row as a zero or one

PIVOT and UNPIVOT • PIVOT • Transforms a set of rows to columns • Similar to Access TRANSFORM • Useful for open schemas/OLAP scenarios • UNPIVOT • Reverse operation of PIVOT

Deadlocks • What is a deadlock? • Connection A locks table 1, needs table 2 • Connection B locks table 2, needs table 1 • Someone has to lose

Deadlocks • Solution: • Don’t ever do that! • Always update tables in the same order • Plan an order of updates, always follow it • Do your updates in stored procedures • Even then, sometimes you’ll get deadlocks • (busy databases can be funny things)

Questions?

Please fill out the survey forms!They are the key to amazing prizes that you can get at the end of each day Thank you!

SQL Querying Tips & Techniques