**1. Stored Procedures vs Ad-Hoc Queries**: Procedures are compiled and cached, executing 3-5x faster. They reduce network traffic by 80% (send procedure name instead of full SQL). Use procedures for frequently executed logic.

**2. Avoid Cursors**: Cursors process rows one-by-one, 100-1000x slower than set-based operations. Example: Cursor to update 1M rows takes 10 minutes, set-based UPDATE takes 5 seconds. Use set-based SQL instead.

**3. Table Variables vs Temp Tables**: Table variables are faster for small datasets (< 1000 rows, no logging). Temp tables are better for large datasets (support indexes, statistics, parallel execution). Choose based on data size.

**4. Scalar Functions in WHERE**: Scalar functions prevent index usage. Example: WHERE dbo.GetYear(order_date) = 2024 does table scan. Use WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01' instead.

**5. SET NOCOUNT ON**: Reduces network traffic by suppressing row count messages. Add to all procedures and triggers. Improves performance by 5-10% in high-volume scenarios.

**6. Inline Table-Valued Functions**: Inline TVFs are expanded into query plan (like views), enabling index usage. Multi-statement TVFs are materialized (like temp tables), preventing optimization. Use inline TVFs when possible.

**7. MERGE Performance**: MERGE is convenient but can be slower than separate INSERT/UPDATE/DELETE for large datasets. Use MERGE for small to medium datasets (< 100K rows). For large datasets, use separate statements with proper indexing.

Stored procedures are compiled and cached - first execution compiles, subsequent executions reuse plan, 3-5x faster than ad-hoc queries

Avoid scalar functions in WHERE clauses - prevent index usage, cause table scans, use inline table-valued functions or direct expressions instead

Table variables vs temp tables: Table variables faster for < 1000 rows (no logging), temp tables faster for > 1000 rows (indexes, statistics, parallel execution)

SET NOCOUNT ON reduces network traffic - suppresses "X rows affected" messages, improves performance by 5-10% in high-volume scenarios

Use MERGE carefully - convenient for upserts but can be slower than separate INSERT/UPDATE/DELETE for large datasets (> 100K rows)

Inline table-valued functions enable optimization - expanded into query plan like views, multi-statement TVFs materialized like temp tables

Avoid WHILE loops for data processing - process rows one-by-one, 100-1000x slower than set-based operations, use UPDATE/INSERT/DELETE with WHERE instead

OUTPUT clause is faster than triggers for auditing - executes in same statement, no separate trigger overhead

Using cursors for data processing - 100-1000x slower than set-based operations, use UPDATE/DELETE with WHERE instead

Not using TRY/CATCH in transactions - errors leave transactions open, cause blocking, use TRY/CATCH with ROLLBACK in CATCH block

Scalar functions in WHERE clauses - prevent index usage, cause table scans, use inline table-valued functions or direct expressions

Not checking @@TRANCOUNT before ROLLBACK - causes "no transaction is active" error in nested procedures, always check IF @@TRANCOUNT > 0

Using table variables for large datasets - no statistics, poor query plans for > 1000 rows, use temp tables with indexes instead

Not using SET NOCOUNT ON - sends row count messages for every statement, increases network traffic, add to all procedures

MERGE without proper indexing - can be slower than separate statements, ensure proper indexes on join columns

Not handling errors in nested procedures - errors bubble up but transactions may be left open, use TRY/CATCH at each level