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

Category: SQL

SQL

SQL Server: Fix Deadlocks Between Two Queries That Update Tables in a Different Order

- 31.08.26 - ErcanOPAK comment on SQL Server: Fix Deadlocks Between Two Queries That Update Tables in a Different Order

🗄️ Two Correct Transactions, One Guaranteed Deadlock Neither query has a bug on its own — each one correctly updates two tables inside a transaction. The deadlock only appears when both run at the same time, because one updates Accounts then Orders, while the other updates Orders then Accounts. 🐞 The Problem — Transaction A […]

Read More
SQL

SQL Server: Fix a Query That Ignores Your Index Because of an Implicit Type Conversion

- 31.08.26 - ErcanOPAK comment on SQL Server: Fix a Query That Ignores Your Index Because of an Implicit Type Conversion

🗄️ The Index Exists. SQL Server Just Won’t Use It. You have a perfectly good index on an integer OrderId column, the query filters on that exact column, and it still runs a full table scan. The execution plan shows why: a hidden implicit conversion, inserted by SQL Server itself, that makes the index unusable […]

Read More
SQL

SQL Server: Recover a Dropped Table in Seconds Before the Space Gets Reused

- 31.08.26 | 31.08.26 - ErcanOPAK comment on SQL Server: Recover a Dropped Table in Seconds Before the Space Gets Reused

🗄️ Dropped a Table? The Clock Is Running Running DROP TABLE on the wrong table is instantly recoverable in the first moments after it happens — and increasingly unrecoverable as more writes happen and SQL Server reuses the freed disk space, so what you do in the next few minutes matters more than almost any […]

Read More
SQL

SQL Server: Why Implicit Conversion Between varchar and nvarchar Silently Kills Index Usage

- 31.08.26 | 31.08.26 - ErcanOPAK comment on SQL Server: Why Implicit Conversion Between varchar and nvarchar Silently Kills Index Usage

🗄️ One Missing ‘N’ Prefix Kills Your Index A query comparing an nvarchar column against a plain string literal looks completely normal — and quietly forces SQL Server to convert every single row before it can compare anything, turning a fast index seek into a full scan with no error to warn you. 🐞 The […]

Read More
SQL

SQL Server: Why a Non-SARGable WHERE Clause Silently Ignores Your Index

- 30.08.26 | 31.08.26 - ErcanOPAK comment on SQL Server: Why a Non-SARGable WHERE Clause Silently Ignores Your Index

🗄️ One Function Call = No Index Used A perfectly indexed column can still cause a full table scan if your WHERE clause wraps it in a function — SQL Server can’t use an index seek when it has to evaluate a function against every row first to know what to compare. 🐞 The Problem […]

Read More
SQL

SQL Server: Recover From a Runaway TempDB Filling the Entire Drive

- 30.08.26 | 31.08.26 - ErcanOPAK comment on SQL Server: Recover From a Runaway TempDB Filling the Entire Drive

🗄️ TempDB at 100% = Whole Server at Risk TempDB filling its drive doesn’t just fail the one query causing it — every other database on that SQL Server instance shares the same TempDB, so a single runaway sort, hash join, or long-open transaction can bring your entire server to a halt. 🔍 Find What’s […]

Read More
SQL

SQL Server: Why NOLOCK Silently Returns Duplicate or Missing Rows

- 30.08.26 | 31.08.26 - ErcanOPAK comment on SQL Server: Why NOLOCK Silently Returns Duplicate or Missing Rows

🗄️ NOLOCK = Maybe-Correct Results WITH (NOLOCK) is everywhere as a quick fix for blocking, but it doesn’t just risk reading uncommitted data — under real concurrent writes it can return the SAME row twice or skip a row entirely, with no error to tell you it happened. 🐞 The Problem SELECT OrderId, CustomerName, Total […]

Read More
SQL

SQL Server: Recover an Accidental UPDATE Without WHERE Using the Transaction Log

- 30.08.26 | 31.08.26 - ErcanOPAK comment on SQL Server: Recover an Accidental UPDATE Without WHERE Using the Transaction Log

🗄️ No WHERE Clause = Every Row Changed You ran an UPDATE meant for one row, forgot the WHERE clause, and every row in the table now has the same value. The transaction already committed. Here’s how to actually get the data back. 🚨 Do This in the First 60 Seconds Stop writing to the […]

Read More
SQL

SQL Server: Find the Missing Index Silently Making Your Query 100x Slower — In One Query

- 30.08.26 - ErcanOPAK comment on SQL Server: Find the Missing Index Silently Making Your Query 100x Slower — In One Query

🗄️ One Index = 100x Faster A query that took milliseconds last month now takes 8 seconds, and nothing in the code changed. The data just grew — and a missing index that didn’t matter at 1,000 rows is now doing a full table scan across 5 million. 🐞 The Problem SELECT * FROM Orders […]

Read More
SQL

SQL Server: Instantly Free a Table Locked by a Runaway Query — Without Restarting the Database

- 30.08.26 - ErcanOPAK comment on SQL Server: Instantly Free a Table Locked by a Runaway Query — Without Restarting the Database

🗄️ One KILL = Instant Relief A query is blocking every other request, your app is frozen, and restarting SQL Server isn’t an option in production. You don’t need to restart anything — you need to find the blocker and kill it, safely. 🐞 The Problem — Every query against the Orders table just hangs […]

Read More
SQL

SQL Transaction Isolation Levels: Master Data Consistency

- 29.08.26 | 29.08.26 - ErcanOPAK comment on SQL Transaction Isolation Levels: Master Data Consistency

🔒 Deep Dive: Transaction isolation levels control data visibility between transactions. Choose the right level for your use case. Concurrent transactions can conflict. Isolation levels prevent data anomalies. 🚀 Isolation Levels Explained — ✅ Read Uncommitted (Dirty Reads) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; BEGIN TRANSACTION; — ✅ Can read uncommitted data (dirty reads) — […]

Read More
SQL

Step-by-Step: SQL Stored Procedures for Maximum Performance

- 29.08.26 - ErcanOPAK comment on Step-by-Step: SQL Stored Procedures for Maximum Performance

⚡ Key Insights: Stored procedures offer better performance, security, and maintainability. Learn to write them like a pro. Write once, execute many times. Stored procedures are the ultimate performance tool. 🚀 Create Stored Procedure — ✅ Basic stored procedure CREATE PROCEDURE dbo.GetOrdersByCustomer @CustomerId INT, @StartDate DATETIME2 = NULL, @EndDate DATETIME2 = NULL AS BEGIN SET […]

Read More
SQL

SQL Server JSON Support: NoSQL Flexibility with SQL Power

- 29.08.26 - ErcanOPAK comment on SQL Server JSON Support: NoSQL Flexibility with SQL Power

📝 Key Insights: SQL Server supports JSON natively. Store, query, and transform JSON data like NoSQL, with full SQL power. Need both relational and document flexibility? SQL JSON support gives you both. 🚀 Store JSON Data — ✅ Table with JSON column CREATE TABLE Orders ( Id INT IDENTITY(1,1) PRIMARY KEY, CustomerId INT NOT NULL, […]

Read More
SQL

SQL Server Query Store: Proactive Performance Monitoring and Tuning

- 29.08.26 - ErcanOPAK comment on SQL Server Query Store: Proactive Performance Monitoring and Tuning

📊 Key Insights: Query Store captures query performance history, identifies regressions, and helps you optimize like a DBA pro. Query performance degrades over time. Query Store helps you detect and fix it. 🚀 Enable Query Store — ✅ Enable Query Store ALTER DATABASE MyDatabase SET QUERY_STORE = ON; — ✅ Configure Query Store ALTER DATABASE […]

Read More
SQL

SQL Columnstore Indexes: 100x Faster Analytics and Data Warehousing

- 29.08.26 - ErcanOPAK comment on SQL Columnstore Indexes: 100x Faster Analytics and Data Warehousing

📊 Deep Dive: Columnstore indexes are designed for analytical queries. They dramatically improve performance for large data warehouses. Rowstore indexes are slow for analytics. Columnstore indexes are designed for it. 🚀 Create Columnstore Index — ✅ Create clustered columnstore index (for data warehouse) CREATE CLUSTERED COLUMNSTORE INDEX CCI_Orders ON dbo.Orders; — ✅ Create nonclustered columnstore […]

Read More
SQL

SQL In-Memory OLTP: 100x Faster Transactions with Memory-Optimized Tables

- 29.08.26 - ErcanOPAK comment on SQL In-Memory OLTP: 100x Faster Transactions with Memory-Optimized Tables

⚡ Quick Win: Memory-optimized tables eliminate disk I/O and locking. Perfect for high-throughput OLTP workloads. Disk I/O is the bottleneck. In-Memory OLTP eliminates it. 🚀 Enable In-Memory OLTP — ✅ Enable In-Memory OLTP ALTER DATABASE MyDatabase SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON; — ✅ Create memory-optimized filegroup ALTER DATABASE MyDatabase ADD FILEGROUP InMemoryFileGroup CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE […]

Read More
SQL

SQL Temporal Tables: Track Data Changes Over Time

- 29.08.26 - ErcanOPAK comment on SQL Temporal Tables: Track Data Changes Over Time

📅 Key Insights: Temporal tables automatically track history of data changes. Perfect for auditing, compliance, and time-travel queries. Need to know what data looked like last month? Temporal tables make it easy. 🚀 Create Temporal Table — ✅ Create table with temporal support CREATE TABLE Orders ( Id INT IDENTITY(1,1) PRIMARY KEY, CustomerId INT NOT […]

Read More
SQL

SQL Full-Text Search: Build Google-Like Search in Your Database

- 29.08.26 - ErcanOPAK comment on SQL Full-Text Search: Build Google-Like Search in Your Database

🔍 Key Insights: Full-Text Search provides powerful, flexible search capabilities. Faster than LIKE and supports linguistic searches. LIKE queries are slow and limited. Full-Text Search is the professional solution. 🚀 Enable Full-Text Search — ✅ Enable Full-Text Search CREATE FULLTEXT CATALOG MyCatalog AS DEFAULT; — ✅ Create Full-Text Index CREATE FULLTEXT INDEX ON Products ( […]

Read More
SQL

SQL Index Fragmentation: Find, Fix, and Prevent Performance Loss

- 23.08.26 - ErcanOPAK comment on SQL Index Fragmentation: Find, Fix, and Prevent Performance Loss

📉 Key Takeaways: Index fragmentation slows queries. Learn to detect, measure, and fix fragmentation for peak performance. Indexes get fragmented over time. Rebuild or reorganize them for performance. 🔍 Check Index Fragmentation — ✅ Check fragmentation for all indexes SELECT OBJECT_NAME(ips.object_id) AS TableName, i.name AS IndexName, ips.index_type_desc, ips.avg_fragmentation_in_percent, ips.page_count, ips.avg_page_space_used_in_percent, CASE WHEN ips.avg_fragmentation_in_percent > 30 […]

Read More
SQL

SQL Recursive CTE: Query Hierarchical Data Like a Pro

- 23.08.26 - ErcanOPAK comment on SQL Recursive CTE: Query Hierarchical Data Like a Pro

🌳 Key Takeaways: Recursive CTEs query tree structures, org charts, and hierarchical data without complex code. Querying hierarchical data is hard. Recursive CTEs make it easy. 📁 Sample Data: Employee Hierarchy CREATE TABLE Employees ( Id INT PRIMARY KEY, Name NVARCHAR(100), ManagerId INT NULL, Department NVARCHAR(50) ); INSERT INTO Employees VALUES (1, ‘CEO’, NULL, ‘Executive’), […]

Read More
SQL

SQL Execution Plan Reuse: Boost Query Performance with Plan Caching

- 23.08.26 - ErcanOPAK comment on SQL Execution Plan Reuse: Boost Query Performance with Plan Caching

📊 Key Takeaways: SQL Server caches execution plans. Learn how to maximize plan reuse and avoid compilation overhead. Query compilation is expensive. Plan reuse saves that cost. 🚀 Check Plan Reuse — ✅ Check plan reuse statistics SELECT cacheobjtype, objtype, usecounts, size_in_bytes, text FROM sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) WHERE cacheobjtype = ‘Compiled Plan’ ORDER […]

Read More
SQL

SQL Index Hints: Force SQL Server to Use the Right Index

- 23.08.26 - ErcanOPAK comment on SQL Index Hints: Force SQL Server to Use the Right Index

🔍 Key Takeaways: Sometimes SQL Server chooses the wrong index. Index hints force the query optimizer to use a specific index. SQL Server usually makes good choices. When it doesn’t, index hints save the day. ❌ When SQL Server Makes Bad Choices — ❌ SQL Server uses wrong index (table scan) SELECT * FROM Orders […]

Read More
SQL

SQL Server Query Store: Proactive Performance Monitoring

- 23.08.26 - ErcanOPAK comment on SQL Server Query Store: Proactive Performance Monitoring

📊 Key Takeaways: Query Store captures query performance history, helps identify regressions, and provides insights for optimization. Query performance degrades over time. Query Store helps you detect and fix it. 🚀 Enable Query Store — ✅ Enable Query Store ALTER DATABASE MyDatabase SET QUERY_STORE = ON; — ✅ Configure Query Store ALTER DATABASE MyDatabase SET […]

Read More
SQL

SQL Window Functions: Revolutionizing Reporting Without Subqueries

- 22.08.26 - ErcanOPAK comment on SQL Window Functions: Revolutionizing Reporting Without Subqueries

📊 Key Takeaways: Master ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and running totals for complex analytics without self-joins or subqueries. Complex reports used to require messy self-joins and subqueries. Window functions make them elegant and fast. ✅ ROW_NUMBER – Ranking Without Gaps — Find top 3 products per category SELECT Category, ProductName, Price, ROW_NUMBER() OVER ( […]

Read More
SQL

SQL Server Execution Plan Analysis: Find and Fix Slow Queries Like a Pro

- 19.08.26 | 19.08.26 - ErcanOPAK comment on SQL Server Execution Plan Analysis: Find and Fix Slow Queries Like a Pro

Your application is slow, and you suspect the database. Execution plans reveal exactly what SQL Server is doing and where the bottlenecks are. 1. Capture Actual Execution Plan: — ✅ Capture actual execution plan SET STATISTICS IO ON; SET STATISTICS TIME ON; — Run your query SELECT o.OrderID, o.OrderDate, c.CustomerName, p.ProductName, od.Quantity, od.UnitPrice FROM Orders […]

Read More
SQL

SQL: Query Store ile Performance Monitoring

- 18.08.26 - ErcanOPAK comment on SQL: Query Store ile Performance Monitoring

📊 Query Store = Performance Monitoring Query performance changes over time. Query Store tracks query execution history for performance analysis. 📝 Query Store Implementation # 1. Enable Query Store ALTER DATABASE MyDatabase SET QUERY_STORE = ON; ALTER DATABASE MyDatabase SET QUERY_STORE ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = […]

Read More
SQL

SQL: Index Types ve Optimization Teknikleri

- 18.08.26 - ErcanOPAK comment on SQL: Index Types ve Optimization Teknikleri

📊 Index Types = Query Speed Doğru index hayat kurtarır. Index types ile sorgu performansını optimize edin. 🎯 Index Türleri # 1. B-Tree Index (Default) CREATE INDEX idx_users_email ON users(email); # 2. Unique Index CREATE UNIQUE INDEX idx_users_username ON users(username); # 3. Composite Index CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date); # 4. Partial Index CREATE […]

Read More
SQL

SQL: PostgreSQL Query Plan Analysis ile Performance Tuning

- 18.08.26 - ErcanOPAK comment on SQL: PostgreSQL Query Plan Analysis ile Performance Tuning

🔍 Query Plan = Performance Tuning Yavaş sorguları hızlandırın. PostgreSQL Query Plan ile sorgu performansını analiz edin. 🎯 Query Plan Analizi # 1. EXPLAIN ANALYZE EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT * FROM orders WHERE customer_id = 12345; # 2. Plan Operations – Seq Scan: Full table scan (expensive) – Index Scan: Using index (fast) – […]

Read More
SQL

SQL: PostgreSQL Window Functions ile Karmaşık Veri Analizi

- 18.08.26 - ErcanOPAK comment on SQL: PostgreSQL Window Functions ile Karmaşık Veri Analizi

📊 Window Functions = Advanced Analytics Basit GROUP BY yeterli değil. PostgreSQL Window Functions ile moving averages, rankings ve running totals hesaplayın. 🎯 Window Fonksiyonları — 1. Ranking SELECT product_name, price, RANK() OVER (ORDER BY price DESC) as rank, DENSE_RANK() OVER (ORDER BY price DESC) as dense_rank, ROW_NUMBER() OVER (ORDER BY price DESC) as row_num […]

Read More
SQL

SQL: Common Table Expressions (CTEs) for Complex Queries

- 17.08.26 - ErcanOPAK comment on SQL: Common Table Expressions (CTEs) for Complex Queries

📝 CTEs = Complex Queries Complex queries are hard to read. Common Table Expressions (CTEs) break down complex queries into readable parts. 📝 CTE Examples — Basic CTE WITH active_users AS ( SELECT * FROM users WHERE active = 1 ) SELECT * FROM active_users; — Multiple CTEs WITH recent_orders AS ( SELECT * FROM […]

Read More
Page 6 of 17
« Previous 1 2 3 4 5 6 7 8 9 10 11 … 17 Next »

Posts pagination

« Previous 1 … 4 5 6 7 8 … 17 Next »
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