1 / 19

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!

kylia
Télécharger la présentation

SQL Querying Tips & Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Querying Tips & Techniques Richard Campbell

  2. 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

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

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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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)

  10. 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

  11. 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

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

  13. 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

  14. 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

  15. 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

  16. 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)

  17. Questions?

  18. 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!

More Related