Matthew Pavey | Faith, Family, Code

You add the index. The column is right there in the WHERE clause. And the query still crawls, because the execution plan shows an index scan where you expected a seek, SQL Server reading every row in the table to answer a question the index should have settled in a few pages. Most of the time the query itself is the culprit. Something in the WHERE clause hid the column from its own index.

There is a word for this: SARGable, from "search argument." A predicate is SARGable when the optimizer can satisfy it by seeking into an index. Break that, and the engine has no choice left but to check your condition against every row it can find, which is the scan you did not want. The usual way to break it is to do something to the indexed column instead of to the value you are comparing it against.

Dates are the most common example. This looks perfectly reasonable:

SELECT OrderId, CustomerId, Total
FROM dbo.Orders
WHERE YEAR(OrderDate) = 2025;

But YEAR() is a function wrapped around the indexed column, so the engine has to compute YEAR(OrderDate) for every row before it can compare anything, and the index on OrderDate sits there unused. Ask the same question as a range and the column comes back out into the open:

SELECT OrderId, CustomerId, Total
FROM dbo.Orders
WHERE OrderDate >= '2025-01-01'
  AND OrderDate <  '2026-01-01';

Same answer, but now it is a seek. The index points straight at the rows in that window instead of the engine reading the whole table to find them.

The sneakier version has no function anywhere in sight. Say ProductCode is a varchar column with an index on it, and you query it like this:

SELECT ProductId, Name
FROM dbo.Product
WHERE ProductCode = @code;

Clean. Nothing wrapped around the column. But if @code arrives as an nvarchar, and from a .NET application it usually does, SQL Server has to reconcile the two types, and nvarchar outranks varchar in precedence. So the engine quietly converts the column to match the parameter, not the other way around, and you end up with the equivalent of CONVERT(nvarchar, ProductCode) on every row. A function on the column again, except this time you did not write it, and the query looks innocent while the plan shows a scan.

The fix is to send the parameter as the type the column already is. In raw ADO.NET that means setting the parameter's SqlDbType to VarChar. In Dapper, wrap the value so it crosses as ANSI:

var rows = conn.Query<Product>(
    "SELECT ProductId, Name FROM dbo.Product WHERE ProductCode = @code",
    new { code = new DbString { Value = code, IsAnsi = true, Length = 20 } });

One property, and the seek comes back. This one is worth knowing because it hides so well: the SQL reads fine, the index exists, and the only visible symptom is a plan that scans a table it should be seeking. I have watched people rewrite a query five different ways looking for the problem when the problem was the parameter type all along.

Wildcards carry the same trap in miniature. LIKE 'John%' can seek, because SQL Server knows where the matching range begins. LIKE '%son' cannot, because a leading wildcard gives the index no starting point to seek to, so it reads everything. A trailing wildcard is an index lookup; a leading one is a full scan. Worth remembering before you build a search feature on a column that way.

The rule underneath all of these is the same. Keep the indexed column by itself on its side of the comparison, and move the work over to the other side. When you genuinely need to filter on a transformation of a column, say a date pulled out of a timestamp or a value extracted from a longer string, SQL Server will let you index the transformation itself through a computed column, so a seek is back on the table. But that is the second move. The first is to check whether you are hiding the column from its own index without meaning to.

All of this shows up the moment you read the actual execution plan. Seek or scan, in plain words, right there on the operator. When a query runs slower than its indexes say it should, that plan is the first place I look, and a scan sitting where a seek belongs almost always traces back to something done to the column that did not need doing.

There are songs that make you tap your foot, and there are songs that take you to your knees to worship. Lauren Daigle's "Dry Bones" is the second kind for me. It is one of the songs I come back to again and again when I want to worship, because it does not just describe God's power, it makes me feel small in front of it, in the way you are supposed to feel small in front of something almighty.

The song is built on one of the strangest and most vivid scenes in the Old Testament. The prophet Ezekiel is set down by God in the middle of a valley, and the valley is full of bones. Not fresh graves. Bones, scattered and picked clean and, in Ezekiel's own word, very dry. This is death that has been death for a long time. And then God asks him a question that has no reasonable answer:

And he said to me, "Son of man, can these bones live?" And I answered, "O Lord GOD, you know." (Ezekiel 37:3, ESV)

I love Ezekiel's answer, because it is so honest. He does not say yes, and he does not say no. Looking out over a field of the long dead, "you know" is the only thing a sane person could say. It is not a failure of faith. It is faith admitting that the outcome is entirely in God's hands, which happens to be exactly where it belongs.

Then God tells him to preach to the bones. To speak life over the most obviously dead thing imaginable. And as Ezekiel obeys, there is a rattling across the valley, and bone comes to bone, and sinew and flesh and skin, and finally breath, until what was a graveyard is standing up alive. The vision is about Israel, a people who had given up, who said their hope was gone and they were cut off. God's answer to that despair is not a pep talk. It is a resurrection:

And I will put my Spirit within you, and you shall live. (Ezekiel 37:14, ESV)

This is the passage Daigle's song lives inside, and you can hear the whole arc of it in the music, the dryness, the question, and then the breath. I will not quote the lyrics here, but I will do something better and link you to it. Turn it on with the volume up, ideally when you have a moment to actually listen rather than have it on in the background.

Lauren Daigle, "Dry Bones"

Here is why this song resonates with me. There was a stretch of my life, years ago, that was as dark as anything I have known. I was a Christian by then, at least on paper, but I was lost, without purpose, and worn down to nothing. I was about as dry as those bones. I could not preach myself back to life any more than a skeleton in that valley could, and I had finally run out of trying.

What changed things for me was not a formula, not a strategy, and not a list of rules I could follow to earn it. Rather, it was surrender. One night, at my lowest point, I got down on my knees and gave God the whole thing, my life, my future, all of it, because I had nothing left to hold onto and no strength left to hold on with. I did not fix myself. I quit trying to, and asked Him to do what only He could. The next morning, my Bible was open beside me to the following passage:

I waited patiently for the LORD; he inclined to me and heard my cry. He drew me up from the pit of destruction, out of the miry bog, and set my feet upon a rock, making my steps secure. He put a new song in my mouth, a song of praise to our God. (Psalm 40:1-3, ESV)

He drew me up from the pit. He set my feet on a rock. He put a new song in my mouth. That is not poetry to me, it is autobiography, because I lived it. I was the dead thing made alive, and I did nothing to earn it except stop pretending I could save myself. That is the part Daigle's song draws me into worship with every time.

That is what moves me to worship. Not God as a comforting idea, but God as the one who stands over a valley of the dead and asks whether they can live, already knowing that at His word they will stand up by the thousands. He did it for a field of bones in Ezekiel's vision. He did it for me on a night I had given up. When I remember that this is who God is, the only reasonable posture left is the one the song puts me in. Small, grateful, and alive.

I run scheduled jobs on several systems that clean out data past its retention window. Anything older than ninety days, or a year, depending on the system, gets removed overnight. On the quiet tables that is nothing to think about. On the busy ones, the logging and event tables especially, "older than ninety days" can mean millions of rows, and that is where a delete stops being a one liner and starts being something you have to plan.

The obvious version is a single statement:

DELETE FROM dbo.EventLog
WHERE CreatedUtc < @Cutoff;

It runs fine in development against a few thousand rows. Against a few million in production, it can take the whole table down with it.

The reason is that this is one transaction. SQL Server acquires locks as it works through the rows, and once a single statement holds about five thousand locks on one table, it escalates to a lock on the entire table. Every other query that needs those rows now waits behind your cleanup. The transaction log grows the whole time, because nothing the delete touches can be cleared from the log until the statement commits; delete ten million rows and the log has to carry all ten million rows of change before it can release any of it. And if anything interrupts the statement partway through, SQL Server rolls the entire thing back, which costs about as much as the delete did in the first place.

The fix is to stop doing it all at once. Delete in small batches, let each batch commit on its own, and loop until nothing is left:

DECLARE @BatchSize INT = 4500;
DECLARE @Cutoff DATETIME2 = DATEADD(DAY, -90, SYSUTCDATETIME());
DECLARE @Rows INT = 1;

WHILE @Rows > 0
BEGIN
    DELETE TOP (@BatchSize)
    FROM dbo.EventLog
    WHERE CreatedUtc < @Cutoff;

    SET @Rows = @@ROWCOUNT;

    WAITFOR DELAY '00:00:01';
END

A batch of a few thousand keeps each statement under that five thousand lock threshold, so the table stays available to everyone else in the gaps between batches. @@ROWCOUNT tells the loop when the last batch came back empty, which is how it knows to stop. The one second WAITFOR is deliberate: it gives the disk and the log a moment to catch up, and on a busy system it leaves room for other queries instead of pounding the table in a tight loop.

One temptation to resist: do not wrap the whole loop in a single BEGIN TRANSACTION and COMMIT to make it tidy. That folds every batch back into one enormous transaction, and you have written the slow version with extra steps. Each DELETE statement is already atomic by itself. Let it commit by itself.

There is one more piece that trips people up, and it is the reason a batched delete sometimes still fills the log. Under the simple recovery model, SQL Server reclaims the log space after each batch checkpoints, so the log stays roughly flat no matter how many rows you remove. Under the full recovery model, that space is not reclaimed until a log backup runs; however, that means your log backup frequency, not your batch size, becomes the real limit. If your log backups run hourly and your delete runs for two hours, the log holds two hours of deletions no matter how neatly you batched them. On the systems where I run the big cleanups, I make sure the batch cadence and the log backup schedule actually agree with each other.

None of this helps if the column in your WHERE clause is not indexed. Each pass through the loop has to find its next few thousand rows to delete, and without an index on the filter column it scans the table to do that, every single time. An index on the date column turns each of those scans into a quick seek:

CREATE NONCLUSTERED INDEX IX_EventLog_CreatedUtc
    ON dbo.EventLog (CreatedUtc);

For a job that runs unattended at two in the morning, being well behaved counts for more than being fast. A batched delete does take longer on the clock than one big statement, and that is the trade you are making on purpose: it gives up some speed so the rest of the server keeps serving while the cleanup works. If you have a nightly job that clears more than a few thousand rows at a time, it is worth rewriting the delete before the table it cleans grows into the one that wakes somebody up.

A single sign-on application I work on used to calculate Active Directory password expiry by taking pwdLastSet, adding the domain's maxPwdAge, and using the result. That worked until the organization introduced fine-grained password policies, which allow different accounts to follow different expiry rules within the same domain.

Some accounts began receiving warnings for expiry dates that did not apply to them. The calculation was correct for the default policy, but not necessarily for the account being checked.

The fix is to stop calculating the date. Active Directory exposes msDS-UserPasswordExpiryTimeComputed, a constructed attribute that the domain controller calculates when it reads the account. It applies the password policy that actually governs that account, including fine-grained policies.

Application code still needs to request the attribute explicitly. A missing constructed attribute does not necessarily raise an exception, so the search can appear to work while quietly returning no expiry value.

$auth = [System.DirectoryServices.AuthenticationTypes]::Secure -bor
        [System.DirectoryServices.AuthenticationTypes]::SecureSocketsLayer

$searchRoot = New-Object System.DirectoryServices.DirectoryEntry("LDAP://dc.example.com:636/DC=example,DC=com")
$searchRoot.AuthenticationType = $auth

$searcher = New-Object System.DirectoryServices.DirectorySearcher($searchRoot)
$searcher.Filter = "(&(objectCategory=person)(objectClass=user)(sAMAccountName=jdoe))"

[void]$searcher.PropertiesToLoad.Add("msDS-UserPasswordExpiryTimeComputed")

$result = $searcher.FindOne()
$raw = [Int64]$result.Properties["msds-userpasswordexpirytimecomputed"][0]

if ($raw -eq [Int64]::MaxValue) { "Password never expires" }
elseif ($raw -eq 0)             { "Must change at next logon" }
else                            { [DateTime]::FromFileTime($raw) }

There are three values worth handling before converting the result to a date. Int64.MaxValue means the password never expires. A value of zero means the user must change the password at the next logon. Any other value is a Windows file time and can be converted with DateTime.FromFileTime.

I compared both calculations against the live directory instead of trusting two test accounts. The comparison covered 4,230 accounts across three domains. The old and new results agreed for 4,041 accounts. Of the 121 accounts governed by fine-grained policies, 45 had been assigned a date that was wrong by 305 days. Another 123 accounts were off by exactly one hour across daylight saving transitions, because the old code used local calendar arithmetic while the domain controller calculated the value from UTC file-time data.

One thing to remember. Reading the attribute through a DirectorySearcher returns it as a System.Int64, which is why the cast above works. Reading the same attribute through a DirectoryEntry property returns it as an IADsLargeInteger COM object instead, with separate high and low parts, so that path has to convert the value before comparing it. The reader below goes through a ConvertLargeInteger helper for exactly that reason.

Private Function ReadComputedExpiry(entry As DirectoryEntry) As DateTime?

    Const AttributeName As String = "msDS-UserPasswordExpiryTimeComputed"

    If Not entry.Properties.Contains(AttributeName) Then
        Return Nothing
    End If

    Dim raw As Long = ConvertLargeInteger(entry.Properties(AttributeName).Value)

    If raw = Int64.MaxValue Then
        Return DateTime.MaxValue    ' password never expires
    End If

    If raw = 0 Then
        Return DateTime.MinValue    ' must change at next logon
    End If

    Return DateTime.FromFileTime(raw)
End Function

The order matters. Check the special values before converting them. Trying to turn the never-expires value into a date throws an exception.

If the constructed attribute is absent, the application should treat that as a directory-read problem rather than as "never expires." A fallback may still be necessary on an authentication path, but it should be visible in logs or metrics so a missing attribute does not look like a valid result.

Dim computed As DateTime? = ReadComputedExpiry(entry)

If computed.HasValue Then
    Return computed.Value
End If

If Not lastSet.Equals(DateTime.MinValue) Then
    Return lastSet.AddDays(GetDefaultMaxAgeDays())
End If

Return DateTime.MaxValue

The fallback preserves the old behavior when Active Directory cannot provide the computed value. It is no longer the normal path, though, and the application no longer has to guess which password policy applies to each account.

If your code adds days to pwdLastSet, replace that calculation with a read of msDS-UserPasswordExpiryTimeComputed. Ask the domain controller for the answer it already knows.

For most of history, everything known about Pontius Pilate came from words on a page. The Gospels put him at the center of the most consequential trial ever held. Josephus and Philo mention him. Tacitus gives him a single line, noting that "Christus" was executed under him. Real sources, all of them, but all of them written accounts passed down through later copies. A determined skeptic could shrug and say Pilate was a literary device, a name the Gospel writers needed to move the story along. There was nothing you could put your hand on.

That changed in the summer of 1961.

An Italian team was digging at Caesarea Maritima, the coastal city Herod the Great built and the Romans used as their administrative capital in Judea. They were working through the ruins of the old theater when they turned over a block of limestone that had been reused, centuries after it was carved, as a step in a staircase. Someone had taken a Roman dedication stone and used it as building material. On its worn face were four lines of Latin, including the words "Pontius Pilatus" and the title that placed him: prefect of Judea.

That is not a manuscript about Pilate. It is not a later historian's recollection of him. It is a stone cut during his own administration, naming him and giving him the same office the Gospels do. The inscription is the first archaeological evidence we have that directly identifies the man behind the name.

Careful scholars had never quite said Pilate was invented. They said his existence lacked direct corroboration. The literary sources were real, but they had nothing solid underneath them. The Caesarea stone supplied that missing piece. One writer on the subject put it plainly: the fact that Pilate governed during Jesus' time "need not be doubted."

The inscription records Pilate dedicating a building, a Tiberieum, in honor of the emperor Tiberius. It was a bureaucrat's monument to his boss, political flattery carved into limestone. Pilate was not thinking about future Christians or future critics. He was doing his job, keeping the emperor pleased and putting his name on the work. The stone's value to later readers was accidental. Nobody made it for us.

The stone does not convert anyone, and it is not meant to. Faith does not stand or fall on a piece of limestone in the Israel Museum. It does remind us, though, that the Gospels are set in a real province, under real governors, within a chain of history you can uncover with a trowel. The writers were talking about people and places that existed outside the page.

He wanted his name fixed in stone. Within a few centuries, the monument had been broken up and used as a stair, walked over by people who had no idea whose name lay beneath their feet.

Pilate secured his place in the Gospels by asking a question he did not wait to hear answered: what is truth? He thought he was the judge that morning, deciding what to do with the man in front of him. Christians have always believed it was the other way around.

I recently built an integration for a client that keeps HubSpot contacts in sync with an internal user list. A console job runs every five minutes, compares the two sides, and pushes changes to HubSpot through their API. The job itself lives in a long-serving .NET Framework application; the samples below use current .NET for clarity, but the pattern is identical. Simple enough on the surface, and one easy trap shows up in the first version of a job like this. It looks like this:

var contact = await FindContactByEmailAsync(user.Email);

if (contact is null)
{
    await CreateContactAsync(user);
}
else
{
    await UpdateContactAsync(contact.Id, user);
}

Find the contact by email. Create it if missing, update it if found. Reasonable, readable, and the happy-path tests pass. Then someone gets married, or the company changes domains, and a user's email address changes. Now the search for the new address finds nothing, so the job creates a brand new contact. The old contact is still sitting in HubSpot under the old address, marked active, holding all the history. You have a duplicate and an orphan, and the job that caused it reports success.

The root mistake is treating the email address as the identity. It is not. It is a property of the identity, and properties change. HubSpot already knows this, which is why every contact has a stable record id. That id is what your system should remember. The first time you create or find a contact, store the HubSpot id in your own database next to the user. From then on, the normal sync path does not search at all. It goes straight to the record:

if (user.HubSpotContactId is null)
{
    user.HubSpotContactId =
        await ResolveContactIdByEmailAsync(http, user.Email)
        ?? await CreateContactAsync(http, user);

    // persist user.HubSpotContactId to your database here
}

await http.PatchAsJsonAsync(
    $"/crm/v3/objects/contacts/{user.HubSpotContactId}",
    new
    {
        properties = new
        {
            email = user.Email,
            firstname = user.FirstName,
            lastname = user.LastName
        }
    });

Notice that a rename is no longer a special case. The email address is just one more property in the PATCH, updated in place on the same contact, with every association and every piece of history intact. When I proved this out against the live portal, the update stayed on the existing contact. The old address no longer identified that contact, and nothing new was left behind. Search by email happens once in a contact's life, on first sight, and here is that piece:

private static async Task<string?> ResolveContactIdByEmailAsync(
    HttpClient http, string email)
{
    var body = new
    {
        filterGroups = new[]
        {
            new
            {
                filters = new[]
                {
                    new { propertyName = "email", @operator = "EQ", value = email }
                }
            }
        },
        limit = 1
    };

    var response = await http.PostAsJsonAsync(
        "/crm/v3/objects/contacts/search", body);
    response.EnsureSuccessStatusCode();

    var result = await response.Content
        .ReadFromJsonAsync<ContactSearchResult>();

    return result?.Results?.FirstOrDefault()?.Id;
}

The stored id is not a substitute for error handling. If the contact was deleted or merged in HubSpot, the update can come back not found, and that is a signal to stop and recover deliberately. An automatic create at that moment is how you manufacture the exact duplicate this pattern exists to prevent. Likewise, if the new email already belongs to a different contact, the sync should stop and report the conflict for review rather than guess.

One more rule from the same project: for this integration, the sync never deletes a contact. When a user is deactivated on our side, the job flips a status property in HubSpot and walks away. Part of the reason is practical: deletion in a CRM destroys history, breaks associations, and cannot be taken back by a job that runs unattended at three in the morning. And part of it is just good manners: those contact records belong to the marketing team, and a background job has no business destroying another team's data. Deactivation is reversible, auditable, and honest about what actually happened, which is that a person became inactive, not that they never existed. Any legal deletion or retention requirement belongs in its own deliberate workflow, decided by people rather than a timer.

None of this is specific to HubSpot. Salesforce, Dynamics, Mailchimp, any system your code talks to: if the remote side has a stable id, store it, and treat every human-readable field as changeable, because sooner or later all of them are. The integrations that age well are the ones that remember who someone is, not just what they were called.

V for Vendetta sits at the very top of my movie list, and if you have seen it, you can probably guess the scene that put it there. Evey is locked in a cell, head shaved, being broken one interrogation at a time. Then a letter reaches her, written on toilet paper by a prisoner named Valerie. The letter comes from the graphic novel by Alan Moore and David Lloyd; the film adapts and condenses it, then gives it a voice that has stayed with me for years.

Valerie was an actress. She fell in love with Ruth, and for three years their London flat smelled of roses. As she remembers it:

For three years I had roses, and apologised to no one.

Then America's war grew worse and eventually came to London. After that, there were no roses anymore. Not for anyone. The regime made being different dangerous. They took Ruth while she was out buying food, and it was not long until they came for Valerie.

Now Valerie sits in a cell writing her life story on toilet paper for a stranger she will never meet. She tells that stranger about Sarah, Christina, her parents, Ruth, and the best years of her life. Then she closes by telling that stranger she loves them.

The letter does not reach Evey by accident. V later tells her that Valerie wrote it just before she died, and that he delivered it to Evey as it had been delivered to him. The letter becomes more than a confession. It becomes a handoff, one person's last act of integrity entrusted to another.

The center of the letter is a single idea:

Our integrity sells for so little, but it is all we really have. It is the very last inch of us. And within that inch, we are free. We must never lose it or give it away. We must never let them take it from us.

Everything else can be taken from a person. That inch can only be surrendered. The regime took her freedom, her future, and Ruth, but it never got that inch. She faces death with it intact, apologising to no one.

A lot of that is simply true. There is something in a human being that no power on earth can take. No one can take your conscience by force. You can only give it away. And Valerie is right about what it is worth. The world will buy it from you for almost nothing, and it is still worth more than everything you own. Jesus asked the same question a different way: what can a man give in exchange for his soul (Mark 8:36-37)?

Even the villains in this story teach something. The government wraps itself in the language of faith while it tortures people. When you see that, do not blame the faith. Blame the people wearing it as a costume. Anyone cruel to a person made in the image of God is showing you who they really serve, and it is not God.

But the letter can only carry its hope so far. Valerie hopes the stranger who finds her letter escapes that place. She hopes the world turns and that things get better. But by her own words, every last inch of her will perish except one. At first, that final inch seems to survive only as ink on paper in a stranger's hands. If death gets the last word, her integrity is beautiful, and it is also doomed.

But the film does not let death have the last word. V carries the letter forward. Evey receives it. What Valerie guarded in a cell becomes strength in another person. The inch survives not because Valerie escaped death, but because what she refused to surrender outlived her.

The gospel agrees with Valerie about the inch and disagrees about the ending. Jesus said that whoever clings to his life will lose it, and whoever hands his life over to Him will keep it (Mark 8:35). So the inch is real. The question is whether there is anyone you can trust to hold it.

Valerie guarded hers in isolation, all the way to the end, and I honor her for it. But listen to her hope again. The world turning. Things getting better. Roses again. That is almost a prayer, and there is a promise that answers it, from the One who makes all things new. Because of the resurrection, the last inch is not a keepsake. It is a seed.

Watch the scene if you never have, or even if you have. And if the story grabs you the way it grabbed me, there is a fan shrine to the book that has been maintained since 1997, which tells you I am not the only one this letter impacted. Then ask yourself the letter’s real question, the one Valerie answered in that cell: what part of you will you refuse to surrender, and who will you trust with it?