SQL Server (MSSQL)

Microsoft SQL Server is an enterprise-grade RDBMS. It uses T-SQL (Transact-SQL), an extension of SQL that adds procedural programming, local variables, and data processing.

When to Use

• Enterprise .NET Stack: Deep integration with C#, Azure, and Visual Studio.
• Complex Analytics: Built-in Analysis Services (SSAS) and Reporting (SSRS).
• Corporate Environments: Active Directory integration for security.

Quick Start (T-SQL)

-- CTE and Window Function
WITH Sales_CTE AS (
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT SalesPersonID, TotalSales,
       RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM Sales_CTE;

Core Concepts

T-SQL

Powerful procedural extensions.

DECLARE @Counter INT = 1;
WHILE @Counter <= 10
BEGIN
   PRINT @Counter;
   SET @Counter = @Counter + 1;
END

SQL Agent

Built-in job scheduler for backups, maintenance, and scripts.

Clustered Index

Organizes the data in the table physically. Usually the Primary Key. A table can likely have only one.

Best Practices (2025)

Do:

• Use AI Integration (2025): Call sp_invoke_external_rest_endpoint to integrate Azure OpenAI directly into queries.
• Use APPLY operator: CROSS APPLY is powerful for joining a table to a table-valued function.
• Query Store: Enable Query Store to track performance regressions over time automatically.

Don’t:

• Don’t use cursors: T-SQL set-based operations are almost always faster.
• Don’t use NOLOCK blindly: It causes dirty reads. Use READ COMMITTED SNAPSHOT isolation instead.

References

• Microsoft SQL Docs