Bits of .NET

⚡ Indexes = Query Performance

Slow queries kill performance. Indexes speed up queries. Find data fast, optimize SELECTs.

📝 Index Types

# B-Tree Index (default)
CREATE INDEX idx_users_email ON users(email);

# Unique Index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);

# Composite Index (multiple columns)
CREATE INDEX idx_users_name_email ON users(name, email);

# Full-Text Index (text search)
CREATE FULLTEXT INDEX idx_articles_content ON articles(content);

# Bitmap Index (for low cardinality)
CREATE BITMAP INDEX idx_users_status ON users(status);

# Index on Expression
CREATE INDEX idx_users_upper_name ON users(UPPER(name));

# Clustered vs Non-Clustered
# Clustered: Table sorted by index (one per table)
# Non-Clustered: Separate structure (multiple per table)

🎯 Index Strategies

# When to Index
- WHERE clauses (frequent queries)
- JOIN columns
- ORDER BY columns
- GROUP BY columns

# Covering Indexes
CREATE INDEX idx_users_covering ON users(email)
INCLUDE (name, created_at);

# Filtered Index (partial)
CREATE INDEX idx_users_active ON users(email)
WHERE status = 'active';

# Index with ASC/DESC
CREATE INDEX idx_users_created ON users(created_at DESC);

# Analyze Query Plan
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

# Find Missing Indexes
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, mig.group_handle) + '_' + CONVERT(VARCHAR, digest.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + '] ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

# Index Best Practices
- Index columns used in WHERE and JOIN
- Avoid over-indexing (slows INSERT/UPDATE)
- Use composite indexes for multiple columns
- Monitor index usage
- Rebuild indexes periodically

💡 Index Tips

• Index columns used in WHERE and JOIN
• Avoid over-indexing (slows INSERT/UPDATE)
• Use composite indexes for multiple columns
• Monitor index usage
• Rebuild indexes periodically