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
- ALTER ASSEMBLY
- Assembly versioning
- Changing code (without breaking everything)