2018 Year End Review

2018-12-27 0 Comments

SQL Server - Dave Mason - 2018

2018 was a busy year for me. There were some misses and lost opportunities, to be sure. But overall, I had some good productivity and growth. I'm not a New Year's resolution person, so there's no checklist of goals to review from a year ago. I'll go mostly from memory. Here goes...

New Job

Towards the end of 2017, I left a full time job to give consulting a try. As January and February unfolded, I finally started getting enough billable hours to make a go of it. A full time job offer found its way to me a couple of months later and I decided to take it. I never announced it here or on social media, but my employer is now Ntirety. I'm a "SQL Server consultant" according to the HR department. It's mostly DBA-type work, but I am doing some occasional SQL development, C# development, and PowerShell development. I'm also doing a fair amount of T-SQL optimization, which I enjoy greatly.

New Blog

Like many of you, I've taken an interest in Data Science. I began learning R earlier this year, and I started a new blog Zero To R to document some of my learning. I've only posted 16 times so far, and to be honest, the blog has stalled a bit. Hopefully that changes soon.


At the end of June, I passed Exam 70-767: Implementing a Data Warehouse. If you wish, summon the vast powers of certification and read about it here.

PASS Summit

Sadly, I missed PASS Summit 2018. It's the second year in a row I've not been able to attend. The dates for 2019 look agreeable, and I should have enough PTO to make it work. Maybe with a little luck, I'll even make it to SQL Saturday Portland the weekend before.


For 2018, I made it to a handful of SQLSaturday events: Tampa (1st time), Jacksonville (3rd?), Atlanta (2nd), and Louisville (2nd). Geographically, I'm in a decent location with many SQLSaturday locations within driving distance. Maybe South Florida and Panama Beach in 2019? Oh, and then there's SQLSaturday Orlando. 2018 was the second time I lent a hand with Andy Warren and the other organizers. It's tough work--and kind of stressful when you're responsible for the big event.

Code Contributions

I haven't been much of a GitHub user and contributor over the years. But I did make my modest SQL to Excel project available there. It seems to be well received--special thanks to everyone at Brent Ozar Unlimited for promoting the project in your newsletter. There were also a few snippets of PowerShell I included with my SQL Server Features Discovery Report post. The folks at dbatools turned it into the Get-DbaFeature cmdlet. Very cool!

SQL Family

I got to meet a number of people face-to-face for the first time this year, mostly at SQLSaturday events. And of course it's always nice to catch up with old friends too. A lot of you are on Twitter and listened to my rants about UCF and ... and ... OK, mostly UCF stuff. Thanks for sticking it out with this college football fanatic. Slack has been a good place to stay in touch with friends and make new friends too. The conversations about SQL, R, PowerShell, and anything else imaginable are fun and informative. There are so many people there willing to share their knowledge and experience in support of others in the community.


Prepared T-SQL Statements

2018-12-01 0 Comments

Dave Mason - SQL Server - Prepared Statements

I recently started reading Grant Fritchey's book SQL Server Execution Plans. I didn't get too many pages deep before the topic of query parameterization and prepared statements came up. As Grant notes, prepared statements "are built from the application code". Prepared T-SQL statements in SQL Server have been somewhat mysterious to me for many years. It's something I felt I should have a better understanding of. I thought to myself, "I'm a developer. I should be able to figure this out." So I decided to go down the rabbit hole and see if I could cobble together an example of a prepared statement using C#.

I quickly found my way to Microsoft documentation for the SqlCommand.Prepare method. To my surprise, that method dates all the way back to version 1.1 of the .NET Framework, which was released in 2003. I was a new C# developer at that time. I didn't know about the method then. And as you're probably figuring out, I just learned about it now.

Before we dive into some code, I want to make a plea to the developers that may be reading. If you arrived here in search of a "How-To" article, please stop and reconsider. In my opinion, prepared statements via sp_prepexec and sp_executevare a poor choice for query parameterization. There are better options, including using stored procedures, parameterizing in T-SQL with sp_executesql, and parameterizing ad-hoc queries with the SqlCommand object. I'm writing from the perspective of a DBA, sharing what I've learned with other DBAs.

OK, DBAs. Are you ready for some C# code? I'll keep it simple and painless. Here's what the code is going to do: connect to SQL Server and insert three rows to dbo.Stooges. That's it. I'll use an ad-hoc query that is parameterized--there should be no risk of SQL injection. The query will be re-used for all three inserts. And SQL Server will use the same execution plan for each insert. Here is the code:

using (SqlConnection conn = new SqlConnection(GetConnectionString()))

    using (SqlCommand cmd = new SqlCommand())
        cmd.Connection = conn;

        // Create and prepare a SQL statement.
        cmd.CommandText =
            "INSERT INTO dbo.Stooges (StoogeName, StoogeNumber) " +
            "VALUES (@name, @number)";
        SqlParameter parName = new SqlParameter("@name", SqlDbType.Text, 128);
        SqlParameter parNumber = new SqlParameter("@number", SqlDbType.TinyInt, 0); 
        parName.Value = "Larry";
        parNumber.Value = 1;

        // Call Prepare after setting the Commandtext and Parameters.

        // Change parameter values and call ExecuteNonQuery.
        cmd.Parameters[0].Value = "Curly";
        cmd.Parameters[1].Value = 2;

        // Change parameter values and call ExecuteNonQuery again.
        cmd.Parameters[0].Value = "Moe";
        cmd.Parameters[1].Value = 3;

At line 1 a connection to SQL Server is created. The connection string identifies the connectivity info. In this example, GetConnectionString() is a user-defined method that builds and returns our connection string. I've omitted the code for that method for brevity. At line 3 the connection is opened.

At line 5 a SqlCommand object is created. You can think of this as a "query" for SQL Server. The query string for the INSERT statement is created starting at line 10. You'll notice the @name and @number placeholders for the parameter values. Next on lines 13 and 14, we create SqlParameter objects for @name and @number. Each SqlParameter has a parameter name, a SqlDbType (aka a data type), and a size. The SqlParameter variables are identified as parName and parNumber.

At lines 15 and 16, parameter values are set in anticipation of our first INSERT for Larry. Then at lines 17 and 18, parName and parNumber are each added to cmd.Parameters, which is a collection of zero or more SqlParameters.

Line 21 is where we "prepare" our query. Specifically, it creates a prepared, reusable version of the command (our INSERT statement) on an instance of SQL Server. Line 22 INSERTs our first stooge by invoking cmd.ExecuteNonQuery(). Most of the hard work is done now. Adding more stooges gets easier from here on out.

Our SqlCommand, cmd still has two parameters in the collection. But they contain the values for Larry. On lines 25 and 26, we assign new values for Curly, who is INSERTed on line 27 where we once again invoke cmd.ExecuteNonQuery(). The process is repeated for Moe on lines 30 - 32.

And that's it from the application perspective. There's not really that much application code involved. If you were monitoring this activity via an Extended Events session (or a Profiler trace) it would look similar to this:

Dave Mason - SQL Server - Prepared Statements

I'd seen stored procedures sp_prepexec and sp_execute during monitoring here and there throughout the years. But I never had a good grasp on how those stored procs work or what they were doing. Now I have a much better idea.

What about the execution plan(s)? How many did the prepared statement actually use? A quick look at sys.dm_exec_query_stats tells us that as advertised, only one execution plan was used.

Dave Mason - SQL Server - Prepared Statements