Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use Indexes to Boost Query Performance

- 19.07.26 - ErcanOPAK

⚡ 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

“Indexes make queries fast. Find data instantly. Essential for database performance.”

— DBA

Related posts:

SQL: Use Window Functions for Running Totals and Ranking

SQL: Use Window Functions for Running Totals and Ranking

SQL COUNT(*) Isn’t Always Cheap

SQL COUNT(*) Isn’t Always Cheap

How to get the first and the last day of previous month in SQL Server

How to get the first and the last day of previous month in SQL Server

Views: 10

Post navigation

.NET Core: Build Background Tasks with Worker Services
C#: Use Object Initializers for Clean Object Creation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (661)
  • Add Constraint to SQL Table to ensure email contains @ (619)
  • Average of all values in a column that are not zero in SQL (577)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (553)
  • Find numbers with more than two decimal places in SQL (481)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store
  • WordPress: Fix a Navigation Block Losing Submenu Items After Sync
  • Visual Studio: Fix CodeLens Reference Counts Stuck at Stale Numbers
  • C#: Fix a UTF-8 String Literal Behaving Differently in a Switch
  • Photoshop: Fix a Liquify Mesh Snapping Back on a Smart Object
  • The AI Prompt That Turns a Crash Dump Into a Short Repro Script

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,068)
  • Get the User Name and Domain Name from an Email Address in SQL (990)
  • How to add default value for Entity Framework migrations for DateTime and Bool (986)
  • Get the First and Last Word from a String or Sentence in SQL (865)
  • How to select distinct rows in a datatable in C# (859)

Recent Posts

  • Network Speed Monitor Windows Application v2.5.1
  • Ağ Hızı İzleyici Windows Uygulaması v2.5.1
  • How to make theater mode the default for Youtube
  • The AI Prompt That Audits a Function for Thread-Safety Gaps
  • Ajax: Fix a Fetch Request Caching a Response Despite No-Store

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com