SQL Spackle #1

1. SQL Spackle #1 Jeff Moden 19 May 2011

2. About Your Speaker • Mostly Self Trained • Started with SQL Server in 1995 • More than 25,000 posts on SQLServerCentral.com (some are actually useful) • 22 Articles on SQLServerCentral.com • http://www.sqlservercentral.com/Authors/Articles/Jeff_Moden/80567/ • SQL Server MVP since 2008 • Coined the phrase “RBAR” • 2 Sessions at PASS 2010 • SQL Server Saturday #60 – Brunswick, OH

3. Agenda • Introduction to “Pseudo Cursors” • What they are • Declarative Code • Procedural Code • Examples • The Calendar Table • What it is • How to build it • Usage examples

4. Introduction To“Pseudo Cursors”

5. Behind the Scenes… • What IS SQL Server Really? • It’s nothing more than a very sophisticated file system. • What IS a SELECT? • It’s nothing more than “declarative” code that loops through a part of a file identified as a “table”. • It does almost like the code you’d write… find a row, read a row, process a row, loop until done. • It LOOPs in the background so you don’t have to write loops like a front-end Developer.

6. “Declarative” Code • You tell it what you want done • SQL Server figures out how to do it. • “Set Based” code is where you allow or even help SQL Server do the best job that it can do. • It works with more than one row at a time or in “sets” of rows.

7. “Procedural” Code • Easy name to remember because you tell SQL Server how to “proceed” every step of the way. • Consists of explicit Loops • Consists of “hidden” RBAR such as recursive CTE’s and recursive functions. • Is usually quite slow because SQL Server can’t do what it’s supposed to

8. “Pseudo Cursor”Examples

9. The“Calendar” Table

10. What A Calendar Table Is • A “Helper” Table • Auxiliary Table of Dates • A special kind of “Pseudo Cursor” • Pre-populated with date related info • Use to replace Cursors, While loops, and other complicated code.

11. Building the“Calendar” Table

12. “Calendar” TableCode Examples

13. Grouping “Islands of Dates”