Sql Development

SQL Development

Optimized for current PostgreSQL, MySQL, and SQL Server releases plus migration-first database workflows.

Comprehensive SQL development guidelines combining SQL coding standards, stored procedure generation, and MS SQL Server DBA best practices.

• Leverage native parallel subagent dispatch and 200k+ context windows where available.

Anti-Patterns

• Using SELECT * in production queries: It hides contract drift and pulls more data than the caller needs.
• Writing non-SARGable predicates: Functions on indexed columns turn otherwise cheap queries into table scans.
• Ignoring transaction and lock behavior: Correct SQL needs both logical correctness and concurrency safety.

Verification Protocol

Before claiming "skill applied successfully":

1. Pass/fail: The SQL Development implementation names the target runtime, framework version, and affected files. 2. Pass/fail: Build, lint, test, or equivalent local validation is run for the changed surface. 3. Pass/fail: Edge cases for errors, dependency drift, and environment differences are addressed or explicitly out of scope. 4. Pressure-test scenario: Apply the workflow to a change that passes happy-path tests but fails one boundary condition. 5. Success metric: Zero untested success claims; every implementation claim maps to a command or artifact.

Before and After Example

-- Before
SELECT *
FROM Orders
WHERE YEAR(created_at) = 2026;

-- After
SELECT order_id, customer_id, created_at, total_amount
FROM Orders
WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01';

Uses explicit columns and a SARGable date range so indexes can do their work.

Activation Conditions

Use symptom -> action triggers: when one matches, apply this skill and verify with the protocol below.

• Writing SQL queries and stored procedures
• Designing database schemas and table structures
• Working with MS SQL Server as a DBA
• Performance tuning and query optimization
• Database backup, restore, and security configuration
• SQL Server 2025+ feature adoption and migration

---

Part 1: Database Schema Design

Table Naming

• All table names in singular form
• All column names in singular form

Required Columns

• All tables must have a primary key column named id
• All tables must have created_at for creation timestamp
• All tables must have updated_at for last update timestamp

Constraints

• All tables must have a primary key constraint
• All foreign key constraints must have a name
• All foreign key constraints defined inline
• All foreign keys must have ON DELETE CASCADE
• All foreign keys must have ON UPDATE CASCADE
• All foreign keys must reference the primary key of the parent table

---

Part 2: SQL Coding Style

Formatting

• Uppercase for SQL keywords (SELECT, FROM, WHERE)
• Consistent indentation for nested queries
• Comments to explain complex logic
• Break long queries into multiple lines
• Organize clauses: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY

Query Structure

• Use explicit column names, never SELECT *
• Qualify column names with table alias when using multiple tables
• Prefer JOINs over subqueries when possible
• Include LIMIT/TOP clauses to restrict result sets
• Use appropriate indexing for frequently queried columns
• Avoid functions on indexed columns in WHERE clauses

---

Part 3: Stored Procedure Standards

Naming Conventions

• Prefix with usp_
• Use PascalCase: usp_GetCustomerOrders
• Include plural noun for multiple records: usp_GetProducts
• Include singular noun for single record: usp_GetProduct

Parameter Handling

• Prefix parameters with @
• Use camelCase: @customerId
• Provide default values for optional parameters
• Validate parameter values before use
• Document parameters with comments
• Required parameters first, optional later

Structure

• Include header comment block with description, parameters, return values
• Return standardized error codes/messages
• Return result sets with consistent column order
• Use OUTPUT parameters for returning status information
• Prefix temporary tables with tmp_
• Include SET NOCOUNT ON for data-modifying procedures

---

Part 4: Security Best Practices

Query Security

• Parameterize all queries to prevent SQL injection
• Use prepared statements for dynamic SQL
• Avoid embedding credentials in SQL scripts
• Proper error handling without exposing system details
• Avoid dynamic SQL in stored procedures

Transaction Management

• Explicitly begin and commit transactions
• Use appropriate isolation levels
• Avoid long-running transactions that lock tables
• Use batch processing for large data operations

---

Part 5: MS SQL Server DBA

Tooling

• Install and enable ms-mssql.mssql VS Code extension for full database management
• Use official Microsoft documentation for reference and troubleshooting

DBA Responsibilities

• Database creation and configuration
• Backup and restore strategies
• Performance tuning and index optimization
• Security management and auditing
• Upgrades and compatibility planning (SQL Server 2025+)

Best Practices

• Focus on tool-based database inspection over codebase analysis
• Highlight deprecated/discontinued features in SQL Server 2025+
• Encourage secure, auditable, performance-oriented solutions
• Reference official docs for troubleshooting
• Warn about deprecated features and suggest alternatives

---

Troubleshooting

---

Common Pitfalls

• Using SELECT * in production queries: It hides contract drift and pulls more data than the caller actually needs.
• Writing non-SARGable predicates: Functions on indexed columns turn otherwise cheap queries into table scans.
• Skipping transaction and lock analysis: Correct SQL needs both logical correctness and concurrency safety.

References & Resources

Documentation

• T-SQL Patterns — MERGE, CTEs, PIVOT, JSON operations, window functions, and error handling
• Performance Tuning — Execution plans, index tuning, Query Store, and anti-patterns

Scripts

• Stored Procedure Template — Production-ready SP template with TRY/CATCH, pagination, and dynamic sorting

Examples

• Schema Design Example — Recipe Management System with 10 tables, stored procedures, and migrations

---

<!-- PORTABILITY:START -->

Cross-Client Portability

This skill is written to stay usable across GitHub Copilot, Claude Code, Codex, and Gemini CLI.

• GitHub Copilot: keep the folder in a Copilot-visible skill or plugin path, or wrap the workflow as project instructions if the host does not support portable skill folders directly.
• Claude Code: keep the folder in a local skills directory or a compatible plugin or marketplace source.
• Codex: install or sync the folder into $CODEX_HOME/skills/<skill-name> and restart Codex after major changes.
• Gemini CLI: this repository generates a project command named /skills:sql-development from this skill. Rebuild commands with python scripts/export-gemini-skill.py sql-development and then run /commands reload inside Gemini CLI.

<!-- PORTABILITY:END -->

<!-- MCP:START -->

MCP Availability And Fallback

Preferred MCP Server: None required

• Fallback prompt: "Use the SQL Development skill without MCP. Rely on the local SKILL.md, bundled references or scripts, and manual verification. Show the exact commands, evidence, and final checks you used before concluding."
• If the current host does not expose a matching server, use the bundled references, scripts, native toolchain, and manual workflow already described in this skill.
• Treat direct local verification, rendered output, logs, tests, or screenshots as the fallback evidence path before completion.

<!-- MCP:END -->

Related Skills

• php-development: Use it when the workflow also needs modern PHP backend implementation.
• powerbi-modeling: Use it when the workflow also needs Power BI semantic model design and DAX work.
• code-quality: Use it when the workflow also needs two-stage review (spec compliance first, then code quality), maintainability, and refactoring guidance.
• systematic-debugging: Use it when the workflow also needs root-cause debugging before proposing fixes.

Changelog

[2026-04-25] - Version 1.2 Verification Protocol Refresh

Added

• Added a Verification Protocol section with skill-specific pass/fail checks, one pressure-test scenario, and a measurable success metric.
• Added guidance to leverage native parallel subagent dispatch and 200k+ context windows where available.

Changed

• Updated SKILL.md frontmatter to version: "1.2" and last_updated: 2026-04-25.
• Reframed activation guidance toward symptom -> action triggers and standardized two-stage review wording where applicable.

[2026-04-24] - Version 1.1 Refresh

Changed

• Updated the SKILL frontmatter version to 1.1 for the 2026-04-24 catalog refresh.
• Added an "Optimized for ..." note at the top so the guidance is anchored to current platform versions.

[2026-04-24] - Skill Refresh

Changed

• Standardized the SKILL frontmatter with version metadata, last-updated date, tags, and a concise catalog description.
• Reformatted the portability and MCP guidance with a preferred server line, a copy-paste fallback prompt, and consistent bullet lists.
• Added a catalog-standard Anti-Patterns section and refreshed the Related Skills links at the end of the skill.

[2026-04-24] - Catalog Audit Cleanup

Fixed

• Replaced the stale nestjs related-skill reference with javascript-development for JavaScript and TypeScript database integration patterns.

All notable changes to this skill will be documented in this file.

[2026-04-04] - Cross-Client Portability Refresh

Changed

• Added a standard portability note covering GitHub Copilot, Claude Code, Codex, and Gemini CLI.
• Clarified that the core workflow does not require a dedicated MCP server and can run with local tools alone.

Tested

• Validated SKILL.md frontmatter, portability sections, and Gemini export readiness with python scripts/validate-skills.py.

[2026-03-09] - Workspace Modernization

Added

• Added a 2026-03-09 maintenance entry after reviewing the skill; the existing structure and guidance remained suitable.

[2026-02-28] — Description Rewrite & Cross-References

Changed

• Rewrote skill description to ~200 characters with clear, specific activation keywords
• Improved keyword specificity to reduce overlap with related skills

Added

• ## Related Skills cross-reference table with 2-4 related skills and "Use When" guidance

Recipe Management System — Database Schema Design

Entity Relationship Diagram (Text)

┌──────────┐       ┌───────────┐       ┌────────────────┐
│  [User]  │1────M│  [Recipe]  │1────M│   [Comment]    │
│──────────│       │───────────│       │────────────────│
│ Id (PK)  │       │ Id (PK)   │       │ Id (PK)        │
│ UserName │       │ Title     │       │ RecipeId (FK)  │
│ Email    │       │ AuthorId  │──┐    │ UserId (FK)    │
│ PassHash │       │ CategoryId│  │    │ Body           │
│ Role     │       │ PrepTime  │  │    │ CreatedAt      │
│ CreatedAt│       │ CookTime  │  │    └────────────────┘
└──────────┘       │ Servings  │  │
      │1           │ CreatedAt │  │    ┌────────────────┐
      │            └───────────┘  └──M│   [Rating]     │
      │                  │1            │────────────────│
      │                  │             │ Id (PK)        │
      └────────M─────────┼───────────→│ RecipeId (FK)  │
                         │             │ UserId (FK)    │
                         │             │ Score (1-5)    │
                    ┌────┴──────┐      │ CreatedAt      │
               ┌────┤           ├────┐ └────────────────┘
               │    │           │    │
          ┌────┴───┐     ┌─────┴─────┐    ┌──────────┐
          │Recipe  │     │ RecipeTag  │M──1│  [Tag]   │
          │Ingredi-│     │───────────│    │──────────│
          │ent     │     │ RecipeId   │    │ Id (PK)  │
          │────────│     │ TagId      │    │ Name     │
          │RecipeId│     └───────────┘    └──────────┘
          │Ingredi-│
          │entId   │     ┌───────────┐
          │Quantity │M──1│[Ingredient]│
          │Unit    │     │───────────│
          └────────┘     │ Id (PK)   │
                         │ Name      │
     ┌───────────┐       └───────────┘
     │[Category] │
     │───────────│
     │ Id (PK)   │1────M Recipe.CategoryId
     │ Name      │
     │ ParentId  │──→ self (nullable, for hierarchy)
     └───────────┘

Table Definitions

User

CREATE TABLE dbo.[User] (
    Id              INT             IDENTITY(1,1) NOT NULL,
    UserName        NVARCHAR(100)   NOT NULL,
    Email           NVARCHAR(255)   NOT NULL,
    PasswordHash    NVARCHAR(500)   NOT NULL,
    DisplayName     NVARCHAR(200)   NULL,
    Bio             NVARCHAR(1000)  NULL,
    AvatarUrl       NVARCHAR(500)   NULL,
    Role            NVARCHAR(20)    NOT NULL DEFAULT 'user',
    IsActive        BIT             NOT NULL DEFAULT 1,
    CreatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_User PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_User_UserName UNIQUE (UserName),
    CONSTRAINT UQ_User_Email UNIQUE (Email),
    CONSTRAINT CK_User_Role CHECK (Role IN ('user', 'admin', 'moderator'))
);

CREATE NONCLUSTERED INDEX IX_User_Email ON dbo.[User] (Email);
CREATE NONCLUSTERED INDEX IX_User_Role ON dbo.[User] (Role) WHERE IsActive = 1;

Category

CREATE TABLE dbo.Category (
    Id              INT             IDENTITY(1,1) NOT NULL,
    Name            NVARCHAR(100)   NOT NULL,
    Slug            NVARCHAR(100)   NOT NULL,
    Description     NVARCHAR(500)   NULL,
    ParentId        INT             NULL,
    SortOrder       INT             NOT NULL DEFAULT 0,
    CreatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Category PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Category_Slug UNIQUE (Slug),
    CONSTRAINT FK_Category_Parent FOREIGN KEY (ParentId) REFERENCES dbo.Category(Id)
);

CREATE NONCLUSTERED INDEX IX_Category_ParentId ON dbo.Category (ParentId);

Recipe

CREATE TABLE dbo.Recipe (
    Id              INT             IDENTITY(1,1) NOT NULL,
    Title           NVARCHAR(200)   NOT NULL,
    Slug            NVARCHAR(200)   NOT NULL,
    Description     NVARCHAR(MAX)   NULL,
    Instructions    NVARCHAR(MAX)   NULL,
    AuthorId        INT             NOT NULL,
    CategoryId      INT             NULL,
    PrepTimeMinutes INT             NULL,
    CookTimeMinutes INT             NULL,
    Servings        INT             NULL,
    Difficulty      NVARCHAR(20)    NOT NULL DEFAULT 'medium',
    ImageUrl        NVARCHAR(500)   NULL,
    IsPublished     BIT             NOT NULL DEFAULT 0,
    ViewCount       INT             NOT NULL DEFAULT 0,
    CreatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Recipe PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Recipe_Slug UNIQUE (Slug),
    CONSTRAINT FK_Recipe_Author FOREIGN KEY (AuthorId) REFERENCES dbo.[User](Id),
    CONSTRAINT FK_Recipe_Category FOREIGN KEY (CategoryId) REFERENCES dbo.Category(Id),
    CONSTRAINT CK_Recipe_Difficulty CHECK (Difficulty IN ('easy', 'medium', 'hard')),
    CONSTRAINT CK_Recipe_PrepTime CHECK (PrepTimeMinutes IS NULL OR PrepTimeMinutes >= 0),
    CONSTRAINT CK_Recipe_CookTime CHECK (CookTimeMinutes IS NULL OR CookTimeMinutes >= 0),
    CONSTRAINT CK_Recipe_Servings CHECK (Servings IS NULL OR Servings > 0)
);

CREATE NONCLUSTERED INDEX IX_Recipe_AuthorId ON dbo.Recipe (AuthorId) INCLUDE (Title, CreatedAt);
CREATE NONCLUSTERED INDEX IX_Recipe_CategoryId ON dbo.Recipe (CategoryId) WHERE IsPublished = 1;
CREATE NONCLUSTERED INDEX IX_Recipe_CreatedAt ON dbo.Recipe (CreatedAt DESC) WHERE IsPublished = 1;
CREATE NONCLUSTERED INDEX IX_Recipe_Title ON dbo.Recipe (Title) INCLUDE (AuthorId, CategoryId, CreatedAt);

Ingredient

CREATE TABLE dbo.Ingredient (
    Id              INT             IDENTITY(1,1) NOT NULL,
    Name            NVARCHAR(200)   NOT NULL,
    CreatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Ingredient PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Ingredient_Name UNIQUE (Name)
);

RecipeIngredient (Junction)

CREATE TABLE dbo.RecipeIngredient (
    RecipeId        INT             NOT NULL,
    IngredientId    INT             NOT NULL,
    Quantity        DECIMAL(10, 2)  NOT NULL,
    Unit            NVARCHAR(50)    NOT NULL,
    SortOrder       INT             NOT NULL DEFAULT 0,
    Notes           NVARCHAR(200)   NULL,

    CONSTRAINT PK_RecipeIngredient PRIMARY KEY CLUSTERED (RecipeId, IngredientId),
    CONSTRAINT FK_RecipeIngredient_Recipe FOREIGN KEY (RecipeId)
        REFERENCES dbo.Recipe(Id) ON DELETE CASCADE,
    CONSTRAINT FK_RecipeIngredient_Ingredient FOREIGN KEY (IngredientId)
        REFERENCES dbo.Ingredient(Id),
    CONSTRAINT CK_RecipeIngredient_Quantity CHECK (Quantity > 0)
);

Tag

CREATE TABLE dbo.Tag (
    Id              INT             IDENTITY(1,1) NOT NULL,
    Name            NVARCHAR(100)   NOT NULL,
    Slug            NVARCHAR(100)   NOT NULL,

    CONSTRAINT PK_Tag PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Tag_Name UNIQUE (Name),
    CONSTRAINT UQ_Tag_Slug UNIQUE (Slug)
);

RecipeTag (Junction)

CREATE TABLE dbo.RecipeTag (
    RecipeId        INT NOT NULL,
    TagId           INT NOT NULL,

    CONSTRAINT PK_RecipeTag PRIMARY KEY CLUSTERED (RecipeId, TagId),
    CONSTRAINT FK_RecipeTag_Recipe FOREIGN KEY (RecipeId)
        REFERENCES dbo.Recipe(Id) ON DELETE CASCADE,
    CONSTRAINT FK_RecipeTag_Tag FOREIGN KEY (TagId)
        REFERENCES dbo.Tag(Id)
);

CREATE NONCLUSTERED INDEX IX_RecipeTag_TagId ON dbo.RecipeTag (TagId);

Comment

CREATE TABLE dbo.Comment (
    Id              INT             IDENTITY(1,1) NOT NULL,
    RecipeId        INT             NOT NULL,
    UserId          INT             NOT NULL,
    ParentId        INT             NULL,
    Body            NVARCHAR(2000)  NOT NULL,
    IsDeleted       BIT             NOT NULL DEFAULT 0,
    CreatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Comment PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Comment_Recipe FOREIGN KEY (RecipeId)
        REFERENCES dbo.Recipe(Id) ON DELETE CASCADE,
    CONSTRAINT FK_Comment_User FOREIGN KEY (UserId)
        REFERENCES dbo.[User](Id),
    CONSTRAINT FK_Comment_Parent FOREIGN KEY (ParentId)
        REFERENCES dbo.Comment(Id)
);

CREATE NONCLUSTERED INDEX IX_Comment_RecipeId ON dbo.Comment (RecipeId, CreatedAt);
CREATE NONCLUSTERED INDEX IX_Comment_UserId ON dbo.Comment (UserId);

Rating

CREATE TABLE dbo.Rating (
    Id              INT             IDENTITY(1,1) NOT NULL,
    RecipeId        INT             NOT NULL,
    UserId          INT             NOT NULL,
    Score           TINYINT         NOT NULL,
    CreatedAt       DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_Rating PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Rating_UserRecipe UNIQUE (UserId, RecipeId),
    CONSTRAINT FK_Rating_Recipe FOREIGN KEY (RecipeId)
        REFERENCES dbo.Recipe(Id) ON DELETE CASCADE,
    CONSTRAINT FK_Rating_User FOREIGN KEY (UserId)
        REFERENCES dbo.[User](Id),
    CONSTRAINT CK_Rating_Score CHECK (Score BETWEEN 1 AND 5)
);

CREATE NONCLUSTERED INDEX IX_Rating_RecipeId ON dbo.Rating (RecipeId) INCLUDE (Score);

ErrorLog (Support Table)

CREATE TABLE dbo.ErrorLog (
    Id              INT             IDENTITY(1,1) NOT NULL,
    ErrorNumber     INT             NULL,
    ErrorSeverity   INT             NULL,
    ErrorState      INT             NULL,
    ErrorLine       INT             NULL,
    ErrorProcedure  NVARCHAR(200)   NULL,
    ErrorMessage    NVARCHAR(4000)  NULL,
    LogDate         DATETIME2(3)    NOT NULL DEFAULT GETUTCDATE(),

    CONSTRAINT PK_ErrorLog PRIMARY KEY CLUSTERED (Id)
);

---

Views

vw_RecipeWithStats

Provides recipes with calculated rating and comment counts.

CREATE OR ALTER VIEW dbo.vw_RecipeWithStats
AS
SELECT
    r.Id,
    r.Title,
    r.Slug,
    r.Description,
    r.AuthorId,
    u.DisplayName AS AuthorName,
    c.Name AS CategoryName,
    r.PrepTimeMinutes,
    r.CookTimeMinutes,
    r.Servings,
    r.Difficulty,
    r.ImageUrl,
    r.IsPublished,
    r.ViewCount,
    r.CreatedAt,
    r.UpdatedAt,
    ISNULL(rs.AvgRating, 0) AS AvgRating,
    ISNULL(rs.RatingCount, 0) AS RatingCount,
    ISNULL(cs.CommentCount, 0) AS CommentCount,
    STRING_AGG(t.Name, ', ') WITHIN GROUP (ORDER BY t.Name) AS Tags
FROM dbo.Recipe r
JOIN dbo.[User] u ON u.Id = r.AuthorId
LEFT JOIN dbo.Category c ON c.Id = r.CategoryId
LEFT JOIN (
    SELECT RecipeId, AVG(CAST(Score AS DECIMAL(3,2))) AS AvgRating, COUNT(*) AS RatingCount
    FROM dbo.Rating
    GROUP BY RecipeId
) rs ON rs.RecipeId = r.Id
LEFT JOIN (
    SELECT RecipeId, COUNT(*) AS CommentCount
    FROM dbo.Comment
    WHERE IsDeleted = 0
    GROUP BY RecipeId
) cs ON cs.RecipeId = r.Id
LEFT JOIN dbo.RecipeTag rt ON rt.RecipeId = r.Id
LEFT JOIN dbo.Tag t ON t.Id = rt.TagId
GROUP BY
    r.Id, r.Title, r.Slug, r.Description, r.AuthorId, u.DisplayName,
    c.Name, r.PrepTimeMinutes, r.CookTimeMinutes, r.Servings, r.Difficulty,
    r.ImageUrl, r.IsPublished, r.ViewCount, r.CreatedAt, r.UpdatedAt,
    rs.AvgRating, rs.RatingCount, cs.CommentCount;
GO

vw_UserProfile

User profile with activity summary.

CREATE OR ALTER VIEW dbo.vw_UserProfile
AS
SELECT
    u.Id,
    u.UserName,
    u.DisplayName,
    u.Bio,
    u.AvatarUrl,
    u.Role,
    u.CreatedAt AS MemberSince,
    COUNT(DISTINCT r.Id) AS RecipeCount,
    COUNT(DISTINCT cm.Id) AS CommentCount,
    COUNT(DISTINCT rt.RecipeId) AS RatedRecipeCount
FROM dbo.[User] u
LEFT JOIN dbo.Recipe r ON r.AuthorId = u.Id AND r.IsPublished = 1
LEFT JOIN dbo.Comment cm ON cm.UserId = u.Id AND cm.IsDeleted = 0
LEFT JOIN dbo.Rating rt ON rt.UserId = u.Id
WHERE u.IsActive = 1
GROUP BY u.Id, u.UserName, u.DisplayName, u.Bio, u.AvatarUrl, u.Role, u.CreatedAt;
GO

---

Stored Procedures

usp_Recipe_Search

Full-text search with filters, pagination, and sorting.

CREATE OR ALTER PROCEDURE dbo.usp_Recipe_Search
    @SearchTerm     NVARCHAR(200)   = NULL,
    @CategoryId     INT             = NULL,
    @TagName        NVARCHAR(100)   = NULL,
    @Difficulty     NVARCHAR(20)    = NULL,
    @MaxPrepTime    INT             = NULL,
    @PageNumber     INT             = 1,
    @PageSize       INT             = 20,
    @SortBy         NVARCHAR(20)    = 'newest',
    @TotalCount     INT             = 0 OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @TotalCount = COUNT(DISTINCT r.Id)
    FROM dbo.Recipe r
    LEFT JOIN dbo.RecipeTag rt ON rt.RecipeId = r.Id
    LEFT JOIN dbo.Tag t ON t.Id = rt.TagId
    WHERE r.IsPublished = 1
        AND (@SearchTerm IS NULL OR r.Title LIKE N'%' + @SearchTerm + N'%'
             OR r.Description LIKE N'%' + @SearchTerm + N'%')
        AND (@CategoryId IS NULL OR r.CategoryId = @CategoryId)
        AND (@TagName IS NULL OR t.Name = @TagName)
        AND (@Difficulty IS NULL OR r.Difficulty = @Difficulty)
        AND (@MaxPrepTime IS NULL OR r.PrepTimeMinutes <= @MaxPrepTime);

    SELECT
        v.Id, v.Title, v.Slug, v.Description, v.AuthorName,
        v.CategoryName, v.PrepTimeMinutes, v.CookTimeMinutes,
        v.Servings, v.Difficulty, v.ImageUrl, v.AvgRating,
        v.RatingCount, v.CommentCount, v.Tags, v.CreatedAt
    FROM dbo.vw_RecipeWithStats v
    WHERE v.IsPublished = 1
        AND (@SearchTerm IS NULL OR v.Title LIKE N'%' + @SearchTerm + N'%'
             OR v.Description LIKE N'%' + @SearchTerm + N'%')
        AND (@CategoryId IS NULL OR v.AuthorName IS NOT NULL AND EXISTS (
            SELECT 1 FROM dbo.Recipe r2 WHERE r2.Id = v.Id AND r2.CategoryId = @CategoryId))
        AND (@Difficulty IS NULL OR v.Difficulty = @Difficulty)
    ORDER BY
        CASE @SortBy
            WHEN 'newest'    THEN v.CreatedAt END DESC,
        CASE @SortBy
            WHEN 'oldest'    THEN v.CreatedAt END ASC,
        CASE @SortBy
            WHEN 'rating'    THEN v.AvgRating END DESC,
        CASE @SortBy
            WHEN 'popular'   THEN v.ViewCount END DESC,
        v.CreatedAt DESC
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;
GO

usp_Recipe_GetById

Retrieve a full recipe with all related data.

CREATE OR ALTER PROCEDURE dbo.usp_Recipe_GetById
    @RecipeId INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Recipe details
    SELECT * FROM dbo.vw_RecipeWithStats WHERE Id = @RecipeId;

    -- Ingredients
    SELECT i.Name, ri.Quantity, ri.Unit, ri.Notes
    FROM dbo.RecipeIngredient ri
    JOIN dbo.Ingredient i ON i.Id = ri.IngredientId
    WHERE ri.RecipeId = @RecipeId
    ORDER BY ri.SortOrder;

    -- Comments (threaded)
    SELECT c.Id, c.ParentId, c.Body, c.CreatedAt,
           u.UserName, u.DisplayName, u.AvatarUrl
    FROM dbo.Comment c
    JOIN dbo.[User] u ON u.Id = c.UserId
    WHERE c.RecipeId = @RecipeId AND c.IsDeleted = 0
    ORDER BY c.CreatedAt;

    -- Increment view count
    UPDATE dbo.Recipe SET ViewCount = ViewCount + 1 WHERE Id = @RecipeId;
END;
GO

---

Migration Script

Run this script to create the full schema from scratch.

-- ============================================================================
-- Recipe Management System — Initial Migration
-- Version: 1.0.0
-- Date: 2026-02-11
-- ============================================================================

BEGIN TRANSACTION;

BEGIN TRY
    -- Tables (in dependency order)
    -- 1. User
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'User' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste User CREATE TABLE from above]
        PRINT 'Created table: User';
    END;

    -- 2. Category
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Category' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste Category CREATE TABLE from above]
        PRINT 'Created table: Category';
    END;

    -- 3. Recipe
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Recipe' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste Recipe CREATE TABLE from above]
        PRINT 'Created table: Recipe';
    END;

    -- 4. Ingredient
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Ingredient' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste Ingredient CREATE TABLE from above]
        PRINT 'Created table: Ingredient';
    END;

    -- 5. RecipeIngredient
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'RecipeIngredient' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste RecipeIngredient CREATE TABLE from above]
        PRINT 'Created table: RecipeIngredient';
    END;

    -- 6. Tag
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Tag' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste Tag CREATE TABLE from above]
        PRINT 'Created table: Tag';
    END;

    -- 7. RecipeTag
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'RecipeTag' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste RecipeTag CREATE TABLE from above]
        PRINT 'Created table: RecipeTag';
    END;

    -- 8. Comment
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Comment' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste Comment CREATE TABLE from above]
        PRINT 'Created table: Comment';
    END;

    -- 9. Rating
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Rating' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste Rating CREATE TABLE from above]
        PRINT 'Created table: Rating';
    END;

    -- 10. ErrorLog
    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ErrorLog' AND schema_id = SCHEMA_ID('dbo'))
    BEGIN
        -- [paste ErrorLog CREATE TABLE from above]
        PRINT 'Created table: ErrorLog';
    END;

    -- Seed data
    INSERT INTO dbo.Category (Name, Slug, Description, SortOrder) VALUES
        (N'Appetizers',  N'appetizers',  N'Starters and small plates', 1),
        (N'Main Course', N'main-course', N'Entrees and main dishes',   2),
        (N'Desserts',    N'desserts',    N'Sweet treats and pastries',  3),
        (N'Beverages',   N'beverages',   N'Drinks and cocktails',      4),
        (N'Soups',       N'soups',       N'Soups and stews',           5);

    INSERT INTO dbo.Tag (Name, Slug) VALUES
        (N'Vegetarian',  N'vegetarian'),
        (N'Vegan',       N'vegan'),
        (N'Gluten-Free', N'gluten-free'),
        (N'Quick',       N'quick'),
        (N'Healthy',     N'healthy'),
        (N'Comfort Food',N'comfort-food'),
        (N'Spicy',       N'spicy'),
        (N'Low-Carb',    N'low-carb');

    PRINT 'Seed data inserted.';

    COMMIT TRANSACTION;
    PRINT 'Migration completed successfully.';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Migration failed: ' + ERROR_MESSAGE();
    THROW;
END CATCH;
GO

Design Decisions

MySQL 8.4 Patterns (2026)

Up-to-date MySQL patterns for relational schema design, query optimization, stored procedures, triggers, and API-oriented workloads.

Version Context

• MySQL 8.4 LTS is the current long-term baseline for modern production usage.
• For this project (XAMPP + MySQL/MariaDB), patterns remain compatible with MySQL 8.x and most MariaDB 10.6+ features.

Schema Design Principles

Naming & Structure

• Use singular table names: user, recipe, review.
• Use id as primary key (INT AUTO_INCREMENT for course scope).
• Include created_at, updated_at in operational tables.
• Add explicit foreign key names and cascading rules.

CREATE TABLE recipe (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(50),
    difficulty ENUM('Easy', 'Medium', 'Hard') NOT NULL DEFAULT 'Medium',
    prep_time INT NOT NULL DEFAULT 0,
    cook_time INT NOT NULL DEFAULT 0,
    servings INT NOT NULL DEFAULT 1,
    author_id INT NOT NULL,
    status ENUM('published', 'pending', 'rejected') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_recipe_author
        FOREIGN KEY (author_id)
        REFERENCES user(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Query Patterns

API List Endpoint Query

SELECT
    r.id,
    r.title,
    r.category,
    r.difficulty,
    r.prep_time,
    r.cook_time,
    r.servings,
    r.created_at,
    u.username AS author_name,
    COUNT(DISTINCT rv.id) AS view_count,
    COUNT(DISTINCT lr.id) AS like_count,
    ROUND(AVG(rev.rating), 2) AS avg_rating
FROM recipe r
JOIN user u ON u.id = r.author_id
LEFT JOIN recipe_view rv ON rv.recipe_id = r.id
LEFT JOIN like_record lr ON lr.recipe_id = r.id
LEFT JOIN review rev ON rev.recipe_id = r.id
WHERE r.status = 'published'
GROUP BY r.id, u.username
ORDER BY r.created_at DESC
LIMIT 20 OFFSET 0;

Search Query Pattern

SELECT
    r.id,
    r.title,
    r.description,
    r.category,
    r.difficulty,
    u.username AS author_name
FROM recipe r
JOIN user u ON u.id = r.author_id
WHERE r.status = 'published'
  AND (
      r.title LIKE CONCAT('%', :search, '%')
      OR r.description LIKE CONCAT('%', :search, '%')
  )
ORDER BY r.created_at DESC
LIMIT :limit OFFSET :offset;

Indexing Strategy

Operational Indexes

CREATE INDEX idx_user_email ON user(email);
CREATE INDEX idx_recipe_author_status ON recipe(author_id, status);
CREATE INDEX idx_recipe_category_status ON recipe(category, status);
CREATE INDEX idx_review_recipe_user ON review(recipe_id, user_id);
CREATE INDEX idx_recipe_view_recipe_viewed ON recipe_view(recipe_id, viewed_at);
CREATE INDEX idx_daily_stat_date ON daily_stat(stat_date);

Notes

• Index columns used in JOIN, WHERE, ORDER BY.
• Keep indexes minimal; each write pays index maintenance cost.
• Verify with EXPLAIN ANALYZE.

Stored Procedure Pattern

DELIMITER $$

CREATE PROCEDURE usp_CreateRecipe (
    IN p_title VARCHAR(200),
    IN p_description TEXT,
    IN p_category VARCHAR(50),
    IN p_difficulty VARCHAR(10),
    IN p_prep_time INT,
    IN p_cook_time INT,
    IN p_servings INT,
    IN p_author_id INT
)
BEGIN
    DECLARE v_recipe_id INT;

    START TRANSACTION;

    INSERT INTO recipe (
        title, description, category, difficulty,
        prep_time, cook_time, servings, author_id,
        status, created_at, updated_at
    ) VALUES (
        p_title, p_description, p_category, p_difficulty,
        p_prep_time, p_cook_time, p_servings, p_author_id,
        'pending', NOW(), NOW()
    );

    SET v_recipe_id = LAST_INSERT_ID();

    COMMIT;

    SELECT v_recipe_id AS recipe_id;
END$$

DELIMITER ;

Trigger Pattern

DELIMITER $$

CREATE TRIGGER trg_RecipeView_UpdateStat
AFTER INSERT ON recipe_view
FOR EACH ROW
BEGIN
    INSERT INTO daily_stat (
        stat_date,
        recipe_view_count,
        page_view_count,
        active_user_count,
        new_user_count,
        created_at,
        updated_at
    )
    VALUES (
        DATE(NEW.viewed_at),
        1,
        0,
        0,
        0,
        NOW(),
        NOW()
    )
    ON DUPLICATE KEY UPDATE
        recipe_view_count = recipe_view_count + 1,
        updated_at = NOW();
END$$

DELIMITER ;

View Pattern

CREATE OR REPLACE VIEW vw_recipe_with_stat AS
SELECT
    r.id,
    r.title,
    r.category,
    r.difficulty,
    r.status,
    r.author_id,
    u.username AS author_name,
    COUNT(DISTINCT rv.id) AS view_count,
    COUNT(DISTINCT lr.id) AS like_count,
    ROUND(AVG(rev.rating), 2) AS avg_rating,
    r.created_at,
    r.updated_at
FROM recipe r
JOIN user u ON u.id = r.author_id
LEFT JOIN recipe_view rv ON rv.recipe_id = r.id
LEFT JOIN like_record lr ON lr.recipe_id = r.id
LEFT JOIN review rev ON rev.recipe_id = r.id
GROUP BY
    r.id, r.title, r.category, r.difficulty, r.status,
    r.author_id, u.username, r.created_at, r.updated_at;

Performance Workflow

Use EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT r.id, r.title, u.username
FROM recipe r
JOIN user u ON u.id = r.author_id
WHERE r.status = 'published'
ORDER BY r.created_at DESC
LIMIT 20;

Check cardinality and selectivity

• High-selectivity columns are better index candidates.
• Composite index order matters: put most selective + most commonly filtered prefix first.

Data Integrity Patterns

• Enforce one review per user/recipe: UNIQUE (user_id, recipe_id)
• Enforce one favorite per user/recipe: UNIQUE (user_id, recipe_id)
• Enforce one like per user/recipe: UNIQUE (user_id, recipe_id)

ALTER TABLE review
ADD CONSTRAINT uq_review_user_recipe UNIQUE (user_id, recipe_id);

Backup & Restore Basics

# Backup
mysqldump -u root -p recipe_sharing_system > backup.sql

# Restore
mysql -u root -p recipe_sharing_system < backup.sql

References

• MySQL 8.4 Reference Manual: https://dev.mysql.com/doc/refman/8.4/en/
• MySQL Performance Schema: https://dev.mysql.com/doc/refman/8.4/en/performance-schema.html
• EXPLAIN Statement: https://dev.mysql.com/doc/refman/8.4/en/explain.html
• InnoDB Locking: https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

MySQL Query Patterns (from MySQL 9.4 Documentation)

Excerpted from official MySQL 9.4 documentation https://dev.mysql.com/doc/refman/9.4/en/.

SELECT JOIN optimization

When optimizing SELECT statements with joins, ensure indexes exist on join columns and foreign keys. This improves performance by allowing MySQL to use indexes instead of table scans.

SELECT t1.col1, t2.col2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.fk_id
WHERE t1.filter_col = 'value';

EXPLAIN SELECT for query analysis

Use EXPLAIN SELECT to understand how MySQL executes your query and which indexes it uses.

EXPLAIN SELECT * FROM your_table;

SHOW CREATE PROCEDURE

View the exact definition of a stored procedure.

SHOW CREATE PROCEDURE procedure_name;

SHOW PROCEDURE STATUS

List all stored procedures and their status.

SHOW PROCEDURE STATUS;

SHOW PROCEDURE CODE

Get the source code of a stored procedure for debugging or documentation.

SHOW PROCEDURE CODE FOR 'procedure_name';

ALTER VIEW

Modify an existing view's SELECT statement.

ALTER VIEW view_name AS
    new_select_statement;

Stored procedure with conditional logic

IF name IS NULL then
  CALL p1();
ELSE
  CALL p2();
END IF;

UPDATE view with join

UPDATE vjoin SET c=c+1;

Source

• MySQL 9.4 Reference: https://dev.mysql.com/doc/refman/9.4/en/
• SELECT optimization: https://dev.mysql.com/doc/refman/9.4/en/select-optimization
• Stored programs: https://dev.mysql.com/doc/refman/9.4/en/stored-programs-defining.html

SQL Server Performance Tuning Guide

Execution Plan Reading

Requesting Plans

-- Estimated plan (no execution)
SET SHOWPLAN_XML ON;
GO
SELECT ... FROM dbo.Recipe WHERE ...;
GO
SET SHOWPLAN_XML OFF;

-- Actual plan (with execution)
SET STATISTICS XML ON;
SELECT ... FROM dbo.Recipe WHERE ...;
SET STATISTICS XML OFF;

Key Operators to Watch

Cost Analysis

• Estimated vs Actual Rows: Large discrepancy signals stale statistics
• Estimated Subtree Cost: Relative cost within the plan (not wall-clock time)
• Actual Executions: How many times an operator ran (watch for nested loop inflation)
• Memory Grant: Check for excessive grants or spills to tempdb

Index Tuning

Missing Index DMVs

SELECT
    CONVERT(DECIMAL(18,2), migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans)) AS ImprovementScore,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY ImprovementScore DESC;

Index Usage Statistics

Find unused indexes consuming write overhead.

SELECT
    OBJECT_NAME(ius.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE ius.database_id = DB_ID()
    AND OBJECTPROPERTY(ius.object_id, 'IsUserTable') = 1
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups ASC;

Index Fragmentation

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Maintenance thresholds:

• 10-30% fragmentation → ALTER INDEX REORGANIZE
• \>30% fragmentation → ALTER INDEX REBUILD
• <1000 pages → fragmentation is irrelevant

Query Optimization

Parameter Sniffing

Problem: First execution compiles a plan optimized for initial parameter values; subsequent calls with different data distributions get a suboptimal plan.

Solutions:

-- Option 1: OPTIMIZE FOR UNKNOWN
SELECT * FROM dbo.Recipe
WHERE CategoryId = @CategoryId
OPTION (OPTIMIZE FOR (@CategoryId UNKNOWN));

-- Option 2: RECOMPILE for volatile parameters
SELECT * FROM dbo.Recipe
WHERE CreatedAt > @StartDate
OPTION (RECOMPILE);

-- Option 3: Local variable assignment (breaks sniffing)
DECLARE @LocalCategoryId INT = @CategoryId;
SELECT * FROM dbo.Recipe
WHERE CategoryId = @LocalCategoryId;

Statistics Management

-- Check statistics freshness
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.Recipe')
ORDER BY sp.last_updated;

-- Manual update with full scan
UPDATE STATISTICS dbo.Recipe WITH FULLSCAN;

-- Update specific statistic
UPDATE STATISTICS dbo.Recipe IX_Recipe_CategoryId WITH FULLSCAN;

Cardinality Estimation

When the optimizer misestimates row counts:

-- Force legacy CE for a specific query
SELECT * FROM dbo.Recipe
WHERE CategoryId = @Id AND IsPublished = 1
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

-- Check CE version in use
SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';

Wait Statistics Analysis

Top Waits

WITH WaitStats AS (
    SELECT
        wait_type,
        wait_time_ms / 1000.0 AS wait_time_sec,
        signal_wait_time_ms / 1000.0 AS signal_wait_sec,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
        waiting_tasks_count,
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
        'SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH',
        'WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE',
        'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
        'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT',
        'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
        'XE_DISPATCHER_WAIT','HADR_FILESTREAM_IOMGR_IOCOMPLETION'
    )
)
SELECT TOP 20
    wait_type,
    wait_time_sec,
    resource_wait_sec,
    signal_wait_sec,
    waiting_tasks_count,
    CAST(pct AS DECIMAL(5,2)) AS pct
FROM WaitStats
ORDER BY wait_time_sec DESC;

Common Wait Types and Actions

TempDB Optimization

-- Check tempdb contention
SELECT
    session_id, wait_type, wait_duration_ms, resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
    AND resource_description LIKE '2:%';  -- database_id 2 = tempdb

-- Best practices:
-- 1. Multiple data files (1 per logical CPU, up to 8)
-- 2. Equal size for proportional fill
-- 3. Trace flag 1118 (SQL 2014 and earlier) for uniform extent allocation
-- 4. Pre-size files to avoid auto-growth during load

Memory Pressure Indicators

-- Buffer pool usage
SELECT
    (total_physical_memory_kb / 1024) AS TotalPhysicalMemoryMB,
    (available_physical_memory_kb / 1024) AS AvailableMemoryMB,
    (total_page_file_kb / 1024) AS TotalPageFileMB,
    (available_page_file_kb / 1024) AS AvailablePageFileMB,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- Page life expectancy (higher is better, <300 is concerning)
SELECT
    object_name, counter_name, cntr_value AS PageLifeExpectancy
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
    AND object_name LIKE '%Buffer Manager%';

-- Memory grants pending
SELECT
    object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending';

Query Store

Enable and Configure

ALTER DATABASE [RecipeDB] SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 30,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

Find Regressed Queries

SELECT TOP 20
    qsq.query_id,
    qsp.plan_id,
    qsqt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.count_executions,
    qsp.is_forced_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats rs ON qsp.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE rsi.start_time > DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Force a Known-Good Plan

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 7;

Common Anti-Patterns and Fixes

T-SQL Common Patterns Reference

UPSERT with MERGE

Insert or update in a single atomic statement.

MERGE INTO dbo.Recipe AS target
USING (SELECT @RecipeId AS Id, @Title AS Title, @Description AS Description) AS source
ON target.Id = source.Id
WHEN MATCHED THEN
    UPDATE SET
        Title = source.Title,
        Description = source.Description,
        UpdatedAt = GETUTCDATE()
WHEN NOT MATCHED THEN
    INSERT (Id, Title, Description, CreatedAt)
    VALUES (source.Id, source.Title, source.Description, GETUTCDATE())
OUTPUT $action, inserted.Id;

Pagination with OFFSET FETCH

Standard keyset-free pagination for sorted results.

SELECT r.Id, r.Title, r.CreatedAt
FROM dbo.Recipe r
WHERE r.IsPublished = 1
ORDER BY r.CreatedAt DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

With total count (single query):

SELECT
    r.Id, r.Title, r.CreatedAt,
    COUNT(*) OVER() AS TotalCount
FROM dbo.Recipe r
WHERE r.IsPublished = 1
ORDER BY r.CreatedAt DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

CTE Patterns

Simple CTE for Readability

WITH ActiveUsers AS (
    SELECT Id, UserName, Email
    FROM dbo.[User]
    WHERE IsActive = 1 AND LastLoginDate > DATEADD(DAY, -30, GETUTCDATE())
)
SELECT au.UserName, COUNT(r.Id) AS RecipeCount
FROM ActiveUsers au
JOIN dbo.Recipe r ON r.AuthorId = au.Id
GROUP BY au.UserName;

Recursive CTE for Hierarchies

WITH CategoryTree AS (
    -- Anchor: top-level categories
    SELECT Id, Name, ParentId, 0 AS Level, CAST(Name AS NVARCHAR(500)) AS Path
    FROM dbo.Category
    WHERE ParentId IS NULL

    UNION ALL

    -- Recursive: child categories
    SELECT c.Id, c.Name, c.ParentId, ct.Level + 1,
           CAST(ct.Path + ' > ' + c.Name AS NVARCHAR(500))
    FROM dbo.Category c
    JOIN CategoryTree ct ON c.ParentId = ct.Id
    WHERE ct.Level < 10  -- safety limit
)
SELECT Id, Name, Level, Path
FROM CategoryTree
ORDER BY Path;

Windowed Aggregation CTE

WITH MonthlyStats AS (
    SELECT
        YEAR(CreatedAt) AS Yr,
        MONTH(CreatedAt) AS Mo,
        COUNT(*) AS RecipeCount,
        SUM(COUNT(*)) OVER (ORDER BY YEAR(CreatedAt), MONTH(CreatedAt)) AS RunningTotal
    FROM dbo.Recipe
    GROUP BY YEAR(CreatedAt), MONTH(CreatedAt)
)
SELECT Yr, Mo, RecipeCount, RunningTotal
FROM MonthlyStats
ORDER BY Yr, Mo;

PIVOT / UNPIVOT

PIVOT — Rows to Columns

SELECT UserId, [1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr
FROM (
    SELECT AuthorId AS UserId, MONTH(CreatedAt) AS Mo, Id
    FROM dbo.Recipe
    WHERE YEAR(CreatedAt) = 2026
) src
PIVOT (
    COUNT(Id) FOR Mo IN ([1], [2], [3], [4])
) pvt;

UNPIVOT — Columns to Rows

SELECT UserId, MonthName, RecipeCount
FROM (
    SELECT UserId, Jan, Feb, Mar, Apr
    FROM dbo.MonthlyRecipeSummary
) src
UNPIVOT (
    RecipeCount FOR MonthName IN (Jan, Feb, Mar, Apr)
) unpvt;

Dynamic SQL with sp_executesql

Safe parameterized dynamic SQL.

DECLARE @SQL NVARCHAR(MAX);
DECLARE @Params NVARCHAR(500);
DECLARE @WhereClause NVARCHAR(MAX) = N'WHERE 1=1';

IF @CategoryId IS NOT NULL
    SET @WhereClause += N' AND r.CategoryId = @pCategoryId';

IF @SearchTerm IS NOT NULL
    SET @WhereClause += N' AND r.Title LIKE @pSearchTerm';

SET @SQL = N'
    SELECT r.Id, r.Title, r.CreatedAt
    FROM dbo.Recipe r
    ' + @WhereClause + N'
    ORDER BY r.CreatedAt DESC
    OFFSET @pOffset ROWS FETCH NEXT @pPageSize ROWS ONLY';

SET @Params = N'@pCategoryId INT, @pSearchTerm NVARCHAR(200), @pOffset INT, @pPageSize INT';

EXEC sp_executesql @SQL, @Params,
    @pCategoryId = @CategoryId,
    @pSearchTerm = @SearchTerm,
    @pOffset = @Offset,
    @pPageSize = @PageSize;

TRY/CATCH Error Handling

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO dbo.Recipe (Title, AuthorId, CategoryId, CreatedAt)
    VALUES (@Title, @AuthorId, @CategoryId, GETUTCDATE());

    DECLARE @NewId INT = SCOPE_IDENTITY();

    INSERT INTO dbo.RecipeTag (RecipeId, TagId)
    SELECT @NewId, t.Id
    FROM dbo.Tag t
    WHERE t.Name IN (SELECT value FROM STRING_SPLIT(@Tags, ','));

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorProc NVARCHAR(200) = ERROR_PROCEDURE();

    INSERT INTO dbo.ErrorLog (Message, Severity, State, Line, Procedure, LogDate)
    VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorProc, GETUTCDATE());

    THROW;
END CATCH;

Table-Valued Parameters

Define the Type

CREATE TYPE dbo.IngredientTableType AS TABLE (
    Name        NVARCHAR(200)   NOT NULL,
    Quantity    DECIMAL(10, 2)  NOT NULL,
    Unit        NVARCHAR(50)    NOT NULL
);

Use in a Stored Procedure

CREATE PROCEDURE dbo.usp_Recipe_AddIngredients
    @RecipeId INT,
    @Ingredients dbo.IngredientTableType READONLY
AS
BEGIN
    INSERT INTO dbo.RecipeIngredient (RecipeId, IngredientId, Quantity, Unit)
    SELECT @RecipeId, i.Id, tvp.Quantity, tvp.Unit
    FROM @Ingredients tvp
    JOIN dbo.Ingredient i ON i.Name = tvp.Name;
END;

Temporal Tables

Create with System Versioning

CREATE TABLE dbo.Recipe (
    Id              INT IDENTITY(1,1) PRIMARY KEY,
    Title           NVARCHAR(200) NOT NULL,
    Description     NVARCHAR(MAX),
    SysStartTime    DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    SysEndTime      DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.RecipeHistory));

Query Historical Data

-- State at a specific point in time
SELECT * FROM dbo.Recipe
FOR SYSTEM_TIME AS OF '2026-01-15T12:00:00';

-- All changes in a date range
SELECT * FROM dbo.Recipe
FOR SYSTEM_TIME BETWEEN '2026-01-01' AND '2026-02-01';

JSON Operations

FOR JSON — Rows to JSON

SELECT r.Id, r.Title,
    (SELECT t.Name FROM dbo.Tag t
     JOIN dbo.RecipeTag rt ON rt.TagId = t.Id
     WHERE rt.RecipeId = r.Id
     FOR JSON PATH) AS Tags
FROM dbo.Recipe r
WHERE r.Id = @RecipeId
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

OPENJSON — JSON to Rows

DECLARE @json NVARCHAR(MAX) = N'[
    {"name": "Flour", "quantity": 2.5, "unit": "cups"},
    {"name": "Sugar", "quantity": 1, "unit": "cup"}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    Name     NVARCHAR(200) '$.name',
    Quantity DECIMAL(10,2) '$.quantity',
    Unit     NVARCHAR(50)  '$.unit'
);

STRING_AGG

Concatenate values from multiple rows into a single string.

SELECT
    r.Id,
    r.Title,
    STRING_AGG(t.Name, ', ') WITHIN GROUP (ORDER BY t.Name) AS Tags
FROM dbo.Recipe r
JOIN dbo.RecipeTag rt ON rt.RecipeId = r.Id
JOIN dbo.Tag t ON t.Id = rt.TagId
GROUP BY r.Id, r.Title;

Window Functions

ROW_NUMBER — Unique Sequential Ranking

SELECT
    ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY CreatedAt DESC) AS RowNum,
    Id, Title, CategoryId
FROM dbo.Recipe;

RANK and DENSE_RANK

SELECT
    RANK() OVER (ORDER BY AvgRating DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY AvgRating DESC) AS DenseRank,
    Id, Title, AvgRating
FROM dbo.Recipe;

LAG and LEAD — Access Adjacent Rows

SELECT
    Id, Title, CreatedAt,
    LAG(Title, 1) OVER (ORDER BY CreatedAt) AS PreviousRecipe,
    LEAD(Title, 1) OVER (ORDER BY CreatedAt) AS NextRecipe,
    DATEDIFF(DAY,
        LAG(CreatedAt, 1) OVER (ORDER BY CreatedAt),
        CreatedAt
    ) AS DaysSincePrevious
FROM dbo.Recipe
WHERE AuthorId = @AuthorId;

Running Total

SELECT
    Id, Title, CreatedAt,
    COUNT(*) OVER (ORDER BY CreatedAt ROWS UNBOUNDED PRECEDING) AS RunningCount,
    SUM(ViewCount) OVER (ORDER BY CreatedAt ROWS UNBOUNDED PRECEDING) AS RunningViews
FROM dbo.Recipe
WHERE AuthorId = @AuthorId
ORDER BY CreatedAt;