What is sqlserver-expert?

The SQL Server Expert skill is a comprehensive resource for Microsoft SQL Server development and administration. It assists users in crafting advanced T-SQL queries, optimizing database performance, and designing robust schemas. Beyond core database tasks, it provides specialized guidance for integrating SQL Server with Node.js applications using the mssql package, emphasizing security, scalability, and industry best practices.

When should I use sqlserver-expert?

sqlserver-expert is useful in the following scenarios: • Writing and debugging complex T-SQL logic, including Common Table Expressions (CTEs), Window Functions, and MERGE statements for sophisticated data processing. • Optimizing database performance by analyzing execution plans, implementing efficient indexing strategies, and refactoring non-SARGable queries. • Developing secure Node.js backend integrations with SQL Server, utilizing connection pooling and parameterized queries to prevent SQL injection. • Performing database administration and schema audits, such as inspecting table structures, managing foreign key relationships, and enforcing security through least-privilege access models. • Implementing data pagination and reporting solutions using advanced SQL techniques like OFFSET/FETCH and ROW_NUMBER().

name	sqlserver-expert
description	Expert in Microsoft SQL Server development and administration. Use when writing T-SQL queries, optimizing database performance, designing schemas, configuring SQL Server, or integrating SQL Server with Node.js using mssql package.

SQL Server Expert

You are a DBA and developer expert in Microsoft SQL Server.

T-SQL Advanced

CTEs (Common Table Expressions)

WITH RankedUsers AS (
  SELECT
    Id, Name, Email,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY HireDate) AS RowNum
  FROM Users
)
SELECT * FROM RankedUsers WHERE RowNum = 1;

Window Functions

SELECT
  OrderId,
  OrderDate,
  Amount,
  SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal,
  LAG(Amount) OVER (ORDER BY OrderDate) AS PreviousAmount,
  AVG(Amount) OVER (PARTITION BY CustomerId) AS CustomerAvg
FROM Orders;

MERGE Statement

MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.Id = source.Id
WHEN MATCHED THEN
  UPDATE SET target.Name = source.Name
WHEN NOT MATCHED THEN
  INSERT (Id, Name) VALUES (source.Id, source.Name)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

Node.js Integration (mssql)

Connection Pool

import sql from "mssql";

const config: sql.config = {
  user: process.env.SQL_USER,
  password: process.env.SQL_PASSWORD,
  server: process.env.SQL_SERVER || "localhost",
  database: process.env.SQL_DATABASE,
  options: {
    encrypt: true,
    trustServerCertificate: true,
    enableArithAbort: true,
  },
  pool: {
    min: 2,
    max: 10,
    idleTimeoutMillis: 30000,
  },
};

let pool: sql.ConnectionPool | null = null;

export async function getPool(): Promise<sql.ConnectionPool> {
  if (!pool) {
    pool = await sql.connect(config);
  }
  return pool;
}

Parameterized Queries

const pool = await getPool();
const request = pool.request();

request.input("userId", sql.Int, userId);
request.input("status", sql.VarChar(50), status);

const result = await request.query(`
  SELECT * FROM Users
  WHERE Id = @userId AND Status = @status
`);

Useful Queries

List Tables

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_SCHEMA, TABLE_NAME;

Table Structure

SELECT
  c.COLUMN_NAME,
  c.DATA_TYPE,
  c.CHARACTER_MAXIMUM_LENGTH,
  c.IS_NULLABLE,
  c.COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = @schema AND c.TABLE_NAME = @table
ORDER BY c.ORDINAL_POSITION;

Indexes

SELECT
  i.name AS IndexName,
  i.type_desc AS IndexType,
  i.is_unique,
  i.is_primary_key,
  STRING_AGG(c.name, ', ') AS Columns
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID(@tableName)
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key;

Foreign Keys

SELECT
  fk.name AS FK_Name,
  tp.name AS ParentTable,
  cp.name AS ParentColumn,
  tr.name AS ReferencedTable,
  cr.name AS ReferencedColumn
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE tp.name = @tableName;

Best Practices

Security

Never concatenate strings in queries - use parameters
Least privilege for application users
Use schemas to organize and control access

Performance

Avoid SELECT * - list columns explicitly
Use appropriate indexes for WHERE and JOIN
Avoid functions on columns in WHERE (not sargable)
Use SET NOCOUNT ON in stored procedures
Paginate with OFFSET/FETCH or ROW_NUMBER()

sqlserver-expert

When & Why to Use This Skill

Use Cases