copilot-instructions database.instructions.md

Schema design

• Tables/columns/indexes/constraints in EN
• PascalCase for tables
• camelCase for columns
• pk_ for primary keys
• fk_ for foreign keys
• ix_ for indexes
• ck_ for checks
• uq_ for unique
• Triggers with tr_ prefix

CREATE TABLE Orders (
    orderId BIGINT PRIMARY KEY,
    createdAt DATETIME
);
ALTER TABLE Orders ADD CONSTRAINT pk_Orders PRIMARY KEY (orderId);
CREATE INDEX ix_Orders_CreatedAt ON Orders(createdAt);

Normalization

• 1NF atomic columns
• 2NF full functional dependency
• 3NF no transitive dependency
• BCNF when applicable
• Controlled denormalization only for critical performance with justification

-- 3NF - Separate Addresses from Users to avoid transitive dependency

Cartesian explosion

• Avoid JOINs without proper predicates
• Prefer EXISTS over IN with subqueries
• Filter before JOIN using CTEs/subqueries
• Verify cardinality estimates
• Test with large datasets
• Inspect execution plans

WITH Filtered AS (SELECT * FROM Orders WHERE status = 'Active')
SELECT * FROM Filtered JOIN Customers ON Filtered.customerId = Customers.id;

Parameter sniffing

• OPTION(OPTIMIZE FOR UNKNOWN) when applicable
• Properly typed parameters
• Avoid dynamic SQL when possible
• Stored procedures with RECOMPILE when needed
• Plan guides for specific cases
• Forced parameterization in controlled scenarios

SELECT * FROM Orders WHERE status = @status OPTION (OPTIMIZE FOR UNKNOWN);

Query performance

• Avoid SELECT *
• Appropriate indexes
• SARGABLE predicates (avoid functions in WHERE)
• LIMIT/TOP for large datasets
• Up-to-date statistics
• Plan analysis
• Avoid N+1
• Keyset (seek) pagination for large lists

SELECT TOP (@size) * FROM Orders
WHERE (createdAt, id) > (@lastCreatedAt, @lastId)
ORDER BY createdAt, id; -- Keyset pagination

Indexes

• Clustered on most queried keys
• Non-clustered for FKs and filter columns
• Composite with most selective first
• Include columns for covering indexes
• Proper fill factor
• Maintenance plans
• Avoid over-indexing
• Drop unused

CREATE INDEX ix_Orders_CustomerId_CreatedAt ON Orders(CustomerId, CreatedAt) INCLUDE (Status, TotalAmount);
-- Drop ix_Orders_Temp if unused per stats