SQL Server Temp Table vs. Table Variable

This post was written by ChatGPT. I used ChatGPT as a natural language search engine to learn more about the modern differences between temp tables and table variables in SQL Server. This is ChatGPT's summary of that conversation.

 

Temp Tables vs. Table Variables in SQL Server: Stop Arguing, Start Measuring

Every SQL Server team eventually has this debate:

Should we use a temp table or a table variable?

And like most long-running arguments in engineering, the answer is:

It depends — but not in the way people think.

Let’s simplify this.

---

First: They Both Live in tempdb

There’s a persistent myth that table variables are “in memory” and temp tables are “on disk.”

That’s not how SQL Server works.

Unless you explicitly use memory-optimized objects, both temp tables and table variables use tempdb.

So if your argument starts with:

“Table variables are faster because they’re in memory…”

It’s already off track.

---

The Real Difference: Statistics

This is the dividing line.

Temp Tables (#Temp)

• Automatically create and maintain statistics

• Support indexes (clustered and nonclustered)

• Give the optimizer accurate cardinality estimates

• Can trigger recompiles when row counts change

In short: the optimizer understands them.

---

Table Variables (@Table)

• Historically had no meaningful statistics

• Often get a low row-count estimate

• Can produce bad join strategies under load

• Don’t trigger recompiles as data volume changes

This is where performance problems come from.

Not storage.
Not memory.
Not logging.

Cardinality estimation.

---

Why This Matters

SQL Server chooses join strategies based on row estimates.

If it thinks a table has 1 row when it actually has 50,000:

• You’ll get nested loops instead of hash joins

• You’ll scan more than you should

• You’ll watch CPU spike and blame the wrong thing

Temp tables give the optimizer better information.
Table variables often hide it.

---

SQL Server 2019/2022 Improvements

Recent versions improved table variable estimation (deferred compilation), which helps.

But it doesn’t magically make them equal to temp tables in complex workloads.

The rule still holds:

If the row count is non-trivial or the query is complex, temp tables usually produce more stable and predictable plans.

---

When Table Variables Make Sense

They’re not evil. They’re just specialized.

Use them when:

• Row counts are small (think dozens, maybe low hundreds)

• You’re inside a function and can’t use temp tables

• The data is truly simple and short-lived

• You want limited scope and simpler lifecycle

They shine in small, controlled scenarios.

They struggle in large, evolving pipelines.

---

When Temp Tables Are the Right Tool

Use temp tables when:

• You’re joining multiple large sets

• You need indexing

• You need reliable cardinality estimates

• You reuse intermediate results multiple times

• Plan stability matters

Yes, there’s tempdb overhead.

But modern SQL Server versions have significantly improved tempdb performance. For most real systems, the bottleneck isn’t tempdb allocation — it’s bad plans.

---

The Pattern I Trust

If I don’t know the final row count yet?

I default to a temp table.

Because I’d rather pay a small, predictable cost up front than debug a production incident caused by a bad estimate.

---

The Anti-Pattern

The worst pattern is this:

DECLARE @Results TABLE (...);

INSERT @Results
SELECT ...
FROM LargeTable
JOIN OtherLargeTable ...

It works in dev.
It works with 200 rows.
It melts under 200,000.

Then someone adds an index hint.
Then someone rewrites it.
Then you’re refactoring under pressure.

All because the optimizer was flying blind.

---

The Decision Framework

Instead of arguing syntax, ask:

1. How many rows might this hold?

2. Will I join against it?

3. Will I reuse it?

4. Does plan stability matter here?

If the answers trend toward “large / complex / reused,”
use a temp table.

If they trend toward “small / simple / scoped,”
a table variable is fine.

---

Final Thought

This isn’t about micro-optimizing.

It’s about giving the optimizer the information it needs to do its job.

Temp tables give it visibility.
Table variables limit it.

Pick accordingly.

And as always:

Measure first.
Optimize second.

 

AWS CDK "Stack with id null does not exist"

We host in AWS at work, which allows us to use the AWS CDK for IaC and CI/CD Pipelines (so many acronyms). Our DevOps team crafted the original CDK pipeline files for us and handed them over to live in our repository, but when it came time to change the architecture of our application I figured I could use what we already had combined with a little help from Copilot and/or ChatGPT and fix it up to do what I need. I was eventually able to do that, but not without a major challenge in the form of a cryptic error message that took me nearly two weeks to figure out and resolve.

TL;DR: AWS caps the length of the handler of a Lambda's build to 128 characters. I had 129 characters so it failed, but didn't give me a useful error message. I had to pull the template from the pipeline and try to manually create it via the UI to get to the error message.

The architecture change was to go from one single Lambda to two Lambdas and an S3 bucket where the first Lambda accepts a file upload request and generates a pre-signed URL to upload the file to the S3 bucket. The client uploads the file using the pre-signed URL and the second Lambda is notified to process the file. It's a fairly straightforward design and all the pieces to implement it exist directly in AWS. It should have been easy.

I added everything and ran the pipeline and it failed with the message in the title: "Stack with id null does not exist". There was another error with the message "ChangeSet [arn:aws:cloudformation:{region}:{target account}:changeSet/PipelineChange/{guid}] does not exist", but no additional information. So the pipeline was running, which should have generated a stack and a changeset on that stack, but neither was being created. Why not? That's where my two weeks went. describe-events didn't tell me anything. Logs didn't tell me anything. I was getting nowhere so I started over and just created the S3 bucket: success. No issues whatsoever. I was able to generate a pre-signed URL and upload a file to it. So I added the second Lambda back in. More failures.

I don't remember all the troubleshooting steps, but at various points I found the bucket creation failed because it already exists and learned that bucket names are globally unique. Not per account or region or customer/subscription. Globally unique. Solved that and still had the same error. ChatGPT or Copilot thought there must be a circular reference so I revamped all the new CDK files to make sure there wasn't. Same failure. So I started over again. This time I just added the second Lambda and did nothing with the bucket and immediately got the same failure again. Obviously, the problem was in the definition of the new Lambda, right? Yes, but no.

The AWS CLI allowed me to generate the template that would be used by the pipeline to create the stack, and then validate that template. Everything was fine. Validation passed, but when I ran the actual pipeline, it failed. Then ChatGPT suggested pulling the template that was generated during the pipeline build (using `aws cloudformation get-template --change-set-name PipelineChange --stack-name {stack name} --region {region} --query 'TemplateBody' --output json > {filename}.json`) and using the template to manually create the Lambda via the AWS UI. That finally led me to an error message that indicated my resource value was too long. My handler was 129 characters and the limit is 128 characters. One too many characters cost me two weeks because AWS wasn't giving me a useful error message.

Extension Methods in C#

I was explaining extension methods in C# to somebody and realized it was a good topic for a blog post, so here we are.

Extension methods are a way to enhance (or extend) an existing object. If you've done any significant coding with C# I can virtually guarantee you've already used extension methods that were built-in to the framework. Things like " banana ".Trim(), which removes leading and trailing whitespace from the string. But you can also create your own extension methods and it's actually pretty easy to do.

First you need to identify what you're trying to extend. For example, if you always want to safely convert a string to an integer and return -1 when the string is not an integer, would you be extending strings or ints? The answer is strings.

 

I actually wrote a separate post with some examples of string extensions that I use pretty frequently. Basically, we're extending (hence the name) the framework's built-in capabilities so we can manipulate an object (type) more easily. Extension methods are a great way to avoid littering your application with helper methods like "GetTrimmedString(string toTrim)". But you want to be aware of unnecessarily polluting your code base with extension methods that are only used in one place.

Fuzzy Matching in C#

I've been working on a project where we allow users to provide us a .csv file and tell us about it, like what kind of data is in which column. Think of it like this: we have a fixed list of data points that we need to have mapped and the user is going to give us a file that contains all the data, but we don't know which column will have which piece of information so the user has to tell us. If we need First Name, Last Name, and Age, the user just has to tell us which column in their file has which value. We want to make that as easy as possible so we check the header in their file to see if we can deduce that information so the user doesn't have to. It's easy if the columns are named First Name, Last Name, and Age, but it gets a little trickier when they're named FName, LName, and AgeInYears. That's where fuzzy matching comes in.

We decided to use a library called Fuzzy Sharp (there are several with that name so be cautious when choosing one) to do the heavy lifting for us. It's pretty simple to use, but there are lots of options and the "documentation" isn't super clear. Fortunately in 2025 we have AI to help us make sense of the nonsensical!

In our first attempt we checked for an exact match manually, then stripped off special characters from both sets for comparison and checked for another "exact" match, then finally relented and used the FuzzySharp library to check for a match:

 

private int FindSourceColumnNumber(string destinationName, IEnumerable<string> columnNames)
{
    var exactMatch = columnNames.Select((name, index) => new { Name = name, Position = index })
        .FirstOrDefault(a => a.Name.Equals(destinationName, StringComparison.OrdinalIgnoreCase));
 
    if (exactMatch is not null)
    {
        return exactMatch.Position;
    }
 
    var strippedColumnNames = columnNames
        .Select((name, index) => new {
            Name = name,
            Position = index,
            StrippedName = SpecialCharacterReplacementRegex().Replace(name, "")
        });
    var strippedDestinationName = SpecialCharacterReplacementRegex().Replace(destinationName, "");
 
    var strippedMatch = strippedColumnNames
        .FirstOrDefault(name => string.Equals(name, strippedDestinationName, StringComparison.InvariantCultureIgnoreCase));
 
    if (strippedMatch is not null)
    {
        return strippedMatch.Position;
    }
 
    var fuzzyMatch = strippedColumnNames
        .Select((name, index) => new { Name = name, Position = index, Score = Fuzz.Ratio(strippedDestinationName, name) })
        .Where(x => x.Score >= 70)
        .OrderByDescendinx(x => x.Score)
        .FirstOrDefault();
 
    return fuzzyMatch is not null ? fuzzyMatch.Position : -1;
}

That was fine, but it turned out there was a much better way to do this by taking advantage of the FuzzySharp library. We were able to simplify our code to just this:

private int? FindSourceColumnNumber(string destinationName, IEnumerable<string> columnNames)
{
    var exactMatch = columnNames.Select((name, index) => new { Name = name, Position = index })
        .FirstOrDefault(a => a.Name.Equals(destinationName, StringComparison.OrdinalIgnoreCase));
 
    if (exactMatch is not null)
    {
        return exactMatch.Position;
    }
 
    // Step 2: Fuzzy match (slower, but more flexible)
    var fuzzyMatch = columnNames
        .Select((name, index) => new { Name = name, Position = index, Score = Fuzz.Ratio(destinationName, name, PreprocessMode.Full) })
        .Where(x => x.Score >= 70)
        .OrderByDescending(x => x.Score)
        .FirstOrDefault();
 
    return fuzzyMatch is not null ? fuzzyMatch.Position : null;
}

Adding the PreprocessMode.Full parameter tells FuzzySharp to strip out non-alphanumeric characters on its own. Since the authors of that library focused on speed and efficiency we've basically decided to trust that they're doing a better job with it than we were when we were using the Regex match capability. There's a ton more that FuzzySharp can do, but this is the extent of what we needed so I didn't delve too deep into its other features.

Distinct Array Values in JavaScript

Sometimes when you're working with collections (in any language) you find yourself needing to get just the distinct list of items in the collection. JavaScript has an object called a Set that makes doing this with arrays really, really easy.

Start with an array of primitives:

let arr = [1, 2, 3, 4, 5, 5, 4, 3, 2, 1, 1, 1, 1, 3, 4, 5, 6]

From that array, create a Set: 

let set = new Set(arr)

Convert it back to an array (this step is optional because you may find working with the Set directly meets your needs):

let result = Array.from(set);

That's it. Now you have an array of distinct primitive values. If you have an array of complex objects, you can use this same trick, but it's a little... trickier.

let arr = [{id:1,name:'Frodo',relatives:[{id:2,name:'Bilbo'}]},{id:1,name:'Frodo',relatives:[{id:2,name:'Bilbo'}]}];
let set = new Set(arr.map(JSON.stringify));
let result = Array.from(set);

Setting Up esLint for an Angular 19 Project

The angular-eslint package makes setting up linting in an Angular project pretty easy, but due to some recent changes there are a few new steps I had to work out to get it working so I figured I'd put them here for next time.

1. Install the angular-eslint package: ng add angular-eslint
2. Rename the generated eslint.config.js to eslint.config.mjs
3. Change the 3 require statements at the top of the config file to import statements
1. const eslint = require("@eslint/js"); => import esLint from '@eslint/js';
2. const tseslint = require("typescript-eslint"); => import tsEsLint from 'typescript-eslint';
3. const angular = require("angular-eslint"); => import angular from 'angular-eslint';
4. Change the module.exports to export default
1. module.exports = tseslint.config( => export default tsEsLint.config(
5. In package.json, add a "type" setting with a value of "module"

At this point, esLint should be all setup. It actually works before any of this, but I wanted to use a plugin (more on that in a moment) and that required me to make these changes.

So, about that plugin. esLint made the decision in 8.53 to stop including stylistic rules as part of the linting process. I understand the decision, but I don't like it. Fortunately, there's a plugin for stylistic linting that you can add and it's pretty easy now that the above changes have been implemented.

1. Install @stylistic/eslint-plugin-ts package: npm install @stylistic/eslint-plugin-ts
2. Import the plugin in eslint.config.mjs
1. import stylisticPlugin from '@stylistic/eslint-plugin-ts'
3. In eslint.config.mjs, add a node as a sibling to the "files" node for plugins:
   plugins: {
         stylistic: stylisticPlugin
       },
4.  Add the individual style rules to the rules node of eslint.config.mjs
1. For example, to enfore the 1 true brace style rule, add "stylistic/brace-style": ["error", "1tbs"],
   

That's it! esLint should be all good to go. If you install a live linter extension in your working environment (like the esLint plugin for VS Code) you should see all your lint rules - including stylistic ones - applied.

JavaScript Boolean Object

It has been a looooooong time since I posted. Not because I haven't had a ton to post about, but because at my core I'm super lazy. Anyway, here's a quick hitter on how to check if every property on an object is true using the Boolean function in JavaScript.

return Object.values(this.data.modal.validation).every(Boolean);

You do need to watch out for a couple of things if you want to use this. First, because of type coercion in JavaScript, an object that has pretty much any kind of data on a property will count as true. So if you had an object with a name property that had a value of "Mickey", that's true in this scenario. Second (and related), if you have a property with a value of 0 it will count as false, again due to type coercion.

I'm using this to check an object that only has boolean properties and it works very well.