Brent Ozar’s Mastering Courses: My Honest Review

This is an honest review of Brent Ozar’s Mastering courses after finishing them all some months ago.

I couldn’t write this a couple of years ago. Working with databases was a subject I avoided at all costs. Even to the point where I traded database-related tasks with an ex-coworker at a past job.

Avoiding database concepts cost me painful lessons.

Like the day I wrote a query with a function around a column in the WHERE and it almost took the server to its knees. That query was poorly written, and the table didn’t have good indexes. My query ended up making SQL Server scan the whole table. Arrgggg!

But, it changed a couple of years later while working with one of my clients.

They asked me to investigate the performance of some critical parts of the app. The bottleneck was in the database and I ended up Googling what to do to speed up SQL Server queries and compiling six SQL Server performance tuning tips I found.

In that search for performance tuning advice, I found Brent Ozar and his Mastering courses. These are the three things I liked after taking them.

1. Realistic Labs and Workloads

As part of Brent’s courses, we work with a copy of the StackOverflow database. Yeap, the same StackOverflow we all know and use.

After every subject in each course, we have labs to finish. Labs with bad queries, no indexes, blocking issues, etc. For the last course, Mastering Server Tuning, we have an emergency to fix. A server is on fire, and we have to put down the fire and lay out a long-term fix.

Often, some labs have easier alternatives. Either focus on a particular issue or run a workload and assess the whole situation.

2. Constraints to Solve Labs

As we progress throughout the courses, we start to have constraints to solve the labs. For example, “no index changes allowed” or “only query-level fixes.”

But, the exercise I like the most is the “Let’s play being performance consultant.” We have to fix a workload under 30 minutes with as few changes as possible. The closest thing to a real-world emergency. That’s from Mastering Server Tuning again. My favorite course.

Of course, there are more courses. They’re four in total. There’s one course solely on indexes, another one about query tuning, one to fix parameter sniffing issues, and, my favorite, the one on server-level fixes. Each course sits on top of the previous ones.

3. Popular Wisdom and Guerilla Tactics

All over the courses, Brent shares his experience as a consultant. I have those tips and pieces of advice on my notes like “when working with clients.”

For example, he shares to build as few indexes as possible and provide rollback scripts for index creation, just in case. Also, to provide a prioritized list of actionable steps to make SQL Server fast.

Also, he shares personal anecdotes. Like the day he went to consult wearing jeans, and everybody at the place was wearing jackets and ties. That story didn’t have a happy ending for the company. But, I won’t tell you more so you can find out what happened by taking the courses.

Parting Thought

Voilà! These are the three things I liked. My biggest lessons are:

1. Focus all tuning efforts on the top-most wait type, and,
2. Make as few changes as possible to take you across the finish line.

Often, we start to push buttons and turn knobs expecting SQL Server to run faster, without noticeable improvements and making more harm than good.

I will take the second lesson to other parts of my work, even outside of performance tuning context. Focus on the few changes that make the biggest impact.

To learn how to make your SQL Server go faster with Brent—and support this blog—buy the Fundamentals Bundle here and the Fundamentals + Mastering Bundle here. I can’t recommend Brent’s courses enough. Learn from a real expert, with real queries.

Monday Links: Blog, Error Messages and Recruiters

Toxic Culture Is Driving the Great Resignation

I have read a couple of times about the Great Resignation. I guess the world isn’t the place after the pandemic. And that is reflected in the job market too. “A toxic corporate culture is the single best predictor of which companies suffered from high attrition in the first six months of the Great Resignation.” Read full article

What’s in a Good Error Message?

I don’t know how many times I have debugged the “The given key was not present in the dictionary” error. In fact, I wrote two C# idioms to avoid exceptions when working with dictionaries. From the article, to write better error messages: give context, show the actual error, and tell how to mitigate it. Read full article

Why don’t I have a blog?

I have always heard: “start your own blog.” Well, I started my own blog…you’re reading it. But this is a post about the opposite: why not have a blog. The author points out is that posts only rephrase Reddit or StackOverflow’s comments. But, I learned from Show Your Work that nothing is completely original. We all have some sort of inspiration. And, precisely that, and our own voice make our blogs unique. Read full article

Learn By Wrapping

The best advice to learn something is to learn by doing. Finding project ideas is often difficult. Another TODO app, really? This article shares a learning activity: Write a wrapper for a library or an API you’re already familiar with. Instead of jumping to the documentation, start writing a wrapper in the language you want to learn. Read full article

Career Advice Nobody Gave Me: Never Ignore a Recruiter

These days, anyone with a LinkedIn profile and “Software engineer” in the headline is getting lots of spam messages and connection requests. I bet you already got a message between these lines: “Hi X, I have one company interested in your profile. Are you available for a quick call to share more details? Please, send me your CV at this email.”

This article shares a template to reply back to these spammy messages. If you use it, you will be asking for a company name, seniority, and compensation before starting any conversation. That would be enough feedback for recruiters too. Read full article

Voilà! Another five reads! Do you answer spammy messages or connection requests on LinkedIn? Do you have a template to answer recruiters? What strategies do you use to learn new programming languages?

Are you interested in unit testing? Check my Unit Testing 101 series. Don’t miss the previous Monday Links on Going solo, Making Friends and AutoMapper.

BugOfTheDay: Object definitions, spaces, and checksums

These days I was working with a database migration tool and ended up spending almost a day figuring out why my migration didn’t work. This is what I learned after debugging an issue for almost an entire day.

In one of my client’s projects to create or update stored procedures, we use a custom migrator tool, a wrapper on top of DbUp, “a set of .NET libraries that help you to deploy changes to different databases.” I’ve already written about Simple.Migrator, a similar tool.

To avoid updating the wrong version of a stored procedure, we rely on checksums. Before updating a stored procedure, we calculate the checksum of the existing stored procedure using a command-line tool.

How to find object definitions in SQL Server?

By the way… To find the text of a stored procedure in SQL Server, use the OBJECT_DEFINITION() function with the object id of the stored procedure.

Like this,

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.MyCoolStoredProc'))
GO

Checksums and CREATE statements

With the checksum of the stored procedure to update, we write a header comment in the new script file. Something like,

/*
Checksum: "A-Sha1-here-of-the-previous-stored-proc"
*/
ALTER PROC dbo.MyCoolStoredProc
BEGIN
  -- Beep, beep, boop...
END

To upgrade the database, the migrator compares the checksums of objects in the database with the checksums in header comments. If they’re different, the migrator displays a warning message and “fails fast.”

Here comes the funny part. When I ran the migrator on my local machine, it always reported a difference. Even when I was grabbing the checksum from the migrator tool itself. Arrggg!

After debugging for a while and isolating the problem I found something. On the previous script for the same stored procedure, I started the script with CREATE OR ALTER PROC. There’s nothing wrong with that.

But there’s a difference in the object definitions of a stored procedure created with CREATE and with CREATE OR ALTER.

CREATE PROC vs CREATE OR ALTER PROC

Let me show you an example. Let’s create the same stored procedure with CREATE and CREATE OR ALTER to see its object definition.

/* With just CREATE */
CREATE PROC dbo.test
AS
SELECT 1
GO

SELECT LEN(OBJECT_DEFINITION(OBJECT_ID('dbo.Test'))) AS Length
    , OBJECT_DEFINITION(OBJECT_ID('dbo.Test')) AS Text
GO

/* What about CREATE OR ALTER? */
CREATE OR ALTER PROC dbo.test
AS
SELECT 1
GO

SELECT LEN(OBJECT_DEFINITION(OBJECT_ID('dbo.Test'))) AS Length
    , OBJECT_DEFINITION(OBJECT_ID('dbo.Test')) AS Text
GO

Here’s the output.

Let’s notice the length of the two object definitions. They’re different! Some spaces were making my life harder. Arrrggg!

The migrator compared checksums of the object definition from the database and the one in the header comment. They were different in some spaces. Spaces!

I made the mistake of writing CREATE OR ALTER on a previous migration, and the migrator didn’t take into account spaces in object names before creating checksums. I had to rewrite the previous script to use ALTER and recreate the checksums.

Parting thoughts

But, what’s in this story for you? We should create processes to prevent mistakes in the first place. For example:

• scripts to commit SQL code formatted properly
• validations to avoid applying data migrations to the wrong environment
• extensions or plugins to follow naming conventions
• scripts to install the right tools and dependencies to run a project
• up to date documentation for internal tools

I hope you got the idea. It should be hard to make stupid mistakes.

Often, code reviews aren’t enough to enforce conventions and prevent mistakes. We’re humans and we all make mistakes. And, the more code someone reviews in a session, the more tired he will get. And, the more reviewers we add, the less effective the process gets.

Voilà! That’s what I learned these days: read object definitions from SQL Server, polish my debugging skills, and build processes around our everyday development practice.

For more content on SQL Server, check my other posts on functions and WHERE clauses, implicit conversions, and index recommendations.

Happy debugging!

Working with ASP.NET Core IDistributedCache Provider for NCache

As we learned last time, when I covered in-memory caching with ASP.NET Core, a cache is a storage layer between an application and an external resource (a database, for example) used to speed up future requests to that resource. In this post, let’s use ASP.NET Core IDistributedCache abstractions to write a data caching layer using NCache.

1. What’s NCache?

From NCache official page, “NCache is an Open Source in-memory distributed cache for .NET, Java, and Node.js applications.”

Among other things, we can use NCache as a database cache, NHibernate 2nd-level cache, Entity Framework cache, and web cache for sessions and responses.

NCache comes in three editions: Open Source, Professional, and Enterprise. The Open Source version supports up to two nodes and its cache server is only available for .NET Framework version 4.8. For a complete list of differences, check NCache edition comparison.

One of the NCache key features is performance. Based on their own benchmarks, “NCache can linearly scale to achieve 2 million operations per second with a 5-server cache cluster.”

2. How to install NCache on a Windows machine?

Let’s see how to install an NCache server on a Windows machine. For this, we need a Windows installer and have a trial license key. Let’s install NCache Enterprise edition, version 5.2 SP1.

After running the installer, we need to select the installation type from three options: Cache server, remote client, and Developer/QA. Let’s choose Cache Server.

Then, we need to enter a license key. Let’s make sure to have a license key for the same version we’re installing. Otherwise, we will get an “invalid license key” error. We receive the license key in a message sent to the email address we used during registration.

Next, we need to enter the full name, email, and organization we used to register ourselves while requesting the trial license.

Then, we need to select an IP address to bind our NCache server to. Let’s stick to the defaults.

Next, we need to choose an account to run NCache. Let’s use the Local System Account.

Once the installation finishes, our default browser will open with the Web Manager. By default, NCache has a default cache named demoCache.

Next time, we can fire the Web Manager by navigating to http://localhost:8251.

NCache’s official site recommends a minimum of two servers for redundancy purposes. But, for our sample app, let’s use a single-node server for testing purposes.

So far, we have covered the installation instructions for a Windows machine. But, we can also install NCache in Linux and Docker containers. And, we can use NCache as virtual machines in Azure and AWS.

3. How to add and retrieve data from an NCache cache?

Now, we’re ready to start using our NCache server from a .NET app. In Visual Studio, let’s create a solution with a .NET 6 “MSTest Test Project” and a class file to learn the basic caching operations with NCache.

Connecting to an NCache cache

Before connecting to our NCache server, we need to first install the client NuGet package: Alachisoft.NCache.SDK. Let’s use the version: 5.2.1.

To start a connection, we need the GetCache() method with a cache name. For our sample app, let’s use the default cache: demoCache.

Let’s start writing a test to add and retrieve movies from a cache.

using Alachisoft.NCache.Client;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace NCacheDemo.Tests;

[TestClass]
public class NCacheTests
{
    [TestMethod]
    public void AddItem()
    {
        var cacheName = "demoCache";
        ICache cache = CacheManager.GetCache(cacheName);

        // We will fill in the details later
    }
}

If you’re new to unit testing, start looking at how to write your first unit test in C# with MSTest.

Notice we didn’t have to use a connection string to connect to our cache. We only used a cache name. The same one as in the Web Manager: demoCache.

NCache uses a client.ncconf file instead of connection strings. We can define this file at the application or installation level. For our tests, we’re relying on the configuration file at the installation level. That’s why we only needed the cache name.

Adding items

To add a new item to the cache, we need to use the Add() and AddAsync() methods with a key and a CacheItem to cache. The key is an identifier and the item is a wrapper for the object to cache.

Every item to cache needs an expiration. The CacheItem has an Expiration property for that.

There are two basic expiration types: Absolute and Sliding.

A cached item with Absolute expiration expires after a given time. Let’s say, a few seconds. But, an item with Sliding expiration gets renewed every time it’s accessed. If within the sliding time, we don’t retrieve the item, it expires.

Let’s update our test to add a movie to our cache.

using Alachisoft.NCache.Client;
using Alachisoft.NCache.Runtime.Caching;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Threading.Tasks;

namespace NCacheDemo.Tests;

[TestClass]
public class NCacheTests
{
    private const string CacheName = "demoCache";

    [TestMethod]
    public async Task AddItem()
    {
        var movie = new Movie(1, "Titanic");
        var cacheKey = movie.ToCacheKey();
        var cacheItem = ToCacheItem(movie);

        ICache cache = CacheManager.GetCache(CacheName);
        // Let's add Titanic to the cache...
        await cache.AddAsync(cacheKey, cacheItem);

        // We will fill in the details later
    }

    private CacheItem ToCacheItem(Movie movie)
        => new CacheItem(movie)
        {
            Expiration = new Expiration(ExpirationType.Absolute, TimeSpan.FromSeconds(1))
        };
}

[Serializable]
public record Movie(int Id, string Name)
{
    public string ToCacheKey()
        => $"{nameof(Movie)}:{Id}";
}

Notice, we used two helper methods: ToCacheKey() to create the key from every movie and ToCacheItem() to create a cache item from a movie.

We used records from C# 9.0 to create our Movie class. Also, we needed to annotate it with the [Serializable] attribute.

Retrieving items

After adding items, let’s retrieve them. For this, we need the Get<T>() method with a key.

Let’s complete our first unit test to retrieve the object we added.

using Alachisoft.NCache.Client;
using Alachisoft.NCache.Runtime.Caching;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Threading.Tasks;

namespace NCacheDemo.Tests;

[TestClass]
public class NCacheTests
{
    private const string CacheName = "demoCache";

    [TestMethod]
    public async Task AddItem()
    {
        var movie = new Movie(1, "Titanic");
        var cacheKey = movie.ToCacheKey();
        var cacheItem = ToCacheItem(movie);

        ICache cache = CacheManager.GetCache(CacheName);
        await cache.AddAsync(cacheKey, cacheItem);

        // Let's bring Titanic back...
        var cachedMovie = cache.Get<Movie>(cacheKey);
        Assert.AreEqual(movie, cachedMovie);
    }

    private CacheItem ToCacheItem(Movie movie)
        => new CacheItem(movie)
        {
            Expiration = new Expiration(ExpirationType.Absolute, TimeSpan.FromSeconds(1))
        };
}

[Serializable]
public record Movie(int Id, string Name)
{
    public string ToCacheKey()
        => $"{nameof(Movie)}:{Id}";
}

Updating items

If we try to add an item with the same key using the Add() or AddAsync() methods, they will throw an OperationFailedException. Try to add a unit test to prove that.

To either add a new item or update an existing one, we should use the Insert() or InserAsync() methods instead. Let’s use them in another test.

using Alachisoft.NCache.Client;
using Alachisoft.NCache.Runtime.Caching;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Threading.Tasks;

namespace NCacheDemo.Tests;

[TestClass]
public class NCacheTests
{
    // Our previous test is the same
    
    [TestMethod]
    public async Task UpdateItem()
    {
        ICache cache = CacheManager.GetCache(CacheName);

        var movie = new Movie(2, "5th Element");
        var cacheKey = movie.ToCacheKey();
        var cacheItem = ToCacheItem(movie);
        // Let's add the 5th Element here...
        await cache.AddAsync(cacheKey, cacheItem);

        var updatedMovie = new Movie(2, "Fifth Element");
        var updatedCacheItem = ToCacheItem(updatedMovie);
        // There's already a cache item with the same key...
        await cache.InsertAsync(cacheKey, updatedCacheItem);

        var cachedMovie = cache.Get<Movie>(cacheKey);
        Assert.AreEqual(updatedMovie, cachedMovie);
    }

    // Rest of the file...
}

Notice we used the InsertAsync() method to add an item with the same key. When we retrieved it, it contained the updated version of the item.

There’s another basic method: Remove() and RemoveAsync(). We can guess what they do. Again, try to write a test to prove that.

4. How to use ASP.NET Core IDistributedCache with NCache?

Up to this point, we have NCache installed and know how to add, retrieve, update, and remove items.

Let’s revisit our sample application from our post about using a Redis-powered cache layer.

Let’s remember the example from that last post. We had an endpoint that uses a service to access a database, but it takes a couple of seconds to complete. Let’s think of retrieving complex object graphs or doing some computations with the data before returning it.

Something like this,

using DistributedCacheWithNCache.Responses;

namespace DistributedCacheWithNCache.Services;

public class SlowService
{
    public async Task<Something> DoSomethingSlowlyAsync(int someId)
    {
        // Beep, boop...Aligning satellites...
        await Task.Delay(3 * 1000);

        return new Something
        {
            SomeId = someId,
            Value = "Anything"
        };
    }
}

Notice we emulated a database call with a 3-second delay.

Also, we wrote a set of extensions methods on top of the IDistributedCache to add and retrieve objects from a cache.

There were the extension methods we wrote last time,

using Microsoft.Extensions.Caching.Distributed;
using Newtonsoft.Json;

namespace DistributedCacheWithNCache.Services;

public static class DistributedCacheExtensions
{
    public static readonly DistributedCacheEntryOptions DefaultDistributedCacheEntryOptions
        = new DistributedCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = TimeSpan.FromSeconds(60),
            SlidingExpiration = TimeSpan.FromSeconds(10),
        };

    public static async Task<TObject> GetOrSetValueAsync<TObject>(
        this IDistributedCache cache,
        string key,
        Func<Task<TObject>> factory,
        DistributedCacheEntryOptions options = null)
            where TObject : class
    {
        var result = await cache.GetValueAsync<TObject>(key);
        if (result != null)
        {
            return result;
        }

        result = await factory();

        await cache.SetValueAsync(key, result, options);

        return result;
    }

    private static async Task<TObject> GetValueAsync<TObject>(
        this IDistributedCache cache,
        string key)
            where TObject : class
    {
        var data = await cache.GetStringAsync(key);
        if (data == null)
        {
            return default;
        }

        return JsonConvert.DeserializeObject<TObject>(data);
    }

    private static async Task SetValueAsync<TObject>(
        this IDistributedCache cache,
        string key,
        TObject value,
        DistributedCacheEntryOptions options = null)
            where TObject : class
    {
        var data = JsonConvert.SerializeObject(value);

        await cache.SetStringAsync(key, data, options ?? DefaultDistributedCacheEntryOptions);
    }
}

Notice we used Newtonsoft.Json to serialize and deserialize objects.

NCache and the IDistributedCache interface

Now, let’s use a .NET 6.0 “ASP.NET Core Web App,” those extension methods on top of IDistributedCache, and NCache to speed up the SlowService.

First, we need to install the NuGet package NCache.Microsoft.Extensions.Caching. This package implements the IDistributedCache interface using NCache, of course.

After installing that NuGet package, we need to add the cache into the ASP.NET dependencies container in the Program.cs file. To achieve this, we need the AddNCacheDistributedCache() method.

// Program.cs
using Alachisoft.NCache.Caching.Distributed;
using DistributedCacheWithNCache;
using DistributedCacheWithNCache.Services;

var (builder, services) = WebApplication.CreateBuilder(args);

services.AddControllers();
services.AddNCacheDistributedCache((options) =>
//      ^^^^^
// We add the NCache implementation here...
{
    options.CacheName = "demoCache";
    options.EnableLogs = true;
    options.ExceptionsEnabled = true;
});
services.AddTransient<SlowService>();

var app = builder.Build();
app.MapControllers();
app.Run();

Notice, we continued to use the same cache name: demoCache. And, also we relied on a Deconstruct method to have the builder and services variables deconstructed. I took this idea from Khalid Abuhakmeh’s Adding Clarity To .NET Minimal Hosting APIs.

Back in the SlowService, we can use the IDistributedCache interface injected into the constructor and the extension methods in the DistributedCacheExtensions class. Like this,

using DistributedCacheWithNCache.Responses;
using Microsoft.Extensions.Caching.Distributed;

namespace DistributedCacheWithNCache.Services;

public class SlowService
{
    private readonly IDistributedCache _cache;

    public SlowService(IDistributedCache cache)
    {
        _cache = cache;
    }

    public async Task<Something> DoSomethingSlowlyAsync(int someId)
    {
        var key = $"{nameof(someId)}:{someId}";
        // Here we wrap the DoSomethingAsync method around the cache logic
        return await _cache.GetOrSetValueAsync(key, async () => await DoSomethingAsync(someId));
    }

    private static async Task<Something> DoSomethingAsync(int someId)
    {
        // Beep, boop...Aligning satellites...
        await Task.Delay(3 * 1000);

        return new Something
        {
            SomeId = someId,
            Value = "Anything"
        };
    }
}

Notice, we wrapped the DoSomethingAsync() with actual retrieving logic around the caching logic in the GetOrSetValueAsync(). At some point, we will have the same data in our caching and storage layers.

With the caching in place, if we hit one endpoint that uses that service, we will see faster response times after the first call delayed by 3 seconds.

Also, if we go back to NCache Web Manager, we should see some activity in the server.

In this scenario, all the logic to add and retrieve items is abstracted behind the IDistributedCache interface. That’s why we didn’t need to directly call the Add() or Get<T>() method. Although, if we take a look at the NCache source code, we will find those methods here and here.

Voilà! That’s NCache and how to use it with the IDistributedCache interface. With NCache, we have a distributed cache server with few configurations and a dashboard out-of-the-box. Also, we can add all the caching logic into decorators and have our services as clean as possible.

To follow along with the code we wrote in this post, check my Ncache Demo repository over on GitHub.

To read more content, check my Unit Testing 101 series to learn from how to write your first unit tests to mocks. Also, check how to implement full-text searching with Lucene and NCache

I wrote this post in collaboration with Alachisoft, NCache creators.

Happy coding!

TIL: T-SQL doesn't have constants and variables aren't a good idea

Today I learned how to use constants in SQL Server stored procedures. While getting a stored procedure reviewed, I got one comment to remove literal values. This is how to bring constants in T-SQL.

SQL Server doesn’t have a keyword for constants. To introduce constants in stored procedures, write literal values next to an explaining comment or use single-row views with the constant values as columns.

1. Don’t use variables as constants

From C# and other programming languages, we’ve learned to use constants or enums instead of magic values all over our code. Often, we would like to bring constants to our T-SQL queries. But…

T-SQL doesn’t have a keyword for constants. And SQL Server engine doesn’t inline variables when executing stored procedures.

The first thing we try by mistake to emulate constants is to use variables.

For example, let’s find all StackOverflow users with two reputation points. That’s not a popular reputation among StackOverflow users. We write something like this,

/* An index to speed things up a bit */
CREATE INDEX Reputation ON dbo.Users(Reputation)
GO

CREATE OR ALTER PROC dbo.usp_GetUsers
AS
BEGIN
    /* This is how we often emulate constants */
    DECLARE @Reputation INT = 2;

    SELECT *
    FROM dbo.Users u
    WHERE u.Reputation = @Reputation
    ORDER BY u.CreationDate DESC;
END
GO

This is the execution plan. Let’s keep an eye on the number of estimated users.

But, there’s a downside. Variables inside stored procedures trigger a different behavior in SQL Server.

Variables and execution plans

When executing a stored procedure, SQL Server creates an execution plan for the first set of parameters it sees. And, the next time we run that stored procedure, SQL Server reuses the same execution plan, even if we use different parameters. We call this behavior Parameter Sniffing.

SQL Server uses statistics (histograms built from samples of our data) to choose the shape of execution plans. SQL Server has to choose the first table to read, the number of threads, and the amount of memory, among other things.

But, when there are variables in a stored procedure, SQL Server builds execution plans, not from statistics (samples of our data), but from an “average value.”

Variables make SQL Server build different execution plans, probably not suited for the set of parameters we’re calling our stored prcedures with. That’s why variables aren’t a good idea to replace constants.

2. Literal values and comments

The simplest solution to constants in T-SQL is to use literal values.

To make stored procedures more maintainable, it’s a good idea to write an explaining comment next to the literal value.

Let’s rewrite our stored procedure with a literal and a comment.

CREATE OR ALTER PROC dbo.usp_GetUsers
AS
BEGIN
    SELECT DisplayName, Location, CreationDate
    FROM dbo.Users u
    WHERE u.Reputation = /* Interesting reputation */2
    ORDER BY u.CreationDate DESC;
END
GO

This is the execution plan.

Do you remember the estimated number of users from our example with variables? It was 123 users. Now, we have a more accurate estimated number. It’s 1,854 users. SQL Server isn’t using an average value anymore. It has better estimates this time!

We even have an index recommendation in our execution plan. By the way, don’t blindly follow index recommendations, just listening to them. They’re only a list of columns to consider indexing.

3. Create a view for constants

The hardcoded value and an explanatory comment are OK if we have our “constant” in a few places.

A more maintainable solution to literal values is to create a single-row view with columns named after the constants to declare.

CREATE OR ALTER VIEW dbo.vw_Constant
AS
SELECT (2) InterestingReputation
GO

With that view in place, we can replace the hardcoded values in our stored procedure.

CREATE OR ALTER PROC dbo.usp_GetUsers
AS
BEGIN
    SELECT *
    FROM dbo.Users u
    /* The view with our constant */
    INNER JOIN dbo.vw_Constant c 
    ON u.Reputation = c.InterestingReputation
    ORDER BY u.CreationDate DESC;
END
GO

A more maintainable alternative while keeping good estimates.

Voilà! That’s how to use constants with a view in SQL Server. I found a proposal to introduce a constant keyword in SQL Server. I learned about the trick with views from this StackOverflow question and in this one too.

For more content on SQL Server, check my other posts on functions and WHERE clauses, implicit conversions and case-sensitive searches.

Happy coding!