SQL Server Essentials

Microsoft SQL Server is a mature, enterprise-grade relational database that has been the backbone of countless .NET and Windows-shop applications for decades, and it now runs on Linux and in containers too. Its query language, T-SQL (Transact-SQL), extends standard SQL with procedural constructs, built-in functions, and error handling that make it possible to write entire application logic as stored procedures. What sets SQL Server apart operationally is its tooling — SQL Server Management Studio, the query optimizer's execution plan visualizations, and Azure SQL Database as a fully managed cloud option — plus strong support for OLTP workloads with configurable isolation levels that most other RDBMSs either lack or implement differently.

T-SQL Fundamentals

T-SQL syntax overlaps heavily with standard SQL but has its own idioms: square brackets for identifiers that need escaping, `TOP` instead of `LIMIT`, `IDENTITY` instead of `AUTO_INCREMENT`, and `GETDATE()`/`SYSDATETIME()` for the current timestamp. Variables are declared with `DECLARE` and prefixed with `@`, and control flow (`IF`, `WHILE`, `BEGIN...END`) makes T-SQL genuinely procedural, not just a query language bolted onto stored procedures.

CREATE TABLE dbo.Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    Status VARCHAR(20) NOT NULL DEFAULT 'pending',
    TotalCents INT NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES dbo.Customers(CustomerId)
);

-- TOP instead of LIMIT; square brackets escape reserved words / spaces
SELECT TOP 20 OrderId, [Status], TotalCents
FROM dbo.Orders
WHERE CustomerId = 42
ORDER BY CreatedAt DESC;

-- Variables, control flow, and error-safe procedural logic
DECLARE @CustomerId INT = 42;
DECLARE @OrderCount INT;

SELECT @OrderCount = COUNT(*) FROM dbo.Orders WHERE CustomerId = @CustomerId;

IF @OrderCount > 100
BEGIN
    PRINT 'VIP customer';
    UPDATE dbo.Customers SET Tier = 'vip' WHERE CustomerId = @CustomerId;
END
ELSE
BEGIN
    PRINT 'Standard customer';
END;

-- MERGE for upsert semantics in one statement
MERGE dbo.Inventory AS target
USING (SELECT 501 AS ProductId, 25 AS Quantity) AS source
ON target.ProductId = source.ProductId
WHEN MATCHED THEN
    UPDATE SET Quantity = source.Quantity
WHEN NOT MATCHED THEN
    INSERT (ProductId, Quantity) VALUES (source.ProductId, source.Quantity);

Indexes & Execution Plans

Every SQL Server table effectively lives inside its clustered index — if you declare a primary key without specifying otherwise, it becomes the clustered index, and the table's rows are physically stored in that key's order. Nonclustered indexes are separate structures pointing back to the clustered key (or a row locator for heaps), so a nonclustered index that's used heavily for lookups should usually `INCLUDE` the columns a query needs, turning it into a covering index that avoids a costly key lookup back into the clustered index. Execution plans (actual vs. estimated) are how you diagnose slow queries — SSMS renders them graphically, but the raw XML/text form is just as readable once you know what to look for: table/index scans instead of seeks, missing index warnings, and operators with disproportionate cost relative to row count.

-- Covering index: includes TotalCents so the query never touches the base table
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Status
ON dbo.Orders (CustomerId, [Status])
INCLUDE (TotalCents, CreatedAt);

-- View the estimated execution plan without running the query
SET SHOWPLAN_XML ON;
GO
SELECT * FROM dbo.Orders WHERE CustomerId = 42 AND [Status] = 'pending';
GO
SET SHOWPLAN_XML OFF;

-- Turn on actual execution stats (row counts, real time) for a running query
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM dbo.Orders WHERE CustomerId = 42;

-- Find missing index suggestions SQL Server's optimizer has logged
SELECT mid.statement AS TableName, mig.index_group_handle,
       migs.avg_user_impact, mid.equality_columns, mid.included_columns
FROM sys.dm_db_missing_index_details mid
JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
ORDER BY migs.avg_user_impact DESC;

Transactions & Isolation Levels

SQL Server's default isolation level, READ COMMITTED, blocks readers behind writers using locks unless you enable READ_COMMITTED_SNAPSHOT at the database level, which switches to row versioning (similar to how PostgreSQL's MVCC works by default) so readers no longer block on uncommitted writes. This single setting is one of the most impactful tuning decisions for a busy OLTP database, because lock-based READ COMMITTED can cause significant contention under concurrent load. SQL Server also supports SNAPSHOT isolation explicitly, plus the standard SERIALIZABLE, REPEATABLE READ, and READ UNCOMMITTED levels, each trading consistency guarantees for concurrency.

-- Enable row-versioning based READ COMMITTED (much less blocking under load)
ALTER DATABASE MyAppDb SET READ_COMMITTED_SNAPSHOT ON;

BEGIN TRANSACTION;

    UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
    UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        THROW 50001, 'Transfer failed, rolled back', 1;
    END

COMMIT TRANSACTION;

-- Explicit isolation level for a single session
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    SELECT Balance FROM dbo.Accounts WHERE AccountId = 1;
    -- Concurrent writers do not block this read, and this read sees a
    -- consistent snapshot as of the transaction's start
COMMIT TRANSACTION;

-- TRY/CATCH is the idiomatic T-SQL error-handling pattern (paired with transactions)
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO dbo.Orders (CustomerId, TotalCents) VALUES (42, 5000);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Stored Procedures & Parameterization

Stored procedures are the standard way to encapsulate business logic close to the data in SQL Server shops, and they come with a real performance benefit beyond organization: query plan caching. SQL Server compiles and caches an execution plan keyed by the procedure and its parameters, so repeated calls skip re-optimization — though this same caching can bite you as "parameter sniffing," where a plan optimized for one parameter's data distribution gets reused for a very different parameter and performs badly. `OUTPUT` parameters, table-valued parameters, and structured error handling with `THROW`/`RAISERROR` round out the toolkit for building real application logic in T-SQL.

CREATE PROCEDURE dbo.usp_CreateOrder
    @CustomerId INT,
    @TotalCents INT,
    @NewOrderId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE CustomerId = @CustomerId)
    BEGIN
        THROW 50002, 'Customer does not exist', 1;
        RETURN;
    END

    INSERT INTO dbo.Orders (CustomerId, TotalCents, [Status])
    VALUES (@CustomerId, @TotalCents, 'pending');

    SET @NewOrderId = SCOPE_IDENTITY();
END;
GO

-- Calling it and capturing the OUTPUT parameter
DECLARE @OrderId INT;
EXEC dbo.usp_CreateOrder @CustomerId = 42, @TotalCents = 5000, @NewOrderId = @OrderId OUTPUT;
SELECT @OrderId AS CreatedOrderId;

-- WITH RECOMPILE forces a fresh plan for procedures suffering from bad parameter sniffing
CREATE PROCEDURE dbo.usp_SearchOrders
    @Status VARCHAR(20)
WITH RECOMPILE
AS
    SELECT * FROM dbo.Orders WHERE [Status] = @Status;

Gotchas & Practical Tips

• Parameter sniffing is a top cause of "this query was fast yesterday and slow today" reports — the cached plan was compiled for a parameter value with a very different row count than today's call. Diagnose with `OPTION (RECOMPILE)` on the offending query, or use plan guides/query hints for a permanent fix.

• Under the default READ COMMITTED isolation without snapshot isolation enabled, long-running reports can block writers and vice versa — enabling READ_COMMITTED_SNAPSHOT is usually the single highest-leverage change for reducing blocking in an OLTP database.

• A nonclustered index that doesn't cover all the columns a query selects forces a "key lookup" back into the clustered index for every matching row — often more expensive than the index seek itself. Check execution plans for Key Lookup operators and consider adding INCLUDE columns.

• Implicit conversions between mismatched data types (e.g., comparing an NVARCHAR column to a VARCHAR literal) silently defeat index usage, turning a seek into a scan. Watch for CONVERT_IMPLICIT warnings in execution plans.

• `SCOPE_IDENTITY()` returns the last identity value inserted within the current scope (safe inside triggers/nested calls); `@@IDENTITY` returns the last identity across the whole session, including triggers, which can silently give you the wrong value.