DELETE vs TRUNCATE vs DROP in SQL Server

 1. DELETE

• Used to remove rows one at a time from a table.

• Can be filtered with WHERE clause.

• Each deleted row is logged in transaction log (slower).

• Identity column value does not reset unless explicitly used with DBCC CHECKIDENT.

• Can be rolled back if inside a transaction.

✅ Example:

DELETE FROM Employees WHERE DeptId = 2;

---

2. TRUNCATE

• Removes all rows from a table (no WHERE).

• Logs only page deallocations (faster than DELETE).

• Resets identity column to seed value.

• Cannot be used if:

  • Table is referenced by a foreign key.

  • Indexed views exist on the table.

• Can be rolled back if inside a transaction.

✅ Example:

TRUNCATE TABLE Employees;

---

3. DROP

• Deletes the entire table structure and data.

• Table definition, constraints, indexes, triggers — all removed.

• Cannot be rolled back (once committed).

• After DROP, table is gone from database; must be recreated to use again.

✅ Example:

DROP TABLE Employees;

---

4. Key Differences Table

Feature	DELETE	TRUNCATE	DROP
Removes	Specific rows (WHERE) or all	All rows	Entire table (structure + data)
WHERE Clause	✅ Allowed	❌ Not allowed	❌ Not applicable
Logging	Row-by-row (slow)	Minimal logging (fast)	Logs table removal
Identity Reset	❌ No	✅ Yes	❌ Not applicable
Rollback Possible	✅ Yes	✅ Yes	❌ No (unless inside transaction)
Foreign Key Restriction	✅ Allowed	❌ Not allowed if referenced	✅ Allowed (but drops constraints)
Table Structure Exists?	✅ Yes (after delete)	✅ Yes (empty table)	❌ No (removed completely)
Speed	Slower (row-by-row)	Faster (deallocate pages)	Fastest (drops object)

---

5. When to Use?

• DELETE → When you need to remove specific rows or want triggers to fire.

• TRUNCATE → When you want to remove all rows quickly and reset identity.

• DROP → When you want to completely remove table and free space.

---

6. Interview Tip

If interviewer asks:
👉 “Can we rollback TRUNCATE?”
✅ Answer: Yes, TRUNCATE can be rolled back if executed inside a transaction.

BEGIN TRAN;
TRUNCATE TABLE Employees;
ROLLBACK; -- restores data

👉 “Why TRUNCATE is faster than DELETE?”
✅ Answer: Because TRUNCATE logs only extent (page) deallocation, while DELETE logs each row removal.

Understanding Singleton Design Pattern in C# with Real-Time Examples

 📌 Introduction

In software design patterns, the Singleton Design Pattern plays a crucial role when we want to ensure that only one instance of a class exists throughout the lifecycle of an application. Singleton provides a global access point to this single instance, making it widely used in scenarios like logging, configuration management, caching, and database connections.

In this article, we will explore:

• What is Singleton Design Pattern?

• Importance of private constructor, static variable, and static constructor.

• Real-time example with C#.

• Difference between Singleton and Static class.

• Lazy Singleton with thread safety implementation.

---

🚀 What is Singleton Design Pattern?

The Singleton Pattern ensures that a class has only one instance and provides a global access point to it. This prevents duplicate objects from being created unnecessarily and helps in maintaining a single state across the application.

For example, imagine a Logger class – instead of creating multiple loggers in different modules, a singleton logger ensures all logs go through one centralized object.

---

🔎 Importance of Components in Singleton

✅ Private Constructor

• Prevents the creation of an object using the new keyword.

• Ensures external classes cannot instantiate multiple objects.

private Logger() { }

---

✅ Static Variable

• Stores the single instance of the class.

• Ensures only one instance exists globally.

private static Logger _instance;

---

✅ Static Constructor

• Initializes the singleton instance only once, automatically by the CLR.

• Ensures thread-safe initialization.

static Logger()
{
    _instance = new Logger();
}

---

💡 Real-Time Example: Logger Singleton

public sealed class Logger
{
    private static readonly Logger _instance;

    // Static constructor
    static Logger()
    {
        _instance = new Logger();
    }

    // Private constructor
    private Logger() { }

    // Public global access point
    public static Logger Instance => _instance;

    public void Log(string message)
    {
        Console.WriteLine($"[Log]: {message}");
    }
}

// Usage
class Program
{
    static void Main()
    {
        Logger.Instance.Log("Application started");
        Logger.Instance.Log("User logged in");
    }
}

📌 Output:

[Log]: Application started
[Log]: User logged in

Here, Logger.Instance always points to the same object.

---

🆚 Singleton vs Static Class

Feature	Singleton Class	Static Class
Instance	Single object (controlled creation)	No instance (direct static access)
Inheritance	Can implement interfaces & inherit	Cannot inherit or implement interfaces
Flexibility	Can be extended, mocked, injected (DI)	Rigid, cannot be mocked/tested
State Management	Can maintain state inside instance	Uses static fields (global state)
Initialization	Can use lazy loading for performance	Always loaded at first access

👉 Use Singleton for shared resources like Database Connections, Logger, Cache Manager.
👉 Use Static Class for utility/helper methods like Math or Convert.

---

🛡️ Lazy Singleton with Thread Safety in C#

In high-performance applications, it is better to create the singleton instance only when needed (Lazy Initialization). This saves memory and improves performance.

✅ Thread-Safe Lazy Singleton Example

public sealed class ConfigurationManager
{
    // Lazy ensures thread safety and lazy initialization
    private static readonly Lazy<ConfigurationManager> _instance =
        new Lazy<ConfigurationManager>(() => new ConfigurationManager());

    // Private constructor
    private ConfigurationManager() 
    {
        Console.WriteLine("Configuration Manager Initialized");
    }

    // Global access point
    public static ConfigurationManager Instance => _instance.Value;

    public string GetConfig(string key)
    {
        return $"Value of {key}";
    }
}

// Usage
class Program
{
    static void Main()
    {
        var config1 = ConfigurationManager.Instance;
        Console.WriteLine(config1.GetConfig("ConnectionString"));

        var config2 = ConfigurationManager.Instance;
        Console.WriteLine(ReferenceEquals(config1, config2)); // true
    }
}

📌 Output

Configuration Manager Initialized
Value of ConnectionString
True

✔ The constructor runs only once.
✔ Both config1 and config2 point to the same instance.
✔ The Lazy<T> type ensures thread safety automatically.

---

🎯 Conclusion

The Singleton Design Pattern in C# is one of the most powerful and commonly used design patterns for managing shared resources. With the help of private constructor, static variables, and static constructors, we can ensure that only one instance exists throughout the application.

For modern .NET applications, the Lazy Singleton with thread safety approach is the most efficient and recommended implementation.

Key Takeaway:

• Use Singleton when you need a shared, single instance with controlled access.

• Use Static Class when you need utility functions without state.