I’m thrilled to share that I’ve completed my first Pluralsight course, Programming SQL Server Triggers and Functions, and it has finally been published for subscribers to enjoy! I began the journey of authoring my first course with Pluralsight September 2018 and officially started production of this course in February of this year. It was certainly a challenge to create a course of 3+ hours while changing jobs, having our sixth child and speaking at a few conferences on other topics, but the overall experience was more rewarding than I expected and has only increased my desire to become a better trainer.
Why a Course on Triggers and Functions?
Seriously, right? Seems like an bit of an “oldie but a goodie” place to start my Pluralsight training career… eh?
I’ve grown up teaching and training in one capacity or another most of my life. My undergrad degree is in education and after teaching for a few years, I was blessed to follow my other passion and develop a great career in software and data. In many regards I often feel like I’m at the bottom of the class on most topics, struggling to keep up. But I also realized that no matter how much I have to learn about a subject, there’s someone else that needs to understand this same information for something they’re working on and I might be the one that can help them get to the next step.
After I auditioned for Pluralsight and was accepted as an author, I came to the first meeting with a handful of topics focused on the Microsoft Data Platform that I wanted to share with the world. SQL Server Database Projects, Power BI Custom Data Connectors, Service Broker.
But I was offered a bit of a “fast-track” to my first course if I chose a course from the list dealing with specific Learning Paths, one of which was SQL Server. Of the 10-ish SQL Server topics on the list, only a few were still available to claim and… well… I happen to be a fan of both Triggers and Functions when they’re right for the job. Pluralsight had already created a basic outline of what they wanted the course to cover and how long they thought it might be. I took 6 weeks to fill in the blanks (remember, I was growing my family and switching jobs) and finally began production in February.
What Does the Course Cover?
I’m glad you asked! I ended up covering a lot more ground than I thought I would. Honestly, if the outline hadn’t been provided for me as a starting point, I might have even recommended the course be broken into two different courses. There are just so many topics to cover when dealing with these two features that, in all honesty, it could have been twice as long. (I’m guessing that most authors feel the same way after recording a course like this)
1. DML Triggers
Of the 3+ hours, the first module on DML Triggers is the longest at 54 minutes. I’ve found over the years that there are a lot of misconceptions on the basics of how Triggers work, including some confusion between SQL Server and other databases like Postgres, that it felt like the detail and coverage of these specific topics was necessary. I discuss and demo all of the major parts of using DML Triggers including AFTER and INSTEAD OF, the INSERTED and DELETED virtual tables, and Execution Order. While the concept of how a DML Trigger works regardless of the event that triggered it are easily transferable (INSERT, UPDATE, or DELETE), I felt it was worth trying to provide at least one example of how and why someone might use each of them. I even talk about using INSTEAD OF Triggers on VIEWS, something many SQL developers just don’t understand.
2. DDL and LOGON Triggers
In this second module I focus on the anatomy of a DDL/LOGON Trigger, what makes them different from DML triggers, and when they tend to be useful. Most of the discussion and examples deal with protecting data and schema using these tools, both through logging and restricting access. Some technology might be slowly diminishing the need for DDL/LOGON triggers, but I still think there are valid use cases to consider.
3. Working Smarter with Triggers
Let’s be honest, it’s the year 2019 and many SQL Server professionals would caution you against using Triggers at all. Even Joe Celko, one of the SQL standard authors, thinks you should generally try to avoid them. I’m very aware of the strong opinions against using triggersand have often seen when they are overused and unmaintained… leading to countless headaches.
But as a data professional, they can be a great tool to solve specific problems when used well! In this module I cover Execution Context and security, how MERGE interacts with Triggers, logging information outside of the transaction, avoiding work when possible, and even a small shout-out to Service Broker and the value of using asynchronous triggers.
4. Reusing Code with Functions
This module is simply an intro to Functions within SQL Server and what the differences are between Multi-statement and Single-statement functions to prepare for the next two modules. Again, differences in other database technologies and even misconceptions within the SQL Server community, can make a detailed discussion on Functions difficult without some initial groundwork.
5. Multi-Statement Scalar and Table-Valued Functions
Functions are a good, necessary tool for producing consistent and reusable code. For nearly two decades in SQL Server, most developers have written and used these types of functions and yet many don’t fully understand the impact it could be having on their overall application performance.
Therefore, in this module I talk about how to write both Scalar and Table-Valued functions and how they can be useful. However, I also show why they can be problematic, what to look for and some recent enhancements, starting in SQL Server 2017, that can improve the performance of your current (and future) Multi-statement functions.
6. Single-Statement (Inline) Table-Valued Functions
The course ends with a module that focuses on Single-Statement Table-Valued functions, often referred to as Inline Table-Valued Functions. I discuss how they differ from Multi-Statement functions, how to create them, and then I discuss and demonstrate how to convert a Multi-Statement function into an Inline Table-Valued Function and what the advantages can be.
Am I a Trigger and Function Expert?
No! I’m not going to lie to you. There are a ton of other people in the SQL Server community that are more qualified than I am to teach a course like this. I happen to have dealt with both Triggers and Functions a lot over the last 15 years and I think I have a good foundation to teach from. I was surprised more than once at how much I didn’t know at the beginning of creating this course. Without a doubt, this certainly proved to be a reminder that “the best way to learn something is to teach it”.
I do think, however, that this course will provide a solid foundation and understanding about Triggers and Functions within SQL Server for most beginner/intermediate SQL Server developers… allowing you to do better work.
Will I Be Creating More Courses?
I hope so. I don’t have the best setup in our current house for producing courses, which sometimes required my family to take day-trips so that I could record with guaranteed quiet, and even then I often had to post-process the audio side of the course. I’ve also just taken on a leadership role in my new company which has shifted my focus a bit for now.
But as I said at the beginning of the article, I love teaching and training, and the staff at Pluralsight are top-notch and extremely helpful. I’d be silly not to try and do it again if I can find the right topic.
Because of the change in my current role, the next courses will likely shift away from SQL Server in particular to PostgreSQL, Power BI and maybe even some tooling topics like Azure Data Studio. I’ve committed to my wife that I won’t do a course unless it’s around a topic I already speak about or would be preparing to speak on. Having that material ready at the beginning significantly cuts down the time needed to create demos and slides.
I hope you have an opportunity to take the course or pass it on to others that are starting their SQL Server journey.