agentleFS
G1JoshiG1Joshi/Agent-Skills/skills/databases/sqlserver/SKILL.md

sqlserver

Skill15 starsChanged 45 days agoMITView on GitHub

Expert Microsoft SQL Server assistance covering T-SQL, execution plans, index tuning, Always On availability groups, and DMV profiling. Use when developing enterprise .NET applications, administering SQL Server, or optimizing T-SQL.

The file

We split this skill into the rules it gives an agent

5 rules. About 0 are common, 1 is distinctive, and 4 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈1

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 4

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Very specific:

    Create index with search columns in key and projection columns in INCLUDE:

    line 84
  2. Distinctive:

    Use Parameterized Queries: Prevent parameter sniffing regressions and eliminate SQL injection vulnerabilities.

    line 102
  3. Very specific:

    Use Read-Intent Routing: Direct reporting queries to readable secondary replicas (ApplicationIntent=ReadOnly).

    line 104
  4. Very specific:

    Use NOLOCK indiscriminately: WITH (NOLOCK) causes dirty reads, phantom records, and duplicate row scans.

    line 108
  5. Very specific:

    Use generic VARCHAR(MAX) everywhere: Oversized LOB types bypass memory optimization and degrade performance.

    line 109

≈ “Common” is an estimate: at least 1 in 5 of the 350,495 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

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 Microsoft Ecosystems: The primary relational database for .NET, C#, Azure SQL, and Windows Server infrastructure.
  • Complex Financial & ERP Transaction Processing: Running mission-critical workloads with Advanced Data Security and Always On Availability Groups.
  • In-Memory OLTP & Temporal Tables: High-throughput memory-optimized tables and automated system-versioned temporal audit tracking.
  • Automated Performance Tuning: Utilizing Query Store to analyze execution plan regressions and force optimal query plans.

Quick Start

-- 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

System-Versioned Temporal Tables

Automatically captures complete historical audit trails for every row mutation:

CREATE TABLE dbo.Employee (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    Department NVARCHAR(50) NOT NULL,
    Salary DECIMAL(10,2) NOT NULL,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

-- Query row state as of specific point in time
SELECT * FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2026-01-15 12:00:00'
WHERE EmployeeID = 101;

Always On Availability Groups

Synchronous and asynchronous multi-database replication across high-availability failover nodes:

[ Primary Replica (Read/Write) ] ──Synchronous Commit──→ [ Secondary Replica (Readable) ]
                                          │
                                          ▼
                               [ Disaster Recovery Replica (Async) ]

Query Store & Plan Forcing

Captures query performance history, runtime statistics, and forces stable execution plans:

-- Enable Query Store
ALTER DATABASE EnterpriseDB SET QUERY_STORE = ON;

-- Force specific compiled execution plan to stop regression
EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 108;

Common Patterns

Index with Included Columns for Covering Queries

Problem: Wide indexes waste storage and index page space, while narrow indexes trigger expensive Key Lookups.

Solution: Create index with search columns in key and projection columns in INCLUDE:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Status
ON Sales.Orders (CustomerId, OrderStatus)
INCLUDE (OrderDate, TotalAmount);

-- Covering query executes purely from index pages without Key Lookup
SELECT OrderDate, TotalAmount
FROM Sales.Orders
WHERE CustomerId = 1205 AND OrderStatus = 'Shipped';

Best Practices

Do:

  • Enable Query Store on All Production Databases: Track execution plan regressions and runtime latency metrics automatically.
  • Use Parameterized Queries: Prevent parameter sniffing regressions and eliminate SQL injection vulnerabilities.
  • Index Foreign Keys: Manually index foreign key columns to prevent table locks during cascades and deletions.
  • Use Read-Intent Routing: Direct reporting queries to readable secondary replicas (ApplicationIntent=ReadOnly).

Don't:

  • Use NOLOCK indiscriminately: WITH (NOLOCK) causes dirty reads, phantom records, and duplicate row scans.
  • Use generic VARCHAR(MAX) everywhere: Oversized LOB types bypass memory optimization and degrade performance.
  • Perform row-by-row cursor processing: Replace procedural cursors with set-based SQL queries.

Troubleshooting

ErrorCauseSolution
Transaction (Process ID ...) was deadlocked on lock resourcesTwo transactions competing for locks in reverse sequence.Implement try-catch retry logic; ensure consistent object access order.
Could not allocate space for object in database ... tablespace fullDatafile or transaction log disk storage exhausted.Check autogrowth settings, backup transaction log (BACKUP LOG), or expand disk.
Implicit conversion causing index scanData type mismatch (e.g. VARCHAR comparing to NVARCHAR column).Align parameter types with column definitions in application queries.

References

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.