Philly.net Code Camp 2009.2 Saturday October 17, 2009

 

 

Well the 2nd code camp of the year was another big success.  Lots of campers showed up from the largest registration yet, 750.

Thanks to all who showed up first thing in the morning and attended my SQL presentation, Advanced Workflow Processes in T-SQL.  This was my fourth or fifth time presenting at code camp so there were no worries about being a presenter.  However,  this was the first presentation of this kind or should I say magnitude for me. 

Usually, the presentations are on very specific technical aspects of T-SQL such as XML, High Performance Set based Techniques, New Features of SQL 2008.  They tend to be composed of many smaller independent sections.  It would be easy to alter the presentation to a smaller time slot but knocking out a section or two.

The Advanced Workflow Processes in T-SQL presentation contained large conceptual sections combined with technical demonstrations of  T-SQL syntax and followed by a real example.  The goal was to spark new ideas and new ways of thinking about T-SQL software development.

The feedback from attendees was overwhelmingly positive and astoundingly enthusiastic. People were finding me all day to let me know how much they took from it and how they were planning on using the techniques right a way.  As a presenter this is a very gratifying outcome.

One gentleman let me know he was planning on integrating it directly into an existing SSIS package.  Instead of handling all of the logic in SSIS he was just going to have a step that called a stored procedure with a parameter.  All the customizations and variations needed for the different modes of operation were just going to be handled in the stored procedure.  The sproc called from SSIS would look up, in a new table, the custom sproc to execute based on the input and dynamically execute it.  He was thrilled by the idea of never having to touch the SSIS package again to implement new or make modifications to an existing variation of the logic. Cleary the message of the presentation was transmitted.

There were some criticism of the presentation that were also useful.  Believe it or not there are two other demos that were slated for this presentation showing alternate techniques for dealing with the issues and uses of dynamic T-SQL workflows.  If you attended the 1 hour and 20 minute presentation you clearly could see that there is just no place for more.  This leads to one of the most useful criticism, which is paraphrased as this: “There were some really interesting an novel bits of code that we didn’t get to explore enough.  We could have used less of the theory and more of the code.”

I agree with the criticism in hindsight.  However, based on the feedback from the last few presentations there had been a request for deeper and less basic subject matter.  I still believe that there is an important need to study and comprehend the theory, best practices, and conceptual ideas behind software development as it applies to T-SQL.  However,  I need to find another way to address these conceptual pieces. 

The plan, at the moment, for the presentation is to break it up into a series. 

Dynamic T-SQL Execution – detailed look look at the techniques, syntax and quirks of using the EXECUTE command and the sp_executesql stored procedure.

The Magical World of The Temp Table and Other Mythical Temporary Persistence Objects – a deep look at the life and times of temp tables and table variables. A compare and contrast exercise between temp tables and table variables with an added focus on the new table variable parameters in SQL 2008.

The Fine Art of Stored Procedures & User Defined Functions – focusing on best practices and techniques of working with stored procedures.  Specific topics to include:

• Compare & Contrast Sproc vs. UDF
• Return Values vs. Output Parameters
• Parameters
• Default Values and how to ensure them
• Input validation
• Error handling
• Temp table & Cursor best practices.
• UDF types & Limitations
• Capturing table set output of a sproc
• What breaks when you have a linked server involved

Dynamic T-SQL Workflows – This presentation will assume that all the previous presentation topics are well understood. The focus will be squarely on applying all the techniques to enable dynamic, flexible, and easy to modify or variegate work flows in T-SQL.

Data Quality Status Monitoring – A Dynamic T-SQL Workflow case study – The use of the dynamic T-SQL workflow techniques applied to the monitoring of the quality of data in a database. The use of this technique is beneficial in development and production environment.  A basic illustration of the workflow as well as details on specific areas in a db where DRI & Constraints don’t work but should be monitored.

T-SQL Delta Engine for determining changes between two snapshots of data – A Dynamic T-SQL Workflow case study – Say you have two snapshots of data and you need to know what's been added, dropped, or modified between them.  This presentation is a case study of the use of a dynamic T-SQL workflow process to enable custom rules to be applied to a data structure to determine the differences between two sets of data.

Customizable T-SQL ETL Engine – A Dynamic T-SQL Workflow case study – Extraction – Translation – And Loading of data is a bread and butter kind of activity for database developers.  If the source and target of an ETL application is constant then nobody really cares how its coded so long as it works and doesn’t take too long to run.  In reality though how it is implemented becomes very important as soon as it needs to be modified or have a variation created.  This presentation shows how to apply the techniques of dynamic T-SQL workflows to building robust, resilient, and flexible ETL process in T-SQL.

Other Topics

Just for fun while I was creating this list I cam up with a few off topic subjects I would also like to cover.  Mostly so I don’t forget but I would be thrilled for your input.

SQL Synonyms or In Other Words – A small brief on the power and use of the synonym.

Developer Productivity: SSMS Templates – How to create and use templates in SQL Server Management Studio.  If I learn how I would also include SQL Command Mode too.

Developer Productivity: Tools for SSMS – Demonstrations of some of the built-in and 3rd party tools to enhance developer productivity in Server Management Studio.