SQL Server

T-SQL data type guidelines. Prefer modern, non-deprecated types and appropriate precision for financial data.

# T-SQL Data Type Guidelines

- Prefer `VARCHAR(n)` over `TEXT`; prefer `NVARCHAR(n)` over `NTEXT`.
- Prefer `DATETIME2` over `DATETIME` for new columns (better precision and range).
- Use `DECIMAL(p,s)` or `NUMERIC(p,s)` for money and financial data; avoid `FLOAT` or `REAL` (imprecise).
- Avoid deprecated types: `IMAGE`, `TEXT`, `NTEXT`.
- Use `VARBINARY(MAX)` instead of `IMAGE` for binary data.
- Specify explicit lengths for `VARCHAR`/`NVARCHAR`; avoid `MAX` unless necessary for large values.

Baseline project context for MSSQL/SQL Server development. Establishes tech stack and MCP integration.

# Project Context

This project uses **Microsoft SQL Server** (MSSQL) and **T-SQL** for database development.

- **Database**: SQL Server (on-premises, Azure SQL, or compatible)
- **SQL dialect**: T-SQL
- **MCP**: When [mssql-mcp](https://github.com/eamonboyle/mssql-mcp) is configured, follow a **schema-first** workflow: prefer `list_databases`, `list_table` / `list_objects`, `describe_relationships`, and `analyze_table` before `read_data`, `search_data`, or `explain_query`. Use `describe_table` (optional `schemaName`) and `describe_object` for definitions. **`READONLY=true`** omits write and DDL tools. **`ENABLE_DDL`** defaults false: DDL tools (`create_table`, `create_index`, `drop_table`) only appear when **`ENABLE_DDL=true`**. When **`REQUIRE_WRITE_PREVIEW`** is enabled (default), run **`preview_update` / `preview_delete`** before **`update_data` / `delete_data`** and pass the **`previewToken`** with **`confirmed=true`**.

Configure via `mcp.json` or env: `SERVER_NAME`; `DATABASE_NAME` (optional if `DATABASES` is set); `DB_USER` / `DB_PASSWORD` for SQL auth; optional `DATABASES`, `READONLY`, `CONNECTION_TIMEOUT`, `QUERY_TIMEOUT_MS`, `MAX_ROWS`, `TRUST_SERVER_CERTIFICATE`, `ENABLE_DDL`, `MAX_WRITE_ROWS`, `REQUIRE_WRITE_PREVIEW`, `MCP_TRANSPORT`, `MCP_HTTP_HOST`, `MCP_HTTP_PORT`, `MCP_BASE_URL`.

MSSQL security and SQL injection prevention guidelines.

# MSSQL Security Guidelines

- Always use parameterized queries or stored procedures; never concatenate user input into SQL strings.
- Avoid dynamic SQL unless necessary; when used, validate and sanitize all inputs.
- Use `QUOTENAME()` and `REPLACE()` for object names in dynamic SQL when building identifiers.
- Apply the principle of least privilege: grant only required permissions to roles and users.
- Never store credentials in code or config files; use environment variables or secrets managers.
- Use `EXECUTE AS` with explicit, limited-privilege users when elevated context is required.
- Avoid `WITH (NOLOCK)` unless you explicitly accept dirty reads; document the trade-off.
- Encrypt sensitive data at rest and in transit when applicable.
- Audit and log privileged operations (DDL, schema changes, bulk operations).

T-SQL naming conventions for tables, columns, stored procedures, and identifiers.

# T-SQL Naming Conventions

## General

- Use underscores to separate words (e.g., `first_name` not `firstName`).
- Avoid abbreviations unless commonly understood.
- Names must begin with a letter and may not end with an underscore.
- Keep the length to a maximum of 30 characters.
- Use collective terms instead of plurals (e.g., `staff` instead of `employees`).

## Tables

- Avoid underscores in table names unless designating a suffix (e.g., `staff_backup20170101`).
- Avoid concatenating two table names for relationship tables; prefer descriptive names (e.g., `services` not `cars_mechanics`).
- Never give a table the same name as one of its columns.
- Do not prefix with `tbl` or Hungarian notation.
- Use collective or singular form (e.g., `staff` or `employee`).

## Columns

- Use lowercase except for proper nouns.
- Do not add a column with the same name as its table.
- Always use singular names.
- Use standard suffixes where appropriate: `_id`, `_date`, `_addr`, `_size`, `_count`, `_total`, `_num`, `_name`, `_flag`, `_status`.

## Stored Procedures

- Prefix with `usp_` (not `sp_` which conflicts with system stored procedures).
- The name must contain a verb.

## Aliasing

- Use explicit alias keywords: `[dbo].[employee] AS [emp]`.
- For computed data (`SUM()`, `AVG()`), use the name you would give it as a schema column.
- Correlation names should relate to the object (e.g., first letter of each word).

MCP server: mssql

{
  "command": "npx",
  "args": [
    "-y",
    "@eamonboyle/mssql-mcp"
  ],
  "env": {
    "SERVER_NAME": "localhost",
    "DATABASE_NAME": "YourDatabase",
    "DATABASES": "",
    "DB_USER": "",
    "DB_PASSWORD": "",
    "READONLY": "false",
    "CONNECTION_TIMEOUT": "30",
    "QUERY_TIMEOUT_MS": "30000",
    "MAX_ROWS": "10000",
    "TRUST_SERVER_CERTIFICATE": "false",
    "MCP_TRANSPORT": "stdio",
    "MCP_HTTP_HOST": "127.0.0.1",
    "MCP_HTTP_PORT": "3333",
    "MCP_BASE_URL": "",
    "ENABLE_DDL": "false",
    "MAX_WRITE_ROWS": "100",
    "REQUIRE_WRITE_PREVIEW": "true"
  }
}

Security-focused reviewer for T-SQL and MSSQL code. Checks for SQL injection, credential handling, dynamic SQL risks, and RBAC.

# MSSQL Security Reviewer

You are a security-focused reviewer for Microsoft SQL Server code. Prioritize concrete, high-impact findings.

## Review focus

1. **SQL injection**: Parameterized queries vs string concatenation; dynamic SQL with user input; `EXEC`/`sp_executesql` usage.
2. **Credential handling**: Hardcoded passwords, connection strings in code, secrets in config files.
3. **Dynamic SQL risks**: Unvalidated object names, unescaped identifiers, `QUOTENAME` usage.
4. **RBAC and permissions**: Overly broad grants, `EXECUTE AS` misuse, principle of least privilege.
5. **Sensitive data**: Unencrypted PII, logging of credentials or tokens, exposure in error messages.

Full T-SQL code reviewer for style, performance, maintainability, and alignment with project rules.

# T-SQL Code Reviewer

You are a comprehensive T-SQL code reviewer. Evaluate SQL Server code for correctness, style, performance, and maintainability.

## Review focus

1. **Style and conventions**: Naming (underscores, `usp_` prefix), semicolons, `TRY...CATCH`, `THROW` vs `RAISERROR`, square brackets, ISO-8601 dates.
2. **Performance**: Missing indexes, implicit conversions, scalar UDFs, NOLOCK usage, large unbounded result sets.
3. **Maintainability**: Readability, redundant SQL, commented-out code, consistent formatting.
4. **Correctness**: Logic errors, NULL handling, join conditions, transaction boundaries.
5. **Alignment**: Ensure code follows the project's T-SQL rules and security guidelines.

Use mssql-mcp tools to explore SQL Server schema before writing queries. Use when you need tables, views, routines, column details, or safe reads.

# MSSQL Schema Explorer

## When to use

- Before writing new queries or stored procedures
- When refactoring or modifying existing schema
- When debugging query issues and need to verify column types or constraints
- When documenting database structure or locating views/procs/triggers

## Instructions

1. **Schema first**: Prefer listing and describing before `read_data`. When `READONLY=true` on the server, only read-class tools are available.
2. **Databases**: `list_databases` when you need to confirm which databases are reachable or pick a `databaseName` for later calls.
3. **List tables**: `list_table` — optional `parameters` (schema names), `databaseName`. Results use `{ name: "schema.table" }` entries.
4. **Broader discovery**: `list_objects` — optional `objectTypes` (`table`, `view`, `procedure`, `function`, `trigger`), `schemaName`, `databaseName`.
5. **Table shape**: `describe_table` — `tableName`, optional `schemaName`, `databaseName`.
6. **Relationships**: Prefer `describe_relationships` or `list_foreign_keys` when modeling joins, cascades, or impact of changes; use `analyze_table` for a summarized view before heavy reads or writes.
7. **Views/procs/etc.**: `describe_object` — `objectName`, optional `objectTypes`, `schemaName`, `databaseName`.
8. **Search rows**: `search_data` — parameterized `LIKE` across named columns (good alternative to hand-written `SELECT` for exploration).
9. **Plans**: `explain_query` — estimated plan for a `SELECT` that passes the same guards as `read_data`.
10. **Sample rows**: `read_data` only after you know table/column names; keep queries minimal and respect `MAX_ROWS`.
11. **Destructive writes**: Before `update_data` or `delete_data`, run `preview_update` or `preview_delete` when `REQUIRE_WRITE_PREVIEW` is enabled (default); use the returned `previewToken` with `confirmed=true` on the matching write. Respect `MAX_WRITE_ROWS`.
12. On clients that support MCP resources/prompts, use `explore_schema` / `draft_safe_select` and table/object/**dependency** resources when offered.

## Prerequisites

mssql-mcp must be configured: at minimum `SERVER_NAME`; `DATABASE_NAME` or `DATABASES`; credentials for SQL auth unless using integrated auth per the driver. See repo `README.md` and [upstream docs](https://github.com/eamonboyle/mssql-mcp).

Review T-SQL for correctness, performance, security, and style compliance. Use when preparing a PR, auditing SQL changes, or validating stored procedures.

# SQL Reviewer

## When to use

- Before opening a pull request with SQL changes
- After writing or modifying stored procedures, views, or scripts
- When validating risky DML or DDL changes
- When reviewing migration scripts

## Instructions

1. **Correctness**: Identify logic errors, missing NULL handling, and incorrect join conditions.
2. **Performance**: Flag missing indexes on filtered/joined columns, N+1 patterns, implicit conversions, and scalar UDFs in SELECT lists.
3. **Security**: Check for SQL injection risks, dynamic SQL without validation, and overly broad permissions.
4. **Style**: Verify alignment with T-SQL coding standards (semicolons, naming, `TRY...CATCH`, `THROW` vs `RAISERROR`).
5. **Recommendations**: Provide concrete fixes with minimal churn; call out missing tests or validation steps where relevant.

Write idempotent migration scripts for schema changes and versioned deployments. Use when creating migration scripts, altering schema, or preparing versioned releases.

# T-SQL Migration Writer

## When to use

- Writing migration scripts for schema changes
- Preparing versioned deployments (e.g. flyway, sqlpackage, custom tooling)
- Adding or altering tables, columns, indexes, constraints
- Refactoring database structure across environments

## Instructions

1. **Idempotent patterns**: Use `IF NOT EXISTS` / `IF EXISTS` so migrations can run safely multiple times.
2. **Transaction wrapping**: Wrap DDL in explicit transactions; roll back on error.
3. **Ordering**: Create dependencies before dependents (e.g. tables before foreign keys, base tables before views).
4. **Rollback**: Document or implement rollback steps where feasible.
5. **Naming**: Use consistent migration naming (e.g. `YYYYMMDD_HHMM_description.sql`).
6. **Schema**: Prefer explicit schema (`[dbo]`) and two-part names.

Identify T-SQL performance issues: missing indexes, implicit conversions, scalar UDFs, and NOLOCK misuse. Use when tuning slow queries or reviewing execution plans.

# T-SQL Optimizer

## When to use

- When queries are slow or timing out
- When reviewing execution plans
- When adding or modifying indexes
- When refactoring stored procedures for performance

## Instructions

1. **Missing indexes**: Look for WHERE, JOIN, and ORDER BY columns that are not indexed. Suggest covering indexes for frequently filtered columns.
2. **Implicit conversions**: Flag comparisons where column and literal types differ (e.g., `varchar` column compared to `int`), causing index scans.
3. **Scalar UDFs**: Identify user-defined functions in SELECT lists; these prevent parallelism and run row-by-row. Suggest inline TVFs or computed columns where appropriate.
4. **NOLOCK / READ UNCOMMITTED**: Note usage and recommend explicit documentation of dirty-read trade-offs; prefer `READ COMMITTED SNAPSHOT` when applicable.
5. **Large result sets**: Flag SELECT * without TOP or pagination when tables are large.
6. **Temp tables vs table variables**: Recommend temp tables for larger datasets; table variables have no statistics.

T-SQL coding standards for readable, maintainable SQL Server code. Based on the T-SQL style guide.

# T-SQL Coding Standards

- End each statement with a semicolon.
- Use `TRY...CATCH` for operationalized DML statements.
- Use `THROW` instead of `RAISERROR` on SQL Server 2012 or later.
- Store ISO-8601 compliant datetime information (`YYYY-MM-DD HH:MM:SS.SSSSS`) with `DATE`, `TIME`, `DATETIME2`, and `DATETIMEOFFSET`.
- Use standard SQL functions instead of Microsoft-specific functions when functionality is the same (portability).
- Use two-part names for isolated databases and three-part names when outside databases are utilized.
- Wrap object names in square brackets.
- Use consistent and descriptive identifiers and names.
- Use underscores to separate words; avoid camelCase.
- Include comments where necessary: use `/* */` for blocks or `--` for single lines.
- Keep code succinct and devoid of redundant SQL.
- Avoid commenting out old code; use source control instead.
- Avoid quoted identifiers unless necessary for SQL-92 compatibility.