sqlserver-expert

CTEs (Common Table Expressions)

```sql

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

```sql

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

```sql

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;

```

Connection Pool

```typescript

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 {

if (!pool) {

pool = await sql.connect(config);

}

return pool;

}

```

Parameterized Queries

```typescript

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

`);

```

List Tables

```sql

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE

FROM INFORMATION_SCHEMA.TABLES

ORDER BY TABLE_SCHEMA, TABLE_NAME;

```

Table Structure

```sql

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

```sql

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

```sql

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;

```

Security

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

Performance

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