TIL: Three Tricks to Debug Your Dynamic SQL Queries

These three tips will help you to troubleshoot your dynamic queries and identify the source of a dynamic query when you find one in your query store or plan cache.

To make dynamic SQL queries easier to debug, format the generated query with line breaks, add as a comment the name of the source stored procedure and use a parameter to only print the generated query.

1. Format your dynamic SQL queries for more readability

To read your dynamic queries stored in the plan cache, make sure to insert new lines when appropriate.

Use a variable for the line endings. For example, DECLARE @crlf NVARCHAR(2) = NCHAR(13) + NCHAR(10).

Also, to identify the source of a dynamic query, add as a comment the name of the stored procedure generating it. But, don’t use inside that comment a timestamp or any other dynamic text. Otherwise, you will end up with almost identical entries in the plan cache.

2. Add a parameter to print the generated query

To debug the generated dynamic query, add a parameter to print it. And, a second parameter to avoid executing the query.

For example, you can name these two parameters, @Debug_PrintQuery and @Debug_ExecuteQuery, respectively.

3. Change the casing of variables and keywords inside your dynamic SQL

To distinguish errors between the actual SQL query and the dynamic query, change the casing of keywords and variables inside your dynamic query.

Example

In the store procedure dbo.usp_SearchUsers below, notice the use of the variable @crlf to insert line breaks and the comment /* usp_SearchUsers */ to identify the source of the query.

Also, check the two debugging parameters: @Debug_PrintQuery and @Debug_ExecuteQuery. And, finally, see how the casing is different inside the dynamic SQL.

CREATE OR ALTER PROC dbo.usp_SearchUsers
  @SearchDisplayName NVARCHAR(100) = NULL,
  @SearchLocation NVARCHAR(100) = NULL,
  @Debug_PrintQuery TINYINT = 0,
  @Debug_ExecuteQuery TINYINT = 1 AS
BEGIN
  DECLARE @StringToExecute NVARCHAR(4000);
  DECLARE @crlf NVARCHAR(2) = NCHAR(13) + NCHAR(10);
    
  SET @StringToExecute = @crlf + N'/* usp_SearchUsers */' + N'select * from dbo.Users u where 1 = 1 ' + @crlf;

  IF @SearchDisplayName IS NOT NULL
    SET @StringToExecute = @StringToExecute + N' and DisplayName like @searchdisplayName ' + @crlf;

  IF @SearchLocation IS NOT NULL
    SET @StringToExecute = @StringToExecute + N' and Location like @searchlocation ' + @crlf;

  IF @Debug_PrintQuery = 1
    PRINT @StringToExecute

  IF @Debug_ExecuteQuery = 1
    EXEC sp_executesql @StringToExecute, 
      N'@searchdisplayName nvarchar(100), @searchlocation nvarchar(100)', 
      @SearchDisplayName, @SearchLocation;
END
GO

Voilà! That’s how you can make your dynamic SQL queries easier to debug. If you’re new to the whole concept of dynamic SQL queries, check how to NOT to write dynamic SQL.

Source: Dynamic SQL Pro Tips

How to Take Smart Notes. Takeaways

“How to Take Smart Notes” describes the Zettelkasten method in depth. It shows how scientists and writers can produce new content from their notes. But, you don’t have to be a scientist to take advantage of this method. Anyone can use it to organize his knowledge.

The Zettelkasten method is the secret behind Niklas Luhman’s success. He was a prominent German sociologist of the 20th century. He earned the title of Professor at Bielefeld University. To earn this title, he wrote a dissertation based on the notes he had about all the books he had read. He had a collection of over 90.000 notes. Impressive, right?

TL;DR

• Don’t use notebooks to take notes
• Don’t organize your notes per subjects and semester
• Read with pen and paper in hand
• Write your ideas into cards. Put them in your own words
• Put an index number on every card
• Create connections from one card to another

What you need to start with Zettelkasten

To start using the Zettlekasten method, you only need pen, paper and and slip-box. That’s why this method is also called the “slip-box” method.

All you have to do is have a pen and paper when you read. And, translate what you read to your own words. Don’t copy and paste.

Alternatively, you can any text editor to use it with your computer. But, don’t complicate things unnecessarily. Good tools should avoid distractions from your main task: thinking.

How to start taking smart notes

Zettelkasten type of notes

The Zettlekasten method uses three types of notes: fleeting, literature and permanent notes.

Write down everything that comes to your mind on fleeting notes. Once you process these notes, you can toss them.

While reading, make literature notes. Write down on a card what you don’t want to forget. You should write what the book says on what page. Be selective with your literature notes. Keep your literature notes in a reference system.

To make permanent notes, review your literature notes and turn them into connections. The goal isn’t to collect, but to generate new ideas and discussions. Ask yourself how it contradicts, expands or challenges your subject of interest.

Keep a single idea per card. Use a fixed number to identify each card. You can use another card to expand on one. Each note should be self-explanatory.

Create a new note

To add a note to your slip-box, follow these four steps:

1. Does this note relate to another note? Put if after.
2. If that’s not the case, put it at the end.
3. Add links from previous notes to this one or viceversa.
4. Add links to it in index card.

Index cards are notes with references to other notes. They act as entry point to a subject.

How not to take smart notes

Don’t take notes on notebooks and on margins of books. These notes end up in different places. You have to remember where you put them.

Don’t underline or make margin notes. Make a separate note of what got your attention. Put it in the reference system. Then, review it and make it a permanent note.

Don’t store your notes on topics/subject and semester. And, don’t store your notes in chronological order either. It doesn’t allow you to reorder notes.

Don’t note everything on a notebook. Your good ideas will end up entangled with other irrelevant notes. Make sure to use fleeting, literature and permanent notes.

Why Zettlekasten method works

Reading with pen and paper force you to understand. You think you understand something until you have write it in your own words. Make sure you always write the output of your thinking.

Rereading doesn’t work. The next time you read something, you feel familiar. But, it doesn’t mean you understand it. Recalling is what indicates if you have learned something or not. The slip-box will show you your unlearned bits.

Reviewing doesn’t help for understanding and learning. Elaboration is better. It means rewriting what you read in your own words and making connections. The slip-box forces to understand and connect.

Memory is a limited resource. Use an external system to remember things. You don’t want to put in your head what you can put on a piece of paper. To get something out of your head, write it down. Use fleeting notes.

“Read, think and write. Take smart notes along the way”

Voilà! That’s How to Take Smart Notes. Remember, don’t use notebooks or write on book margins. Instead, use indexed cards to take notes and connect them with other cards.

For more content, read how I use plain text to write notes and my takeaways from Ultralearning and Pragmatic Thinking and Learning.

Happy note taking!

TIL: LINQ DefaultIfEmpty method in C#

Today I was reading the AutoFixture source code in GitHub and I found a LINQ method I didn’t know about: DefaultIfEmpty.

DefaultIfEmpty returns a collection containing a single element if the source collection is empty. Otherwise, it returns the same source collection.

For example, let’s find all the movies with a rating greater than 9. Otherwise, return our all-time favorite movie.

// We don't have movies with rating greater than 9
var movies = new List<Movie>
{
    new Movie("Titanic", 5),
    new Movie("Back to the Future", 7),
    new Movie("Black Hawk Down", 6)
};

var allTimesFavorite = new Movie("Fifth Element", 10);
var movieToWatch = movies.Where(movie => movie.Score >= 9)
                    .DefaultIfEmpty(allTimesFavorite)
                    // ^^^^^
                    .First();

// Movie { Name="Fifth Element", Score=10 }

If I had to implement it on my own, it would be like this,

public static IEnumerable<T> DefaultIfEmpty<T>(this Enumerable<T> source, T @default)
    => source.Any() ? source : new[]{ @default };

Voilà! DefaultIfEmpty is helpful to make sure we always have a default value when filtering a collection. It’s a good alternative to FirstOrDefault followed by a null guard.

To learn more about LINQ, check my Quick Guide to LINQ with Examples.

Happy coding!

TIL: SQL Server uses all available memory

SQL Server tries to use all available memory. SQL Server allocates memory during its activity. And, it only releases it when Windows asks for it.

This is normal behavior. SQL Server caches data into memory to reduce access to disk. Remember, SQL Server caches data pages, not query results.

You can limit the amount of memory available by setting the option “Maximum Server Memory”. By default, it is a ridiculous huge number: 2,147,483,647 MB.

This is specially true, if you’re running SQL Server on your development machine.

For your Production instances, check BornSQL’s Max Server Memory Matrix to set the right amount of RAM your SQL Server needs.

Voilà! This is a true story of how SQL Server was eating my memory. We needed some limits to keep things running smoothly on my laptop.

For more SQL Server content, check Six SQL Server performance tuning tips and How to write dynamic SQL queries.

Source: Setting a fixed amount of memory for SQL Server

How to write good unit tests: Noise and Hidden Test Values

These days, I needed to update some unit tests. I found two types of issues with them. Please, continue to read. Maybe, you’re a victim of those issues, too. Let’s learn how to write good unit tests.

To write good unit tests, avoid complex setup scenarios and hidden test values. Often tests are bloated with unneeded or complex code in the Arrange part and full of magic or hidden test values. Unit tests should be even more readable than production code.

The tests I had to update

The tests I needed to update were for an ASP.NET Core API controller, AccountController. This controller created, updated, and suspended user accounts. Also, it sent a welcome email to new users.

These tests checked a configuration object for the sender, reply-to, and contact-us email addresses. The welcome email contained those three emails. If the configuration files miss one of the email addresses, the controller throws an exception from its constructor.

Let’s see one of the tests. This test checks for the sender’s email.

[TestMethod]
public Task AccountController_SenderEmailIsNull_ThrowsException()
{
    var mapper = new Mock<IMapper>();
    var logger = new Mock<ILogger<AccountController>>();
    var accountService = new Mock<IAccountService>();
    var accountPersonService = new Mock<IAccountPersonService>();
    var emailService = new Mock<IEmailService>();
    var emailConfig = new Mock<IOptions<EmailConfiguration>>();
    var httpContextAccessor = new Mock<IHttpContextAccessor>();

    emailConfig.SetupGet(options => options.Value)
        .Returns(new EmailConfiguration()
        {
            ReplyToEmail = "email@email.com",
            SupportEmail = "email@email.com"
        });

    Assert.ThrowsException<ArgumentNullException>(() =>
        new AccountController(
            mapper.Object,
            logger.Object,
            accountService.Object,
            accountPersonService.Object,
            emailService.Object,
            emailConfig.Object,
            httpContextAccessor.Object
        ));

    return Task.CompletedTask;
}

This test uses Moq to create stubs and mocks.

Can you spot any issues in our sample test? The naming convention isn’t one, by the way.

Let’s see the two issues to avoid to write good unit tests.

1. Reduce the noise

Our sample test only cares about one object: IOptions<EmailConfiguration>. All other objects are noise for our test. They don’t have anything to do with the scenario under test. We have to use them to make our test compile.

Use builder methods to reduce complex setup scenarios.

Let’s reduce the noise from our test with a MakeAccountController() method. It will receive the only parameter the test needs.

After this change, our test looked like this:

[TestMethod]
public void AccountController_SenderEmailIsNull_ThrowsException()
//     ^^^^
// We can make this test a void method
{
    var emailConfig = new Mock<IOptions<EmailConfiguration>>();
    emailConfig
        .SetupGet(options => options.Value)
        .Returns(new EmailConfiguration
        {
            ReplyToEmail = "email@email.com",
            SupportEmail = "email@email.com"
        });

    // Notice how we reduced the noise with a builder
    Assert.ThrowsException<ArgumentNullException>(() =>
        MakeAccountController(emailConfig.Object));
        // ^^^^^
    
    // We don't need a return statement here anymore
}

private AccountController MakeAccountController(IOptions<EmailConfiguration> emailConfiguration)
{
    var mapper = new Mock<IMapper>();
    var logger = new Mock<ILogger<AccountController>>();
    var accountService = new Mock<IAccountService>();
    var accountPersonService = new Mock<IAccountPersonService>();
    var emailService = new Mock<IEmailService>();
    // We don't need Mock<IOptions<EmailConfiguration>> here
    var httpContextAccessor = new Mock<IHttpContextAccessor>();

    return new AccountController(
            mapper.Object,
            logger.Object,
            accountService.Object,
            accountPersonService.Object,
            emailService.Object,
            emailConfiguration,
            // ^^^^^
            httpContextAccessor.Object);
}

Also, since our test doesn’t have any asynchronous code, we could declare our test as a void method and remove the return statement. That looked weird in a unit test, in the first place.

With this refactor, our test started to look simpler and easier to read. Now, it’s clear this test only cares about the EmailConfiguration class.

2. Make your test values obvious

Our test states in its name that the sender’s email is null. Anyone reading this test would expect to see a variable set to null and passed around. But, that’s not the case.

Make scenarios under test and test values extremely obvious.

Please, don’t make developers decode your tests.

To make the test scenario obvious in our example, let’s add SenderEmail = null to the initialization of the EmailConfiguration object.

[TestMethod]
public void AccountController_SenderEmailIsNull_ThrowsException()
{
    var emailConfig = new Mock<IOptions<EmailConfiguration>>();
    emailConfig
        .SetupGet(options => options.Value)
        .Returns(new EmailConfiguration
        {
            // The test value is obvious now
            SenderEmail = null,
            //            ^^^^^
            ReplyToEmail = "email@email.com",
            SupportEmail = "email@email.com"
        });

    Assert.ThrowsException<ArgumentNullException>(() =>
        MakeAccountController(emailConfig.Object));
}

If we have similar scenarios, we can use a constant like const string NoEmail = null. Or prefer object mothers and builders to create test data.

3. Don’t write mocks for IOptions

Finally, as an aside, we don’t need a mock on IOptions<EmailConfiguration>.

Don’t use a mock or a stub with the IOptions interface. That would introduce extra complexity. Use Options.Create() with the value to configure instead.

Let’s use the Option.Create() method instead.

[TestMethod]
public void AccountController_SenderEmailIsNull_ThrowsException()
{
    var emailConfig = Options.Create(new EmailConfiguration
    //                ^^^^^
    {
        SenderEmail = null,
        ReplyToEmail = "email@email.com",
        SupportEmail = "email@email.com"
    });

    Assert.ThrowsException<ArgumentNullException>(() =>
        MakeAccountController(emailConfig));
}

Voilà! That’s way easier to read. Do you have noise and hidden test values in your tests? Remember, readability is one of the pillars of unit testing. Don’t make developers decode your tests.

For other tips on writing good unit tests, check my follow-ups on writing failing tests first and using simple test values. Also, don’t miss my Unit Testing 101 series where I cover more subjects like this.

Happy unit testing!