DEV Community: Vishnu KN

TIL: Temp table vs CTE

Vishnu KN — Mon, 03 Nov 2025 05:01:14 +0000

Simple Analogy

Imagine you’re a chef preparing dishes for a party.

🍲 Using a Temp Table

You chop a big bowl of onions once and keep them in a bowl (#TempTable).
Then, whenever a recipe needs onions, you just scoop some out.
✅ Efficient and fast — prep once, reuse many times.

🍳 Using a CTE

Instead of keeping the chopped onions, you follow the “chop onions” step every single time a recipe calls for onions.
❌ Wasteful — you repeat the same work multiple times.

IMPORTAN NOTE: A CTE that is created exists only for the duration of the execution of one statement and within the scope of that statement. So if you are executing multiple statements within a query the use of CTE can bloat up the memory resources used.

Going back to the chef analogy, there is one instance where the CTE option of “chopping onions” step every single time trumps the temp table option of chopping a big bowl of onions once and keeping them in a bowl. That is, if you are cooking just one dish.
In SQL terms, if your derived data is being used just once, then it does not make sense to create a temporary table.

TIL: Transactions in .NET

Vishnu KN — Fri, 17 Oct 2025 18:02:20 +0000

When your app talks to a database, it might have to do multiple things at once. Sometimes, these things might be connected, and separating them can cause problems.

Consider an online store where the user purchases something from his balance. For a single purchase, we might want to

Deduct from the user's balance in one table
Add a record of the order to another.

If one of these actions fail, it does not make sense to proceed with the other since it leads to data inconsistencies and confusion. In such cases, transactions come in handy.

A transaction is like a promise to the database: "Do all of these actions together. If they succeed, great! If even one of them fails, undo everything and pretend like nothing happened"

In other words, a transaction is a set of database operations, either performed on one table or across multiple tables, which are designed to either succeed as a whole or to fail as a whole. If it succeeds it is 'committed' (the changes are saved) , if it fails it is 'rolled back' (the changes are undone).

Code example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "your-connection-string-here";

        int userId = 1;
        decimal purchaseAmount = 100;

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            // Start the transaction
            SqlTransaction transaction = conn.BeginTransaction();

            try
            {
                // Step 1: Deduct from User Balance
                SqlCommand deductCmd = new SqlCommand(@"
                    UPDATE Users
                    SET Balance = Balance - @Amount
                    WHERE UserId = @UserId", conn, transaction);
                deductCmd.Parameters.AddWithValue("@Amount", purchaseAmount);
                deductCmd.Parameters.AddWithValue("@UserId", userId);
                deductCmd.ExecuteNonQuery();

                // Step 2: Add to OrderHistory
                SqlCommand insertOrderCmd = new SqlCommand(@"
                    INSERT INTO OrderHistory (UserId, Amount, OrderDate)
                    VALUES (@UserId, @Amount, @OrderDate)", conn, transaction);
                insertOrderCmd.Parameters.AddWithValue("@UserId", userId);
                insertOrderCmd.Parameters.AddWithValue("@Amount", purchaseAmount);
                insertOrderCmd.Parameters.AddWithValue("@OrderDate", DateTime.Now);
                insertOrderCmd.ExecuteNonQuery();

                // Everything succeeded, commit the transaction
                transaction.Commit();
                Console.WriteLine("Purchase completed successfully.");
            }
            catch (Exception ex)
            {
                // Something went wrong, roll back everything
                transaction.Rollback();
                Console.WriteLine("Purchase failed. Transaction rolled back.");
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

TIL: Adapter Design Pattern

Vishnu KN — Thu, 02 Oct 2025 13:48:24 +0000

Sometimes there are two parts of your code that you want to work together, but they cannot because they are incompatible. This is where the Adapter Design Pattern comes in handy.

Imagine a large car manufacturing factory with multiple departments. The engineering department and the testing departments need to co-ordinate and work together, but that is not happening. The engineering team's output does not match what the testing department expects, on many levels. For example, the engineering department designs parts using imperial measurements (inches, pounds) while the testing departments expects metric measurements (centimeters, kilograms). Like this, there are many aspects which are leading to incompatibility and not understanding each other's language and expectations. To solve this, the factory introduces a small Adapter department whose whole job is to receive parts or data from Engineering, convert the measurements (e.g., inches to centimeters), and hand them off to Testing in the format it expects.

Code example:

// Engineering Department (old interface)
class EngineeringDepartment {
  provideMeasurements() {
    return { length: 20, unit: "inches" };
  }
}

// Testing Department (expects metric)
class TestingDepartment {
  testPart(part) {
    console.log(`Testing part of length ${part.length} ${part.unit}`);
  }
}

// Adapter Department
class EngineeringToTestingAdapter {
  constructor(engineeringDept) {
    this.engineeringDept = engineeringDept;
  }

  getMetricMeasurements() {
    const imperial = this.engineeringDept.provideMeasurements();
    return {
      length: imperial.length * 2.54, // inches to cm
      unit: "cm"
    };
  }
}

// Usage
const engineering = new EngineeringDepartment();
const adapter = new EngineeringToTestingAdapter(engineering);
const testing = new TestingDepartment();

const partForTesting = adapter.getMetricMeasurements();
testing.testPart(partForTesting);

TIL: Facade Design Pattern

Vishnu KN — Thu, 02 Oct 2025 09:54:59 +0000

The Facade design pattern comes in handy when your code is complex and you are dealing with a lot of sub-systems and "wheels within wheels" in your code. It provides a way of navigating all this by creating an interface which takes in the request and behind the scenes, sets the relevant wheels in motion, talks to the relevant sub-systems and delivers the request.

Imagine a large car manufacturing factory. Inside the factory, there are specialized departments which work on specialized aspects of building a car. There might be a chassis department, engine department, paint shop, interior department, quality control department etc. If, as a customer, you tried to co-ordinate with each of these departments to get the car built, it would be a nightmare. To prevent this problem, the factory has a manager or a front-office. All that the customer has to do is to walk in and state his requirement "I want a red sedan with leather seats." and the manager/front-office sets the relevant departments in motion at the right times and delivers on the request.

Code example

// Subsystems (Departments inside the factory)

class ChassisDepartment {
  buildChassis(type) {
    console.log(`🔩 Building a ${type} chassis...`);
  }
}

class EngineDepartment {
  installEngine(engineType) {
    console.log(`⚙️ Installing a ${engineType} engine...`);
  }
}

class PaintDepartment {
  paint(color) {
    console.log(`🎨 Painting the car ${color}...`);
  }
}

class InteriorDepartment {
  installInterior(interiorType) {
    console.log(`🪑 Installing ${interiorType} interior...`);
  }
}

class QualityControlDepartment {
  inspect() {
    console.log(`🧪 Performing final inspection...`);
  }
}

// 🧑‍💼 Facade (Front Office)/Manager
class CarFactoryFacade {
  constructor() {
    this.chassis = new ChassisDepartment();
    this.engine = new EngineDepartment();
    this.paint = new PaintDepartment();
    this.interior = new InteriorDepartment();
    this.qc = new QualityControlDepartment();
  }

  // High-level method that hides the complexity
  orderCar({ type, engine, color, interior }) {
    console.log("📋 Order received at Front Office. Processing...\n");

    this.chassis.buildChassis(type);
    this.engine.installEngine(engine);
    this.paint.paint(color);
    this.interior.installInterior(interior);
    this.qc.inspect();

    console.log(`\n🚗 Your ${color} ${type} with a ${engine} engine and ${interior} interior is ready!`);
  }
}

// 👨‍💻 Client code
const frontOffice = new CarFactoryFacade();

// The client just places an order — doesn't deal with each department individually
frontOffice.orderCar({
  type: "Sedan",
  engine: "V8",
  color: "Red",
  interior: "leather"
});

TIL: Singleton Design Pattern

Vishnu KN — Thu, 02 Oct 2025 09:21:54 +0000

Sometimes, having multiple instances of a class makes things confusing, wasteful or logically wrong. In such instances, the factory design pattern comes handy.

In the construction of a car, each car has only one chassis. One set of workers may request access to the chassis to add wheels, the other set might want the chassis to work on gears and an other set of workers might want to work on the windshield. But they all get the same chassis. When it is first requested, the chassis is created. For all subsequent requests, the same chassis is returned.

Here is how it look like in code:

class Chassis {
  constructor() {
    if (Chassis.instance) {
      return Chassis.instance;
    }
    console.log("🚗 New chassis created");
    Chassis.instance = this;
  }
}

const teamA = new Chassis(); // 🚗 New chassis created
const teamB = new Chassis(); // returns existing chassis
const teamC = new Chassis(); // same instance

console.log(teamA === teamB && teamB === teamC); // true

TIL: Builder Design Pattern

Vishnu KN — Thu, 02 Oct 2025 05:53:08 +0000

In scenarios where the object creation logic is complex AND you need need fine-grained control over HOW the object is assembled, the builder pattern comes in handy.

Imagine someone walking up to, say, a car manufacturing factory, with a highly specific request for a car of a certain type (red color, sedan, 818 total horsepower etc) and the factory appoints a builder who in-turn builds the car, accepting inputs at every step of the process and building it accordingly. That is the builder pattern in a nutshell.

With the builder pattern, the end goal is to have something like this:

const myObject = new ObjectBuilder() 
  .setFeature1("feature1") 
  .setFeature2("feature2") 
  .setFeature3("feature3") 
  .build();

Here is a full example.

// Product class
class Car {
  constructor(brand, color, engine, sunroof, wheels) {
    this.brand = brand;
    this.color = color;
    this.engine = engine;
    this.sunroof = sunroof;
    this.wheels = wheels;
  }

  describe() {
    console.log(
      `${this.color} ${this.brand} with a ${this.engine} engine, ` +
      `${this.sunroof ? "with sunroof" : "no sunroof"}, ` +
      `${this.wheels} wheels.`
    );
  }
}

// Builder class
class CarBuilder {
  constructor() {
    // default values
    this.brand = "Generic";
    this.color = "white";
    this.engine = "standard";
    this.sunroof = false;
    this.wheels = 4;
  }

  setBrand(brand) {
    this.brand = brand;
    return this;
  }

  setColor(color) {
    this.color = color;
    return this;
  }

  setEngine(engine) {
    this.engine = engine;
    return this;
  }

  addSunroof() {
    this.sunroof = true;
    return this;
  }

  setWheels(number) {
    this.wheels = number;
    return this;
  }

  build() {
    return new Car(this.brand, this.color, this.engine, this.sunroof, this.wheels);
  }
}

// Usage
const myCar = new CarBuilder()
  .setBrand("Ferrari")
  .setColor("Red")
  .setEngine("V8")
  .addSunroof()
  .setWheels(4)
  .build();

myCar.describe();
// 👉 Output: Red Ferrari with a V8 engine, with sunroof, 4 wheels.

So to sum up, builder pattern is like requesting a builder, since you have a very specific and complex request and you want fine-grained control over how the car is built. The builder builds the car step by step, taking your inputs at every stage.

The builder class is the builder and the methods defined within builder represent the things the builder is capable of doing, his skillset, so to speak. The instantiation of the builder class and calling specific set methods is like talking to the builder and giving step by step instructions. The final build() call is like telling the builder "I am satisfied with the build. Please hand me the car".

TIL: Factory Design Pattern

Vishnu KN — Tue, 30 Sep 2025 18:53:47 +0000

In scenarios where the object creation logic is complex or dynamic, the factory design pattern comes in handy.

Imagine someone walking up to, say, a car manufacturing factory, with a request for a car of a certain type (red color, sedan, 818 total horsepower etc) and the factory accepts the request, figures out how to makes it and delivers a concrete product back to the user.

That is the whole idea behind the factory pattern.

So basically, with the factory design pattern, the end result that we want as developers is a way to create objects using a factory class and an abstract instruction like:

objectFactory.createObject(objectType);

So, in order to do something like this, objectFactory should, of necessity, have a static method of the same name.

The reason why it should be static is that we do not want the factory to create a sub-factory and then create the object requested because that would be inefficient

The static method should be very flexible, in the sense that it can take the parameter passed into it and return a concrete instance of an object.

class ObjectFactory {
  static createObject(type) {
    switch (type) {
      case "car":
        return new Car();
      case "bike":
        return new Bike();
      default:
        throw new Error("Invalid object type");
    }
  }
}

class Car {
  drive() {
    console.log("Driving a car");
  }
}

class Bike {
  ride() {
    console.log("Riding a bike");
  }
}

// Usage
const myCar = ObjectFactory.createObject("car");
myCar.drive(); // Output: Driving a car

const myBike = ObjectFactory.createObject("bike");
myBike.ride(); // Output: Riding a bike

The factory pattern is like submitting a token at a glass covered token which is then accepted by a faceless hand which then picks the right model/variant based on the request, quickly builds/assembles the car and hands it back to the requester.

The factory is the factory class. The faceless hand is the static method and the right model/variant that it picks represent the classes it uses to create the object.

You are just saying to the factory - "Here is my request - hand me the appropriate car" and the factory figures out the how and does just that.

TIL: Optimistic concurrency

Vishnu KN — Sat, 27 Sep 2025 07:12:12 +0000

Concurrency, in DB management, is a situation where a row or a piece of data living in a database, is being accessed by multiple users at the same time with the purpose of reading/updating/deleting it.

This obviously can lead to conflicts and inconsistencies.

There are 2 broad ways of dealing with such conflicts: Optimistic concurrency and Pessimistic concurrency.

Simply put, Optimistic concurrency means allowing concurrent edits/updates without blocking it just because some else is working on it, with the optimism that it will all work out in the end with proper conflict handling.

Pessimistic concurrency means NOT allowing concurrent edits/updates by blocking it because some else is working on it, with the pessimism that it will all NOT work out in the end.

AI generated analogy:

📚 The Setting

Lila and Mohan are librarians working in the same library.
They both need to update the same library book card — the card for “The Hobbit”.
The library allows two different ways of handling such situations:

Pessimistic concurrency 🔒
Optimistic concurrency 🌿

🔒 1. Pessimistic Concurrency — Lock First, Work Alone

Lila goes to update the card for “The Hobbit.”
She takes the original card from the filing cabinet, puts it on her desk, and locks it in her drawer while she works.

Mohan comes by a few minutes later. He also wants to make a change to “The Hobbit.”

The librarian says:

“❌ Sorry, Lila is working on that card. You’ll have to wait until she’s done.”

So Mohan just stands by until Lila finishes.
When she’s done, she puts the card back, unlocks it, and now Mohan can take it and make his edits.

👉 Key idea:

Only one person can work on the card at a time.
Conflicts are prevented upfront because nobody else is allowed to touch the card while it’s “checked out.”
But it means others (like Mohan) might have to wait.

🧠 This is pessimistic concurrency:

“Let’s lock the data immediately so no one else can mess with it while I’m working.”

🌿 2. Optimistic Concurrency — Work Freely, Check Later

Now imagine a different approach.

Lila goes to update the same card. Instead of taking the original, she photocopies it. She works on the copy at her desk.

At the same time, Mohan also goes to update the same card. He too takes his own photocopy and works on it independently.

Neither of them blocks the other. They both happily make their edits at the same time.

Later, both return to the librarian to apply their changes to the original card.

Mohan returns first and updates the original card. The librarian stamps the card “Version #6.”
Lila comes a bit later with her changes based on the older Version #5.

The librarian checks:

“Hmm, this card is already at Version #6.
Lila, you’re trying to apply edits to Version #5. Someone else has changed this in the meantime.”

Now, there are three possible ways the librarian handles this:

🚫 Reject Lila’s update — “Please take the latest card and reapply your changes.”
📝 Ask Lila to manually merge her edits with the new Version #6.
🤖 Automatically merge the changes if they don’t conflict (e.g., Lila changed the title, Mohan changed the author).

👉 Key idea:

Multiple people can work simultaneously.
Conflicts are detected later when they try to save.
Nobody is blocked while editing, but the system must check versions to catch conflicts.

🧠 This is optimistic concurrency:

“Let’s trust that things will work out, let everyone work in parallel, and handle any conflicts after they happen.”

TIL: Clustered and Non-clustered indices in SQL

Vishnu KN — Thu, 25 Sep 2025 08:33:38 +0000

The tables that we see in SQL server are not the stored data but the "presentation" of it. When a table is created and rows are inserted, behind the scenes, SQL stores the actual data relating to that table on what is known as data pages. This behind the scenes stuff is called SQL internals. And the way it is stored may be "clustered" or "non-clustered" (based on what is specified) or "heap" (if nothing is specified)

Heap is like haphazardly dumping the data wherever space is available, like an unorganized phone book. In SQL server, if no index is defined, heap is the default approach.

Clustered index is when you organize that phone book according to some parameter (like first name, etc). That means, when clustered index is defined, the heap is organized as per the parameter defined in the clustered index (and thus it is no longer a heap, it is eliminated).

Heap and clustered index are thus opposites of each other.

And non clustered index is like a yellow pages book which references the phone book (organized or un-organized).

In SQL server, IF primary key is defined, it automatically becomes the clustered index key. If not defined, there will be no clustered index key. And any column can be defined as the clustered index key or as the non clustered index key.

AI Visulaization:

1️⃣ Heap (no clustered index)

Rows are just placed wherever there’s space. No particular order:

Data Pages
+-------------------+   +-------------------+
| StudentID=3,Ravi  |   | StudentID=2,Meera |
| StudentID=1,Anil  |   |                   |
+-------------------+   +-------------------+

👉 To find Name='Meera', SQL has to scan everything.

2️⃣ Clustered Index on `StudentID`

Rows are physically sorted by StudentID across data pages:

Data Pages (clustered on StudentID)
+-------------------+   +-------------------+
| StudentID=1,Anil  |   | StudentID=3,Ravi  |
| StudentID=2,Meera |   |                   |
+-------------------+   +-------------------+

👉 To find StudentID=2, SQL can go straight to it (fast).

3️⃣ Non-Clustered Index on `Name` (table has clustered index on StudentID)

Separate structure, sorted by Name, pointing back to rows:

Non-Clustered Index (sorted by Name)
+--------------------+
| Anil   → StudentID=1
| Meera  → StudentID=2
| Ravi   → StudentID=3
+--------------------+

Clustered Data Pages (sorted by StudentID)
+-------------------+   +-------------------+
| 1,Anil,22         |   | 3,Ravi,20         |
| 2,Meera,21        |   |                   |
+-------------------+   +-------------------+

👉 Steps when you query WHERE Name='Meera':

SQL looks in the non-clustered index → finds Meera → StudentID=2.
Uses the clustered index on StudentID → fetches row 2,Meera,21.

4️⃣ Non-Clustered Index on `Name` (table is a heap)

If there’s no clustered index, the non-clustered index points to the physical address (RID):

Non-Clustered Index (sorted by Name)
+--------------------+
| Anil   → Page1,Slot2
| Meera  → Page2,Slot1
| Ravi   → Page1,Slot1
+--------------------+

Heap Data Pages (unordered)
+-------------------+   +-------------------+
| Page1: Ravi(3)    |   | Page2: Meera(2)   |
| Page1: Anil(1)    |   |                   |
+-------------------+   +-------------------+

👉 Here, SQL jumps directly to the physical location.

✅ Summary:

Heap = unordered, non-clustered indexes point to physical row location.
Clustered = ordered by clustered key, non-clustered indexes point to clustered key.

Optimizing how SQL stores data behind the scenes (internals) can make a huge difference in performance and scalability.

Non clustered indexes come in useful in scenarios where a column which is not a clustered index is frequently being queried.

🔹 Example

Suppose you have a table clustered on EmployeeID (so rows are stored ordered by ID):

EmployeeID	Name	Department
1	Anil	IT
2	Meera	Finance
3	Ravi	HR

Query 1:

  SELECT * FROM Employees WHERE EmployeeID = 2;

👉 Fast because clustered index is on EmployeeID.

Query 2:

  SELECT * FROM Employees WHERE Name = 'Meera';

👉 Without non-clustered index, SQL must scan every row (slow).
👉 With a non-clustered index on Name, SQL can directly use the sorted Name list and jump to the right row.

✅ Analogy

Clustered index = phone book sorted by last name (only one order possible).
Non-clustered index = an extra list, say sorted by phone number or by city, pointing back to the entries in the main book.

So, a non-clustered index is useful when you want fast lookups or sorts based on a column other than the clustered key.

In the above example, SQL can directly jump to the M's and that reduces the volume of scanning that needs to be done.

TIL: Temporary tables and CTE's in SQL

Vishnu KN — Wed, 24 Sep 2025 19:04:09 +0000

Temporary tables are makeshift tables that you can create and store data in for temporary reference. One you are done with querying and you close your ssms, the temp table gets deleted.

You use the # symbol as a prefix to create one.

There are 2 types: local and global. If you create a local one, it is accessible only to you and if you create a global one, it is accessible to all active sessions on ssms.

AI generated analogy:

For local temporary tables (#table_name)
A personal scratchpad: Think of a local temp table as a private notepad you create for yourself to use during a single work session.
The session is the key: The "session" starts when you connect to the database and ends when you disconnect or close the query window.
It vanishes with the session: When you close the SSMS window, your connection to the database ends, and SQL Server automatically erases your temporary table.

For global temporary tables (##table_name)
A shared whiteboard: A global temp table is a bit different. It's like a whiteboard that everyone in the office can see and write on.
It outlives your session (sometimes): The global temp table stays around as long as at least one person is still using it. If you close your SSMS window but someone else is still running a query against that same table, it will not be deleted.
The last person to leave turns out the light: It is only automatically deleted once the very last connection that has been referencing that table closes.

CTE's, (Common Table Expressions) on the other hand, only holds that data with a nickname in the SQL internals/data pages (the behind the scenes storage of SQL) for the duration of the query and then deletes it.

Table variables are the middle ground between temp tables and CTEs. In temp tables, once you create the table and run your SQL code, SQL server destroys the table after your session ends whereas in case of table variables, SQL server destroys the table after the chunk of SQL code referencing the table variable is done executing.

TIL: Angular OnPush

Vishnu KN — Tue, 23 Sep 2025 13:34:03 +0000

The OnPush change detections strategy triggers change detection only
1) when @Input data is updated and that too, with conditions
a) If The update is a reference type and not a mutation (pushed in and not slipped in)
b) in case of observables as @Input() data, if it uses async pipe
2) User interactions/events are set in motion
3) when it is manually triggered

I can use OnPush when my component's data is mostly coming in from the outside and I am not changing the data via mutation (ie., I am only using immutable patterns to make changes). It is also an excellent choice when I am using async pipe in my component and all its data comes from observables

TIL: Repository pattern

Vishnu KN — Tue, 23 Sep 2025 13:32:26 +0000

Repository pattern: A domain specific storage of database manipulation methods containing method names that are in the language of the business rather than in the language of SQL.

The whole point of the repository pattern is to have a place where the database logic defined in the language of the business, rather than the technical language.

The repository pattern cannot work by itself. It needs to communicate with an ORM like EF core or Dapper to work.