THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Full Outline for my PASS Pre-Con: SQLCLR From Beginner to Expert

Since the announcement of my SQLCLR pre-conference seminar at this November's PASS conference I have received a few e-mails asking for more detail about what I will be covering. In addition to the Q&A I did with PASS, I thought it might be helpful if I post the entire schedule for the day (see below).

The seminar's goal is to literally move you from "Beginner" to "Expert" and although some familiarity with the topic will help you get into the latter category faster, none is required. I do recommend at least basic knowledge of C# and ADO.NET; there are a couple of free online courses (such as this one), and if you're planning to addend the pre-con and have not yet worked with C# I highly recommend taking a few hours to go through one of them. As a matter of fact, even if you aren't planning to attend the pre-con you should do this; learning another language is fun and, in my opinion, will improve your T-SQL skills by opening your mind to new and different ways of solving problems.

Not sure if you're interested in getting involved with all of this SQLCLR stuff? Then check out my 24 Hours of PASS session, SQLCLR or T-SQL? A Brief Survey of Performance Options. In this session I will present just a few of the performance examples from the full-day pre-con, and in slightly less detail due to time constraints, but it should be enough to show you the power and flexibility that well-engineered SQLCLR solutions can provide.

If you have any questions about either the 24 Hours of PASS session or the pre-con, feel free to post here or contact me through my blog. I'm really looking forward to both events and hope to see some of you there!


SQLCLR From Beginner to Expert

  • Module 1: Overview (What SQLCLR Can Do)
    • Why .NET in the data tier?
    • Does it perform?
    • What kinds of business and technical challenges can it solve?
    • Is it hard to manage?
  • Module 2: Introduction to SQLCLR: UDFs
    • Creating your first SQLCLR method
    • CREATE ASSEMBLY and related statements
    • Intro to permission sets
    • SqlTypes library
    • Interaction concerns for nullable types and new SQL Server 2008 data types
    • SqlFunction attribute
    • Determinism, precision, and system data access
    • Dealing with Visual Studio deployment issues
    • IEnumerable and table-valued UDFs
    • Performance and UDFs
  • Module 3: SQLCLR Stored Procedures and Data Access
    • Stored procedures vs. functions
    • Introduction to the in-proc data access model
    • The context connection
    • SqlContext class
    • SqlPipe class
    • Exceptions and exception handling
    • When does it make sense to use a CLR stored procedure?
    • When should a CLR UDF be used instead?
    • A few thoughts on SQLCLR triggers
    • Use cases
      • Data access and performance
      • Better administration through SQLCLR
      • Solving sequential and time series problems
  • Module 4: Security, Reliability, and Design Considerations
    • What do the permission sets REALLY mean?
    • HostProtection
    • Code Access Security
    • Database trustworthiness
    • Strong named assemblies
    • Designing for least privilege
    • Designing for reuse
    • Creating robust utility classes
  • Module 5: User-Defined Types
    • Why complex types?
    • SqlUserDefinedType attribute
    • INullable interface
    • Serialization and ordering
    • Static and instance methods
    • Data validation and domain rules checking
    • The Parse method
    • Eliminating the Parse method: Factory type patterns and strongly-typed methods
    • Design considerations
    • Use cases for types and static methods
    • Helping to bridge the app/database gap
  • Module 6: User-Defined Aggregates
    • Where do UDAs fit and what problems do they solve?
    • What are their limitations?
    • SqlUserDefinedAggregate attribute
    • The various methods that make up a UDA
    • The SQL Server 2005 8000-byte limitation and the SQL Server 2008 lack thereof
    • Solving business and analytical problems using UDAs
  • Module 7: Troubleshooting and Ongoing Maintenance
    • Deployment concerns
    • The SQLCLR management UDAs
    • Assembly versioning
    • Changing code (without breaking everything)
Published Monday, August 17, 2009 11:00 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Deep said:

Hi Adam,

Will the conf recording be available after the conference in PASS website?

August 17, 2009 10:48 AM

Adam Machanic said:

Hi Deep,

I believe that they will be selling DVDs of the seminar.

August 17, 2009 11:39 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement