Topic(s): The Running Total Problem and Other "Previous Row" Calculations and Groupings
Creating the Million Row Test Table
Overview: The ROW_NUMBER() and RANK() "windowing functions" work great but what if you are still using SQL Server 2000? What if you need to number groups of rows in a particular order or sequence that even ROW_NUMBER() won't handle? What if you need to "copy data down" to the next row or do something to the next row based on the previous row like a Running Total, Grouped Running Total, or a 5 day average sliding window? Even SQL Server 2008 doesn't have functions like that! So what do you do? Use a RBAR Cursor or While Loop? Not on your life. It's too slow! This Developer-to-Developer session demonstrates a unique technique to solve all those problems in a super fast set-based fashion. Some of the "bad" techniques will also be demonstrated so you know what to watch out for.
"A Developer must not guess… a Developer must KNOW!" There are many myths and legends about what makes high performance code. The problem is that most folks demonstrate their "speed" on only 10 or 20 rows. As the number of rows in a table go up, the Execution Plan changes and maybe that code that someone is so proud of doesn't work in the face of scalability. How can you test for such a thing? Easy… build a million row test table to test against. This Developer-to-Developer session demonstrates how to create and control the content of a million row table without buying any 3rd party tools. It's all done in T-SQL and it works in less than 30 seconds. Better than that, the code is only 4 lines long plus 1 line for each desired column and two of those are duplicates which makes this method easy to remember
Speaker: SQL Server MVP Jeff Moden is back with more T-SQL tricks up his sleeve in two more "Developer-to-Developer" presentations. As you may recall, Jeff gave the first ever "Developer-to-Developer" presentation for SEMSSUG last February on the Tally table and how to use it to replace loops. Jeff has several articles and more than 15,000 posts (as he says, "Some are actually useful!" ) on SQLServerCentral.com. This double header session is actually a preview of part of what's to come in the book he's currently for RedGate/Simple Talk publishing and two of the presentation abstracts that he submitted for this year's PASS conference.