TIL: Three differences between TRUNCATE and DELETE

Both DELETE and TRUNCATE remove records from a table. But, these days I learned three differences between TRUNCATE and DELETE statements in SQL Server. Let me share them with you.

DELETE accepts a WHERE condition to only remove some records, TRUNCATE doesn’t. DELETE doesn’t reset identity columns to its initial value, but TRUNCATE does. And, DELETE fire triggers, TRUNCATE doesn’t.

To see these three differences in action, let’s create a sample database with a Movies table. It only contains an auto-incremented id, a movie title and an score.

CREATE DATABASE DeleteVsTruncate
GO

USE DeleteVsTruncate
GO

CREATE TABLE dbo.Movies(
    Id INT PRIMARY KEY IDENTITY,
    Name VARCHAR(255) NOT NULL,
    Score INT
)
GO

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO

1. TRUNCATE doesn’t accept a WHERE clause

The first difference is about the WHERE clause.

DELETE accepts a WHERE clause to only delete some records from a table. But, TRUNCATE doesn’t. It deletes all records from a table.

If you try to add a WHERE clause with TRUNCATE, you get “Incorrect syntax near the keyword ‘WHERE’“.

SELECT * FROM dbo.Movies
GO

DELETE FROM dbo.Movies WHERE Name = 'Armageddon'
GO
TRUNCATE TABLE dbo.Movies WHERE Name = 'Armageddon'
                          // ^^^^^^^ Incorrect syntax near the keyword 'WHERE'

2. TRUNCATE resets Identity columns

An identity column is a column with automatic incremented values. It’s used to create key values in tables.

Values for identity columns start from a “seed” value and increase by an “increment” value. You can use any number as seed and any positive or negative number as increment. By default, if you don’t use any seed or increment, it starts from 1 and increments by 1. IDENTITY = IDENTITY(1, 1)

DELETE statements don’t reset identity columns. It means new rows will have the next value in the identity columns. But, TRUNCATE does reset identity columns. The next new row will have the seed in the identity column.

Let’s delete all movies from our sample table and see the Id columns for the new movies.

SELECT * FROM dbo.Movies
GO
DELETE FROM dbo.Movies
GO
INSERT INTO dbo.Movies
VALUES
    ('Avatar', 5)
GO
SELECT * FROM dbo.Movies
GO

Notice how ‘Avatar’ still has the Id = 4 after deleting all movies.

Now, let’s see how TRUNCATE resets the identity column. This time, let’s use TRUNCATE instead of DELETE.

SELECT * FROM dbo.Movies
GO
TRUNCATE TABLE dbo.Movies
GO
INSERT INTO dbo.Movies
VALUES
    ('Platoon', 4)
GO
SELECT * FROM dbo.Movies
GO

Notice the Id of ‘Platoon’. It’s 1 again. When we created our Movies table, we used the default seed and increment.

3. TRUNCATE doesn’t fire Triggers

A trigger is an special type of store procedure that runs when a given action has happened at the database or table level.

For example, you can run a custom action inside a trigger after INSERT, DELETE or UPDATE to a table.

When you work with triggers, you have two virtual tables: INSERTED and DELETED. These tables hold the values inserted or deleted in the statement that fired the trigger in the first place.

Now, back to the differences between TRUNCATE and DELETE. DELETE fires triggers, TRUNCATE doesn’t.

Let’s create a trigger that shows the deleted values. It uses the DELETED table.

CREATE OR ALTER TRIGGER dbo.PrintDeletedMovies
ON dbo.Movies
AFTER DELETE
AS
BEGIN
    SELECT Id 'Deleted Id', Name 'Deleted Name', Score 'Deleted Score'
    FROM DELETED;
END

Now, let’s delete our movies with DELETE and TRUNCATE to see what happens. First, let’s add some new movies and let’s use the DELETE.

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO
DELETE FROM dbo.Movies
GO

Notice the results tab with our three sample movies. Now, let’s use TRUNCATE.

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO
TRUNCATE TABLE dbo.Movies
GO

Now, after truncating the table, we only see in the messages tab the number of rows affected. No movies shown.

Bonus: Rollback a TRUNCATE

We can rollback a TRUNCATE operation. To see this, let’s add our three movies and use a ROLLBACK with some SELECT’s in between.

INSERT INTO dbo.Movies
VALUES
    ('Titanic', 5),
    ('The Fifth Element', 5),
    ('Terminator 2', 5)
GO

BEGIN TRAN
    TRUNCATE TABLE dbo.Movies;

    SELECT * FROM dbo.Movies;
ROLLBACK
GO

SELECT * FROM dbo.Movies;
GO

Notice, the two results. The one inside the transaction, before the rollback, is empty. And the last one, after the rollback, with our three movies.

Voilà! Those are three differences between DELETE and TRUNCATE.

For more content about SQL Server, check how to write Dynamic SQL and Two free tools to format your SQL queries.

A case of primitive obsession. A real example in C#

These days I was working with Stripe API to take payments. And I found a case of primitive obsession. Keep reading to learn how to get rid of it.

Primitive obsession is when developers choose primitive types (strings, integers, decimals) to represent entities of the business domain. To solve this code smell, create classes to model the business entities and to enforce the appropriate business rules.

Using Stripe API

Stripe API uses units to represent amounts. All amounts are multiplied by 100. This is 1USD = 100 units. Also, we can only use amounts between $0.50 USD and $999,999.99 USD. This isn’t the case for all currencies, but let’s keep it simple. For more information, check Stripe documentation for currencies.

The codebase I was working with used two extension methods on the decimal type to convert between amounts and units. Those two methods were something like ToUnits() and ToAmount().

But, besides variable names, there wasn’t anything preventing me to use a decimal instead of Stripe units. It was the same decimal type for both concepts. Anyone could forget to convert things and charge someone’s credit card more than expected. Arggg!

Getting rid of primitive obsession

1. Create a type alias

As an alternative to encode units of measure on variable names, we can use a type alias.

Let’s declare a new type alias with using Unit = System.Decimal and change the correct parameters to use Unit. But, the compiler won’t warn us if we pass decimal instead of Unit. See the snippet below.

using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace GettingRidOfPrimitiveObsession
{
    using Unit = System.Decimal;

    [TestClass]
    public class ConvertBetweenAmountAndUnits
    {
        [TestMethod]
        public void UseTypeAlias()
        {
            decimal amount = 100;
            Unit chargeAmount = amount.ToUnit();

            var paymentService = new PaymentService();
            paymentService.Pay(chargeAmount);

            paymentService.Pay(amount);
            // ^^^^ It compiles
        }
    }

    public class PaymentService
    {
        public void Pay(Unit amountToCharge)
        {
            // Magic goes here
        }
    }

    public static class DecimalExtensions
    {
        public static Unit ToUnits(this decimal d)
            => d * 100;
    }
}

Using a type alias is more expressive than encoding the unit of measure in variable names and parameters. But, it doesn’t force us to use one type instead of the other.

With the Unit type alias, we can still, by mistake, pass regular decimals when we meant units.

Let’s try a better alternative!

2. Create a new type

Now, let’s create a Unit class and pass it around instead of decimal.

In the constructor of the new Unit class, let’s check if the input amount is inside Stripe bounds. Also, let’s use a method to convert units back to normal amounts.

public class Unit
{
    internal decimal Value { get; }

    private Unit(decimal d)
    {
        if (d < 0.5m || d > 999_999.99m)
        {
            throw new ArgumentException("Amount outside of bounds");
        }

        Value = d * 100m;
    }

    public static Unit FromAmount(decimal d)
      => new Unit(d);

    public decimal ToAmount()
        => Value / 100m;
}

Notice, we made the constructor private and added a FromAmount() factory method for more readability.

After using a class instead of an alias, the compiler will warn us if we switch the two types by mistake. And, it’s clear from a method signature if it works with amounts or units.

[TestMethod]
public void UseAType()
{
    decimal amount = 100;
    Unit chargeAmount = Unit.FromAmount(amount);

    var paymentService = new PaymentService();
    paymentService.Pay(chargeAmount);

    // paymentService.Pay(amount);
    // ^^^^ cannot convert from 'decimal' to 'GettingRidOfPrimitiveObsession.Unit'
}

If needed, we can overload the + and - operators to make sure we’re not adding oranges and apples. Decimals and units, I mean.

Records from C# 9.0 offer a shorter notation for classes to replace primitive values. Records have built-in memberwise comparison, ToString() methods and copy constructors, among other features.

We can use custom classes, like the Unit class we wrote, to encode restrictions, constraints, and business rules in our business domain. That’s the main takeaway from Domain Modeling Made Functional.

Voilà! That’s how we can get rid of primitive obsession. A type alias was more expressive than encoding units of measure on names. But, a class was a better alternative. By the way, F# supports unit of measures to variables. And, the compiler will warn you if you forget to use the right unit of measure.

Looking for more content on C#? Check my post series on C# idioms and my C# definitive guide. Working with Stripe, too? Check my post on how to use the Decorator pattern to implement retry logic.

Happy coding!

TIL: Always Use a Culture When Parsing Numeric Strings in C#

This week, I reinstalled the operating system of my computer. The new version uses Spanish instead of English. After that, some unit tests started to break in one of my projects. The broken tests verified the formatting of currencies. This is what I learned about parsing numeric strings and unit testing.

To have a set of always-passing unit tests, use a default culture when parsing numeric strings. Add a default culture to the Parse() and ToString() methods on decimals. As an alternative, wrap each test in a method to change the current culture during its execution.

Failing to parse numeric strings

Some of the failing tests looked like the one below. These tests verified the separator for each supported currency in the project.

[TestMethod]
public void ToCurrency_IntegerAmount_FormatsAmountWithTwoDecimalPlaces()
{
    decimal value = 10M;
    var result = value.ToCurrency();

    Assert.AreEqual("10.00", result);
}

And this was the ToCurrency() method.

public static string ToCurrency(this decimal amount)
{
    return amount.ToString("0.00");
}

The ToCurrency() method didn’t specify any culture. Therefore, it used the user’s current culture. And, the tests expected . as the separator for decimal places. That wasn’t the case for the culture I started to use after reinstalling my operating system. It was ,. That’s why those tests failed.

Use a default culture when parsing

To make my failing tests always pass, no matter the culture, I added a default culture when parsing numeric strings.

Always add a default culture when parsing numeric strings.

For example, you can create ToCurrency() and FromCurrency() methods like this:

public static class FormattingExtensions
{
    private static CultureInfo DefaultCulture
        = new CultureInfo("en-US");

    public static string ToCurrency(this decimal amount)
    {
        return amount.ToString("0.00", DefaultCulture);
    }

    public static decimal FromCurrency(this string amount)
    {
        return decimal.Parse(amount, DefaultCulture);
    }
}

Notice that I added a second parameter of type CultureInfo, which defaults to “en-US.”

Alternatively: Use a wrapper in your tests

As an alternative to adding a default culture, I could run each test inside a wrapper that changes the user culture to the one needed and revert it when the test finishes.

Something like this,

static string RunInCulture(CultureInfo culture, Func<string> action)
{
    var originalCulture = Thread.CurrentThread.CurrentCulture;
    Thread.CurrentThread.CurrentCulture = culture;
    
    try
    {
        return action();
    }
    finally
    {
        Thread.CurrentThread.CurrentCulture = originalCulture;
    }
}

Then, I could refactor the tests to use the RunInCulture wrapper method, like this,

private readonly CultureInfo DefaultCulture
  = new CultureInfo("en-US");

[TestMethod]
public void ToCurrency_IntegerAmount_FormatsAmountWithTwoDecimalPlaces()
{
    RunInCulture(DefaultCulture, () =>
    {
        decimal value = 10M;
        var result = value.ToCurrency();

        Assert.AreEqual("10.00", result);
    });
}

Voilà! That’s what I learned after reinstalling my computer’s operating system and running some unit tests. I learned to use a default culture in all of my parsing methods. If you change your computer locale, all your tests continue to pass?

If you’re new to unit testing, read Unit Testing 101, 4 common mistakes when writing unit tests and 4 test naming conventions. Don’t miss the rest of my Unit Testing 101 series where I also cover mocking, assertions, and best practices.

All tests turned green!

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!