Dell Webinar 2014-06-24: Subqueries For Superheroes
Download as PPTX, PDF0 likes616 views
ºÝºÝߣ deck from my Subqueries For Superheroes webinar, presented for Dell Software as part of their Experts Perspective series.
More Related Content
Viewers also liked (10)
Similar to Dell Webinar 2014-06-24: Subqueries For Superheroes (20)
Dell Webinar 2014-06-24: Subqueries For Superheroes
- 1. Experts¡¯ Perspective: Subqueries for Superheroes Tracy McKibben DBA Supervisor, Pearson VUE
- 2. 2 Dell Software Your host ? Richard Douglas ? Sales Engineer / SQL Evangelist ? Maidenhead SQL User Group Leader ? Blog: http://SQL.RichardDouglas.co.uk ? Twitter: @SQLRich ? Email: Richard.Douglas@Software.Dell.com
- 3. 3 Dell Software Tracy McKibben DBA Supervisor, Senior SQL Server DBA Pearson VUE Blog: realsqlguy.com Twitter: @RealSQLGuy I¡¯m not saying I¡¯m Batman, I¡¯m just saying that nobody has ever seen me and Batman in the same room together...
- 4. 4 Dell Software Today¡¯s Lineup ? What is a subquery? ? Correlated vs non-correlated ? Occupational hazards ? Subqueries in disguise
- 5. 5 Dell Software What is a subquery? A query wrapped within, embedded within, or otherwise referenced by another query. Also known as an INNER query. The lowly sidekicks of the SQL world.
- 6. 6 Dell Software Anatomy of a subquery Which of these is a valid place to put a subquery? SELECT ? FROM ? INNER JOIN ? ON ? WHERE ? GROUP BY ? HAVING ?
- 7. 7 Dell Software Anatomy of a subquery SELECT <a subquery can go here> FROM <or here> INNER JOIN <or here> ON <or here> WHERE <or here> GROUP BY <or here> HAVING <or here> It¡¯s, like, the ultimate superpower!
- 8. 8 Dell Software Is this a subquery?
- 9. 9 Dell Software Is this a subquery?
- 10. 10 Dell Software Is this a subquery?
- 11. 11 Dell Software Is this a subquery?
- 12. 12 Dell Software Is this a subquery?
- 13. 13 Dell Software Is this a subquery?
- 14. 14 Dell Software Is this a subquery?
- 15. 15 Dell Software Is this a subquery?
- 16. 16 Dell Software Exercise Time!
- 17. 17 Dell Software Sometimes loopy A subquery is either correlated or non-correlated. What is the difference?
- 18. 18 Dell Software Sometimes loopy A subquery is either correlated or non-correlated. What is the difference? A correlated subquery depends on the outer query, looping through the outer resultset, executing once for each row in the outer query. A non-correlated subquery stands alone, only running once, independent of the values in the outer query.
- 19. 19 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 20. 20 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 21. 21 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 22. 22 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 23. 23 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 24. 24 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 25. 25 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 26. 26 Dell Software What¡¯s the correlation? Does this subquery stand alone (non-correlated), or does it need help (correlated)?
- 27. 27 Dell Software Exercise Time!
- 28. 28 Dell Software Occupational hazards Subqueries offer a lot of power and flexibility.
- 29. 29 Dell Software Occupational hazards Subqueries offer a lot of power and flexibility. But sometimes, things go terribly wrong
- 30. 30 Dell Software COUNT vs EXISTS Are there any Green Lantern symbols in this collection? How do you know? Did you count them, or did you simply see green and say ¡°yes¡±? Did you do this: WHERE COUNT() > 0? or, did you do this: WHERE EXISTS()?
- 31. 31 Dell Software NOT IN vs NULL Be careful of NULL values returned by inner query or value list in a NOT IN clause. NULL has no value and can produce surprising results. Always returns an empty resultset.
- 32. 32 Dell Software MAX vs RANK MAX/MIN are often used in a subquery to determine the oldest/greatest/most recent row in the outer query. RANK/OVER and other windowing functions are more efficient. or
- 33. 33 Dell Software Exercise Time!
- 34. 34 Dell Software Subqueries In Disguise Some T-SQL constructs behave much like subqueries. ? non-indexed views ? common table expressions (CTE) ? user-defined functions Be wary of performance problems.
- 35. 35 Dell Software Exercise Time!
- 36. 36 Dell Software The conclusion ? correlated vs non-correlated - know the difference and potential impacts ? multiple ways to get the same data, but not all perform well ? know new language features like RANK ? don¡¯t count unless you need a number ? be wary of NULL values and hidden subqueries
- 37. 37 Dell Software Join us at these upcoming SQL events! SQL Community Corner Date Location June 25 PASS DBA Virtual Chapter ¨C ¡°The Day After Tomorrow; Why You Need to Baseline¡± July 12 SQLSaturday #312 - Sacramento 2014 July 17-19 SQLBits XII ¨C Telford, UK July 26 SQLSaturday #302 ¨C Albany 2014
- 38. 38 Dell Software Visit our SQL Community on ToadWorld- www.toadworld.com/platforms/sql-server/default.aspx
- 39. Pg. 39? 2012 Quest Software Inc. All rights reserved. Solution Area Product Description Fast, flexible backup and recovery with industry-leading compression technology Discover and resolve performance issues in production before they impact end users and service levels Deepest possible understanding of database performance and norms Plan and develop applications that deliver both functionality and optimal performance Backup and Recovery Performance & Operations Performance Tuning Development Comprehensive schema, object, security and change management Administration Community crowdsourcing for SQL Server tracing and performance information! Community, Knowledge, Training
- 40. 40 Dell Software
- 41. 41 Dell Software
- 42. 42 Dell Software Questions? Continue the conversation on Twitter via #DellSQL
- 43. Thank You!
Editor's Notes
- #4: Tracy is a senior DBA at Pearson VUE, a syndicated blogger at ToadWorld.com and was recognised as Pro of the month for December 2013. He¡¯s also the Director of Program Development at the Minnesota PASS Chapter Tom Rath¡¯s - StrengthFinder: Strategic Command Deliberative Focus Achiever