Module 12: SQL Server & Database Design

While EF Core (Module 11) allows you to avoid writing SQL most of the time, a professional .NET developer must understand what is happening “under the hood.” When an app is slow or a report is wrong, you won’t fix it in C#; you’ll fix it in the database.

1. SQL Server Basics

SQL Server is a Relational Database Management System (RDBMS). It organizes data into tables that relate to one another.

• T-SQL (Transact-SQL): Microsoft’s version of SQL. It includes standard commands like SELECT and INSERT, plus advanced features like variables and error handling.
• SSMS (SQL Server Management Studio): The primary tool used to write queries and manage the server.

2. Tables, Keys & Constraints

These are the “rules” of your data. Without them, your database becomes a “data swamp.”

• Primary Key (PK): A unique identifier for every row (e.g., UserId).
• Foreign Key (FK): A column that creates a link between two tables (e.g., CategoryId in the Products table).
• Constraints:
  • Unique: Ensures no two rows have the same value (e.g., Email).
  • Check: Ensures data meets a condition (e.g., Age >= 18).
  • Not Null: Prevents empty values in critical columns.

3. Joins & Subqueries

In a normalized database, data is split across tables. Joins allow you to stitch them back together for a report.

• Inner Join: Returns only rows where there is a match in both tables.
• Left Join: Returns all rows from the left table, and matched rows from the right (shows “NULL” if no match).
• Subqueries: A query inside another query. Used for complex filtering (e.g., “Find all customers who spent more than the average”).

4. Normalization (The 3 Golden Rules)

Normalization is the process of organizing data to reduce redundancy.

1. 1NF (First Normal Form): No “arrays” in a cell. Every cell must have a single, atomic value.
2. 2NF (Second Normal Form): Move data that applies to multiple rows to a separate table (e.g., don’t store “Supplier Address” in the Products table).
3. 3NF (Third Normal Form): Remove columns that don’t depend directly on the Primary Key.

5. Indexes & Performance Tuning

If a query is slow, Indexes are usually the answer.

• Clustered Index: Determines the physical order of data on the disk (usually the Primary Key). You can only have one.
• Non-Clustered Index: Like an index in the back of a book. It’s a separate structure that points to the data. It makes SELECT fast but makes INSERT/UPDATE slightly slower.
• Execution Plan: A visual map showing how SQL Server found your data. It tells you if you are missing an index.

6. Stored Procedures

Instead of sending a long SQL string from C#, you save the query on the SQL Server and call it by name.

• Pros: Faster (pre-compiled), more secure (prevents SQL injection), and centralizes logic.
• Cons: Harder to version control compared to C# code.

7. Transactions (ACID)

A Transaction ensures that a group of operations either all succeed or all fail. Imagine a bank transfer:

1. Subtract $100 from Account A.
2. Add $100 to Account B. If step 2 fails (power outage), step 1 must be undone (Rollback). This follows the ACID properties: Atomicity, Consistency, Isolation, Durability.

8. Real-World Tip for .NET Developers

In modern development, we often use EF Core for 90% of tasks (simple CRUD) and Dapper or Raw SQL for the remaining 10% (complex reports or high-performance bulk updates). Knowing how to write a manual JOIN or INDEX is what separates a senior developer from a junior.