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 Identical Queries Returning Different Row Counts From Two Replicas Because One Secondary Is Running Behind Under an Always On Availability Group

- 29.10.26 - ErcanOPAK comment on SQL Server: Fix Identical Queries Returning Different Row Counts From Two Replicas Because One Secondary Is Running Behind Under an Always On Availability Group

🗄️ Fix Identical Queries Returning Different Row Counts From Two Replicas Because One Secondary Is Running Behind Under an Always On Availability Group A readable secondary replica in an Always On Availability Group applies transaction log changes with some inherent lag, even under synchronous-commit mode during periods of heavy load. Running the identical query against […]

Read More
SQL

SQL Server: Fix a Scalar UDF Making a Simple Query 50 Times Slower Because It Blocks Batch Mode Processing on the Whole Plan

- 29.10.26 - ErcanOPAK comment on SQL Server: Fix a Scalar UDF Making a Simple Query 50 Times Slower Because It Blocks Batch Mode Processing on the Whole Plan

🗄️ Fix a Scalar UDF Making a Simple Query 50 Times Slower Because It Blocks Batch Mode Processing on the Whole Plan Referencing even a trivial scalar user-defined function anywhere in a query’s SELECT list or WHERE clause forces the entire execution plan – including unrelated columnstore-index scans that would otherwise run in fast batch […]

Read More
SQL

SQL Server: Fix a Columnstore Index Query Getting Slower Over Time Because Delta Store Rowgroups Were Never Compressed

- 29.10.26 - ErcanOPAK comment on SQL Server: Fix a Columnstore Index Query Getting Slower Over Time Because Delta Store Rowgroups Were Never Compressed

🗄️ Fix a Columnstore Index Query Getting Slower Over Time Because Delta Store Rowgroups Were Never Compressed Rows inserted into a table with a clustered columnstore index land first in an uncompressed, row-oriented delta store rowgroup rather than directly into compressed columnar storage, and SQL Server only closes and compresses a delta rowgroup once it […]

Read More
SQL

SQL Server: Fix a Natively Compiled Stored Procedure Returning Stale Results Because In-Memory OLTP Tables Only Support Snapshot Isolation

- 29.10.26 - ErcanOPAK comment on SQL Server: Fix a Natively Compiled Stored Procedure Returning Stale Results Because In-Memory OLTP Tables Only Support Snapshot Isolation

🗄️ Fix a Natively Compiled Stored Procedure Returning Stale Results Because In-Memory OLTP Tables Only Support Snapshot Isolation Every natively compiled stored procedure accessing a memory-optimized table is hard-coded to run under SNAPSHOT isolation – the only isolation level In-Memory OLTP tables support at all, and it can’t be overridden per call the way an […]

Read More
SQL

SQL Server: Fix New Deadlocks Appearing Only After Raising the Isolation Level Because Range Locks Now Cover Rows That Didn’t Exist Yet

- 28.10.26 - ErcanOPAK comment on SQL Server: Fix New Deadlocks Appearing Only After Raising the Isolation Level Because Range Locks Now Cover Rows That Didn’t Exist Yet

🗄️ Fix New Deadlocks Appearing Only After Raising the Isolation Level Because Range Locks Now Cover Rows That Didn’t Exist Yet Moving a transaction from the default READ COMMITTED isolation level up to SERIALIZABLE for the sake of consistency buys exactly what it promises – no phantom rows, no non-repeatable reads – at the cost […]

Read More
SQL

SQL Server: Fix an Upsert Built From Two Separate Statements That Creates Duplicate Rows Under Load Because of the Gap Between the Existence Check and the Insert

- 28.10.26 - ErcanOPAK comment on SQL Server: Fix an Upsert Built From Two Separate Statements That Creates Duplicate Rows Under Load Because of the Gap Between the Existence Check and the Insert

🗄️ Fix an Upsert Built From Two Separate Statements That Creates Duplicate Rows Under Load Because of the Gap Between the Existence Check and the Insert An upsert written as a plain existence check followed by a separate insert reads perfectly reasonably and passes every test run one request at a time. The problem only […]

Read More
SQL

SQL Server: Fix a Scalar User-Defined Function That Silently Forces a Row-by-Row Scan Across the Entire Query Plan

- 28.10.26 - ErcanOPAK comment on SQL Server: Fix a Scalar User-Defined Function That Silently Forces a Row-by-Row Scan Across the Entire Query Plan

🗄️ Fix a Scalar User-Defined Function That Silently Forces a Row-by-Row Scan Across the Entire Query Plan A scalar user-defined function called in a SELECT list or WHERE clause looks like any other expression in the query text, but unless it’s specifically defined as inline (and even then, only under certain conditions SQL Server can […]

Read More
SQL

SQL Server: Fix an ORDER BY Inside a View or Subquery Being Silently Discarded Because It’s Only Honored on the Outermost Result Set

- 28.10.26 - ErcanOPAK comment on SQL Server: Fix an ORDER BY Inside a View or Subquery Being Silently Discarded Because It’s Only Honored on the Outermost Result Set

🗄️ Fix an ORDER BY Inside a View or Subquery Being Silently Discarded Because It’s Only Honored on the Outermost Result Set SQL Server treats a result set as fundamentally unordered unless an ORDER BY is applied to the final, outermost query that actually returns results to the caller – an ORDER BY written inside […]

Read More
SQL

SQL Server: Fix a Parameterized Query That Runs Fast Once and Slow Forever After Because of Parameter Sniffing on a Skewed Column

- 28.10.26 - ErcanOPAK comment on SQL Server: Fix a Parameterized Query That Runs Fast Once and Slow Forever After Because of Parameter Sniffing on a Skewed Column

🗄️ Fix a Parameterized Query That Runs Fast Once and Slow Forever After Because of Parameter Sniffing on a Skewed Column SQL Server compiles and caches an execution plan for a parameterized query the first time it runs, choosing that plan based on the specific value passed on that very first call – a reasonable […]

Read More
SQL

SQL Server: Fix a Deadlock That Only Happens Under Load Because Two Stored Procedures Touch the Same Tables in Opposite Order

- 28.10.26 - ErcanOPAK comment on SQL Server: Fix a Deadlock That Only Happens Under Load Because Two Stored Procedures Touch the Same Tables in Opposite Order

🗄️ Fix a Deadlock That Only Happens Under Load Because Two Stored Procedures Touch the Same Tables in Opposite Order A deadlock forms when one procedure locks a table and then needs a lock on a second table, while at the same moment a different procedure running concurrently has already locked that second table and […]

Read More
SQL

SQL Server: Fix Row-Level Security Silently Hiding Rows From a Report Because Its Predicate Function Runs as the Caller, Not the Service Account

- 23.10.26 - ErcanOPAK comment on SQL Server: Fix Row-Level Security Silently Hiding Rows From a Report Because Its Predicate Function Runs as the Caller, Not the Service Account

🗄️ Fix Row-Level Security Silently Hiding Rows From a Report Because Its Predicate Function Runs as the Caller, Not the Service Account Row-Level Security’s filter predicate function evaluates under the security context of whoever is actually running the query against the table – not the identity a reporting tool’s connection string logs in as on […]

Read More
SQL

SQL Server: Fix an UPDATE … FROM Statement That Silently Updates From a Different Join Row Than Expected Because the Join Isn’t Unique

- 23.10.26 - ErcanOPAK comment on SQL Server: Fix an UPDATE … FROM Statement That Silently Updates From a Different Join Row Than Expected Because the Join Isn’t Unique

🗄️ Fix an UPDATE … FROM Statement That Silently Updates From a Different Join Row Than Expected Because the Join Isn’t Unique An UPDATE statement joined to another table to pull in a value – a current price, a latest status – assumes there’s exactly one matching row on the other side of the join […]

Read More
SQL

SQL Server: Fix a Stored Procedure That Performs Great for Most Callers but Terribly for One Because of Parameter Sniffing on the First Call’s Value

- 23.10.26 - ErcanOPAK comment on SQL Server: Fix a Stored Procedure That Performs Great for Most Callers but Terribly for One Because of Parameter Sniffing on the First Call’s Value

🗄️ Fix a Stored Procedure That Performs Great for Most Callers but Terribly for One Because of Parameter Sniffing on the First Call’s Value SQL Server compiles and caches exactly one execution plan the first time a stored procedure is called with a given set of parameter values, optimized specifically for those values’ selectivity – […]

Read More
SQL

SQL Server: Fix an Indexed View That Silently Stops Being Used by the Optimizer Because One of Its Required SET Options Was Never Enabled on the Session That Created It

- 23.10.26 - ErcanOPAK comment on SQL Server: Fix an Indexed View That Silently Stops Being Used by the Optimizer Because One of Its Required SET Options Was Never Enabled on the Session That Created It

🗄️ Fix an Indexed View That Silently Stops Being Used by the Optimizer Because One of Its Required SET Options Was Never Enabled on the Session That Created It An indexed view needs a specific set of SET options enabled at creation time – ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and NUMERIC_ROUNDABORT all have to […]

Read More
SQL

SQL Server: Fix a Table Variable’s Missing Statistics Making the Optimizer Assume Only One Row Every Time

- 23.10.26 - ErcanOPAK comment on SQL Server: Fix a Table Variable’s Missing Statistics Making the Optimizer Assume Only One Row Every Time

🗄️ A Table Variable Looks Like a Table – the Optimizer Treats It Like a Guess Switching from a temp table to a table variable for a batch of intermediate results feels like a safe, purely syntactic swap, but the query optimizer has no statistics on a table variable’s contents at all – it doesn’t […]

Read More
SQL

SQL Server: Fix a Clustered Index’s Fill Factor Causing Constant Page Splits on an Ever-Increasing Key

- 23.10.26 - ErcanOPAK comment on SQL Server: Fix a Clustered Index’s Fill Factor Causing Constant Page Splits on an Ever-Increasing Key

🗄️ A Low Fill Factor Helps Random Inserts – and Actively Hurts Sequential Ones Fill factor controls how much free space SQL Server leaves on each index page when it’s built or rebuilt, and a lower value (commonly recommended at 70-80% for tables with scattered, random-key inserts) reserves room for new rows to be inserted […]

Read More
SQL

SQL Server: Fix DATEDIFF Silently Returning the Wrong Result Because It Counts Boundary Crossings, Not Elapsed Time

- 22.10.26 - ErcanOPAK comment on SQL Server: Fix DATEDIFF Silently Returning the Wrong Result Because It Counts Boundary Crossings, Not Elapsed Time

🗄️ DATEDIFF Counts Calendar Lines Crossed, Not Time That Passed DATEDIFF reads like it should tell you how much time elapsed between two dates in whatever unit you ask for, and for most everyday ranges it happens to give an answer that matches that intuition closely enough that nobody questions it – until a pair […]

Read More
SQL

SQL Server: Fix ROW_NUMBER() Returning a Different Order Every Run Because the OVER Clause Has No Real Tiebreaker

- 22.10.26 - ErcanOPAK comment on SQL Server: Fix ROW_NUMBER() Returning a Different Order Every Run Because the OVER Clause Has No Real Tiebreaker

🗄️ ORDER BY With Ties Means the Optimizer Gets to Decide, Not You ROW_NUMBER() OVER (ORDER BY …) feels deterministic because it always produces a clean, gapless sequence of integers – but that guarantee is only about the numbers themselves, not about which specific row gets which number when the ORDER BY column has duplicate […]

Read More
SQL

SQL Server: Fix String Data Getting Silently Truncated Instead of Erroring Because SET ANSI_WARNINGS Was OFF for That Session

- 22.10.26 - ErcanOPAK comment on SQL Server: Fix String Data Getting Silently Truncated Instead of Erroring Because SET ANSI_WARNINGS Was OFF for That Session

🗄️ The INSERT That Should Have Failed but Didn’t With ANSI_WARNINGS ON (the default for most client connections, including SSMS), inserting or updating a string value that’s too long for its target column’s declared length raises a hard error – ‘String or binary data would be truncated’ – and rejects the statement outright, but with […]

Read More
SQL

SQL Server: Fix a Query That Falls Back to an Expensive Key Lookup Because One Extra Column in the SELECT List Wasn’t Part of the Covering Index

- 22.10.26 - ErcanOPAK comment on SQL Server: Fix a Query That Falls Back to an Expensive Key Lookup Because One Extra Column in the SELECT List Wasn’t Part of the Covering Index

🗄️ The Index Seek That’s Secretly Doing a Second Trip for Every Row A nonclustered index only fully covers a query when every column the query needs – in the WHERE clause, the JOIN, and the SELECT list – is present in the index’s key or INCLUDE columns, and the moment just one additional column […]

Read More
SQL

SQL Server: Fix a Partitioned Table That Still Scans Every Partition Because the Optimizer Can’t Eliminate Any of Them Without a Filter on the Partitioning Key

- 22.10.26 - ErcanOPAK comment on SQL Server: Fix a Partitioned Table That Still Scans Every Partition Because the Optimizer Can’t Eliminate Any of Them Without a Filter on the Partitioning Key

🗄️ The Partitioning Scheme That Never Actually Got to Skip Anything Table partitioning’s entire performance benefit for a query comes from partition elimination – the optimizer recognizing, from the WHERE clause, that only a handful of partitions could possibly contain matching rows and skipping every other partition’s data entirely – but that elimination only happens […]

Read More
SQL

SQL Server: Fix Always Encrypted Columns That Silently Break Every Parameterized Query Because the Client Driver Never Enabled Column Encryption Setting

- 22.10.26 - ErcanOPAK comment on SQL Server: Fix Always Encrypted Columns That Silently Break Every Parameterized Query Because the Client Driver Never Enabled Column Encryption Setting

🗄️ The Encrypted Column That Looked Like It Just Stopped Matching Anything Always Encrypted protects sensitive column data by encrypting it client-side before it ever reaches SQL Server, which means the database engine itself never sees plaintext values and can’t decrypt them for comparison either – the entire feature depends on the client driver transparently […]

Read More
SQL

SQL Server: Fix a Columnstore Index Silently Falling Back to a Full Scan Because a Predicate Wraps the Column in a Function

- 21.10.26 - ErcanOPAK comment on SQL Server: Fix a Columnstore Index Silently Falling Back to a Full Scan Because a Predicate Wraps the Column in a Function

🗄️ The Columnstore Index That Never Gets to Skip Anything A nonclustered columnstore index’s biggest performance win comes from segment elimination – skipping entire row groups whose min/max metadata proves they can’t contain any matching rows for a given predicate – but that elimination only works against predicates compared directly to the raw column value. […]

Read More
SQL

SQL Server: Fix an Online Index Rebuild Silently Blocking Writes Anyway Because an Old Transaction Is Still Open on the Table

- 21.10.26 - ErcanOPAK comment on SQL Server: Fix an Online Index Rebuild Silently Blocking Writes Anyway Because an Old Transaction Is Still Open on the Table

🗄️ The Online Rebuild That Wasn’t Actually Online ALTER INDEX … REBUILD WITH (ONLINE = ON) is specifically designed to let reads and writes continue against a table while its index rebuilds in the background, briefly taking only a short schema modification lock at the very start and end of the operation – but that […]

Read More
SQL

SQL Server: Fix an XML Index Query That Falls Back to a Full Table Scan Because the XQuery Expression Doesn’t Match a PATH Index

- 21.10.26 - ErcanOPAK comment on SQL Server: Fix an XML Index Query That Falls Back to a Full Table Scan Because the XQuery Expression Doesn’t Match a PATH Index

🗄️ The XML Index That’s Sitting There Unused SQL Server’s secondary XML indexes only get used by the optimizer when a query’s XQuery expression matches the specific pattern the index was built for. A PATH secondary index is optimized for queries that filter on an exact, fully-specified element path, but if the actual predicate uses […]

Read More
SQL

SQL Server: Fix a Point-in-Time Restore That Silently Fails Because an Earlier Switch to the Simple Recovery Model Broke the Transaction Log Chain

- 21.10.26 - ErcanOPAK comment on SQL Server: Fix a Point-in-Time Restore That Silently Fails Because an Earlier Switch to the Simple Recovery Model Broke the Transaction Log Chain

🗄️ The Restore That Needed a Log Chain Nobody Kept Intact Point-in-time restore depends on an unbroken chain of transaction log backups going back to the last full backup. Switching a database from full to simple recovery model, even briefly for what seemed like a harmless maintenance operation, truncates the transaction log and permanently breaks […]

Read More
SQL

SQL Server: Fix a Query Store That Silently Stops Capturing New Query Plans Because It Hit Its Max Size and Switched to Read-Only Mode

- 21.10.26 - ErcanOPAK comment on SQL Server: Fix a Query Store That Silently Stops Capturing New Query Plans Because It Hit Its Max Size and Switched to Read-Only Mode

🗄️ The Query Store That Stopped Watching Without Telling Anyone Query Store is meant to keep capturing query plans and runtime statistics continuously, which is exactly what makes it useful for diagnosing a regression after the fact. But Query Store has a configured maximum size, and when it hits that limit with its cleanup policy […]

Read More
SQL

SQL Server: Fix a Temporal Table’s History That Silently Stops Recording Changes Because SYSTEM_VERSIONING Was Turned Off During a Schema Change

- 21.10.26 - ErcanOPAK comment on SQL Server: Fix a Temporal Table’s History That Silently Stops Recording Changes Because SYSTEM_VERSIONING Was Turned Off During a Schema Change

🗄️ The History Table That Quietly Stopped Growing System-versioned temporal tables automatically keep a full history of every row change, which makes them a popular choice for audit trails. But altering a temporal table’s schema – adding a column, changing a data type, anything the engine won’t let you do while versioning is active – […]

Read More
SQL

SQL Server: Fix a Table-Valued Parameter That Silently Passes Zero Rows Because the Same DataTable Instance Was Reused Across Calls Without Being Cleared

- 16.10.26 - ErcanOPAK comment on SQL Server: Fix a Table-Valued Parameter That Silently Passes Zero Rows Because the Same DataTable Instance Was Reused Across Calls Without Being Cleared

🗄️ The Table-Valued Parameter That Quietly Sends Nothing Table-valued parameters let a C# or .NET caller hand a whole set of rows to a stored procedure in one call, built from an ordinary DataTable passed through a SqlParameter. The gotcha shows up when that DataTable is cached or reused across multiple calls as a small […]

Read More
SQL

SQL Server: Fix a Database Snapshot That Silently Fills the Data Drive Because It Keeps a Copy-on-Write Diff of Every Page Changed Since It Was Created

- 16.10.26 - ErcanOPAK comment on SQL Server: Fix a Database Snapshot That Silently Fills the Data Drive Because It Keeps a Copy-on-Write Diff of Every Page Changed Since It Was Created

🗄️ The Snapshot That Quietly Grows Until the Drive Is Full A SQL Server database snapshot looks lightweight when it’s created – its sparse file starts at almost nothing – but it works by copying the original, unmodified version of every data page into that sparse file the first time the live database changes that […]

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

Posts pagination

« Previous 1 2 3 4 5 … 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