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 Optimistic Concurrency Checks That Silently Always Succeed Because a rowversion Column Was Never Compared in the WHERE Clause

- 16.10.26 - ErcanOPAK comment on SQL Server: Fix Optimistic Concurrency Checks That Silently Always Succeed Because a rowversion Column Was Never Compared in the WHERE Clause

🗄️ The Optimistic Lock That Never Actually Checked Anything A rowversion (timestamp) column is the standard way SQL Server supports optimistic concurrency: you read a row along with its rowversion, and when you save, your UPDATE’s WHERE clause includes that original rowversion value – if someone else changed the row in between, the WHERE clause […]

Read More
SQL

SQL Server: Fix a Correlated Subquery in the SELECT List That Silently Runs Once Per Row Instead of a Set-Based CROSS APPLY

- 16.10.26 - ErcanOPAK comment on SQL Server: Fix a Correlated Subquery in the SELECT List That Silently Runs Once Per Row Instead of a Set-Based CROSS APPLY

🗄️ The SELECT List That Secretly Runs a Query Per Row A scalar correlated subquery dropped directly into a SELECT list – fetching each customer’s most recent order date, say – reads like a single query and returns perfectly correct results, which is exactly why nobody questions it. What the syntax hides is that the […]

Read More
SQL

SQL Server: Fix a Foreign Key With No Supporting Index That Silently Turns Every Delete Into a Full Scan of the Child Table

- 16.10.26 - ErcanOPAK comment on SQL Server: Fix a Foreign Key With No Supporting Index That Silently Turns Every Delete Into a Full Scan of the Child Table

🗄️ The Delete That Turned Into a Full Scan Three Tables Deep SQL Server never automatically creates an index on a foreign key column in the child table – only the primary key side of the relationship gets one for free. When you delete a row from the parent, the engine still has to find […]

Read More
SQL

SQL Server: Fix a Computed Column Index That Silently Stops Being Used After a Session Changes ANSI Settings

- 16.10.26 - ErcanOPAK comment on SQL Server: Fix a Computed Column Index That Silently Stops Being Used After a Session Changes ANSI Settings

🗄️ The Computed Column Index That Stopped Being Used Overnight An index on a computed column only gets considered by the optimizer when the session running the query has a specific set of options all set correctly: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, and QUOTED_IDENTIFIER ON, CONCAT_NULL_YIELDS_NULL ON, and NUMERIC_ROUNDABORT OFF. Most modern drivers set these correctly […]

Read More
SQL

SQL Server: Fix a CHECK Constraint That Silently Lets NULL Bypass the Rule It Was Meant to Enforce

- 15.10.26 - ErcanOPAK comment on SQL Server: Fix a CHECK Constraint That Silently Lets NULL Bypass the Rule It Was Meant to Enforce

🗄️ The Constraint That Let Nothing Through A CHECK constraint only rejects rows where its expression evaluates to definitely FALSE – and SQL’s three-valued logic treats any comparison involving NULL as UNKNOWN, not FALSE. A constraint written as CHECK (Price > 0) rejects a negative price correctly, because that comparison is definitely false, but it […]

Read More
SQL

SQL Server: Fix ON DELETE CASCADE Silently Deleting Rows Several Tables Away Through a Chain of Foreign Keys You Forgot About

- 15.10.26 - ErcanOPAK comment on SQL Server: Fix ON DELETE CASCADE Silently Deleting Rows Several Tables Away Through a Chain of Foreign Keys You Forgot About

🗄️ The Delete That Took Three Tables Down With It ON DELETE CASCADE on a foreign key doesn’t stop at the next table – if that child table is itself the parent of another cascading foreign key, deleting the original row triggers a chain reaction straight down through every table that transitively points back at […]

Read More
SQL

SQL Server: Fix a Query That Runs Instantly in SSMS but Times Out From the App Because ARITHABORT Defaults to a Different Value Per Client

- 15.10.26 - ErcanOPAK comment on SQL Server: Fix a Query That Runs Instantly in SSMS but Times Out From the App Because ARITHABORT Defaults to a Different Value Per Client

🗄️ The Query That Was Fast Everywhere Except Where It Mattered SSMS connects with ARITHABORT ON by default; most application driver connections (including the defaults used by many .NET data providers) connect with it OFF. A stored procedure’s execution plan gets cached against whichever SET options were active the first time it compiled – so […]

Read More
SQL

SQL Server: Fix a Cursor-Based Loop That Silently Takes Minutes Where a Set-Based Rewrite Takes Seconds for the Same Result

- 15.10.26 - ErcanOPAK comment on SQL Server: Fix a Cursor-Based Loop That Silently Takes Minutes Where a Set-Based Rewrite Takes Seconds for the Same Result

🗄️ The Cursor That Did One Row at a Time, On Purpose, Forever A cursor processes exactly one row per iteration – fetch, compute, update, commit, fetch again – paying the fixed per-row overhead of the engine’s row-by-row machinery every single time, even when every row’s logic is completely independent of the others. A set-based […]

Read More
SQL

SQL Server: Fix a Query Plan Cache Bloated by Ad-Hoc Queries Because Literal Values Were Never Parameterized

- 15.10.26 - ErcanOPAK comment on SQL Server: Fix a Query Plan Cache Bloated by Ad-Hoc Queries Because Literal Values Were Never Parameterized

🗄️ The Plan Cache Full of One-Time-Use Queries SQL Server caches an execution plan for every distinct query text it compiles, keyed on the literal SQL string itself – so a query built by concatenating a customer ID directly into the WHERE clause produces a brand new, never-reused plan for every single customer. The plan […]

Read More
SQL

SQL Server: Fix a JSON Column Query That Can’t Use an Index Because OPENJSON Was Applied Row by Row

- 15.10.26 - ErcanOPAK comment on SQL Server: Fix a JSON Column Query That Can’t Use an Index Because OPENJSON Was Applied Row by Row

🗄️ The JSON Query That Scanned Every Row to Parse Text SQL Server stores JSON as plain nvarchar text – there’s no native JSON data type with its own index the way there is in some other databases. A query that calls OPENJSON or JSON_VALUE directly in a WHERE clause against that column has to […]

Read More
SQL

SQL Server: Fix a Recursive CTE That Silently Stops Early Because of the Default MAXRECURSION Limit

- 11.10.26 - ErcanOPAK comment on SQL Server: Fix a Recursive CTE That Silently Stops Early Because of the Default MAXRECURSION Limit

🗄️ The Recursive CTE That Stopped at Exactly 100 A recursive common table expression in SQL Server has a built-in safety limit on how many times it will recurse before giving up, specifically so a CTE with a bug in its anchor or recursive member doesn’t spin forever – that limit defaults to 100 recursions, […]

Read More
SQL

SQL Server: Fix a Query Reading Uncommitted Data Because READ_COMMITTED_SNAPSHOT Isn’t What You Assumed

- 11.10.26 - ErcanOPAK comment on SQL Server: Fix a Query Reading Uncommitted Data Because READ_COMMITTED_SNAPSHOT Isn’t What You Assumed

🗄️ The READ COMMITTED That Wasn’t What You Thought The isolation level READ COMMITTED has two entirely different underlying implementations in SQL Server, and which one a database actually runs depends on a database-level setting, not anything in the query itself. With READ_COMMITTED_SNAPSHOT OFF – the server default for most on-premises installs – READ COMMITTED […]

Read More
SQL

SQL Server: Fix a COALESCE Default Value That Gets Silently Truncated Because the Result Took the First Column’s Declared Length

- 11.10.26 - ErcanOPAK comment on SQL Server: Fix a COALESCE Default Value That Gets Silently Truncated Because the Result Took the First Column’s Declared Length

🗄️ The COALESCE Default That Got Cut Down to Size COALESCE has to pick a single data type and length for its result from among all the expressions handed to it, and SQL Server’s data type precedence rules decide that, not whichever argument looks like the intended fallback. When a narrow, explicitly-typed column (a VARCHAR(5), […]

Read More
SQL

SQL Server: Fix a LEFT JOIN That Silently Behaves Like an INNER JOIN Because a WHERE Clause Filtered the Right-Side Table

- 11.10.26 - ErcanOPAK comment on SQL Server: Fix a LEFT JOIN That Silently Behaves Like an INNER JOIN Because a WHERE Clause Filtered the Right-Side Table

🗄️ The LEFT JOIN That Quietly Stopped Being Left A LEFT JOIN promises every row from the left-hand table, filling in NULLs for the right-hand side when there’s no match – but that promise only holds through the join itself. A condition on the right-hand table placed in the WHERE clause runs afterward, against the […]

Read More
SQL

SQL Server: Fix an IDENTITY Column That Jumps by Thousands After a Restart Because of Cached Sequence Values

- 11.10.26 - ErcanOPAK comment on SQL Server: Fix an IDENTITY Column That Jumps by Thousands After a Restart Because of Cached Sequence Values

🗄️ The IDENTITY Column That Skips Ahead After Every Restart SQL Server caches a block of upcoming IDENTITY values in memory so it can hand them out to new rows without a disk write for every single insert, which is exactly the kind of invisible optimization that never needs a second thought until the server […]

Read More
SQL

SQL Server: Fix a Table Variable That Forces a Serial Query Plan Because It Carries No Column Statistics for the Optimizer to Use

- 11.10.26 - ErcanOPAK comment on SQL Server: Fix a Table Variable That Forces a Serial Query Plan Because It Carries No Column Statistics for the Optimizer to Use

🗄️ The Table Variable the Optimizer Flies Blind On A table variable is deliberately excluded from the column-level statistics the query optimizer relies on to estimate how many rows a given step will return – by design, SQL Server treats every table variable as if it holds exactly one row for costing purposes, regardless of […]

Read More
SQL

SQL Server: Fix a Window Function That Returns Unexpected Ties Because ORDER BY Doesn’t Uniquely Sort the Partition

- 10.10.26 - ErcanOPAK comment on SQL Server: Fix a Window Function That Returns Unexpected Ties Because ORDER BY Doesn’t Uniquely Sort the Partition

🗄️ The Ranking That’s Only as Stable as Its Sort Key ROW_NUMBER(), RANK(), and similar window functions assign their numbers based entirely on the ORDER BY clause inside the OVER() clause. When that ordering doesn’t uniquely distinguish every row in a partition, sorting only by a date column when multiple rows share the same date, […]

Read More
SQL

SQL Server: Fix a Columnstore Index That Silently Falls Back to Row-by-Row Inserts Because Rows Arrive Below the Batch Threshold

- 10.10.26 - ErcanOPAK comment on SQL Server: Fix a Columnstore Index That Silently Falls Back to Row-by-Row Inserts Because Rows Arrive Below the Batch Threshold

🗄️ The Columnstore That Secretly Reverts to Row Mode A clustered or nonclustered columnstore index only gets its compression and scan benefits when rows arrive through SQL Server’s batch-mode execution: bulk inserts and large INSERT … SELECT statements route through the columnstore compression pipeline and land directly in compressed row groups. Ordinary single-row INSERT statements, […]

Read More
SQL

SQL Server: Fix a CHECK Constraint That Silently Stops Enforcing New Violations After a Bulk Load Leaves It Marked NOT TRUSTED

- 10.10.26 - ErcanOPAK comment on SQL Server: Fix a CHECK Constraint That Silently Stops Enforcing New Violations After a Bulk Load Leaves It Marked NOT TRUSTED

🗄️ The Constraint That Stopped Checking Loading data with WITH CHECK CONSTRAINTS temporarily disabled, or re-enabling a constraint afterward with the default WITH NOCHECK, re-activates that constraint for all future inserts and updates but leaves it marked as NOT TRUSTED – a state where SQL Server has deliberately decided not to verify whether the existing […]

Read More
SQL

SQL Server: Fix a Stored Procedure’s Cached Execution Plan That Goes Stale the Moment a Client Changes a SET Option Mid-Session

- 10.10.26 - ErcanOPAK comment on SQL Server: Fix a Stored Procedure’s Cached Execution Plan That Goes Stale the Moment a Client Changes a SET Option Mid-Session

🗄️ The Plan That Expired the Moment Someone Else Spoke Up SQL Server caches a stored procedure’s execution plan keyed in part on the SET options in effect when that plan was compiled, because several of them, ANSI_NULLS and ANSI_PADDING in particular, can change how the same query should actually be optimized. A connection pool […]

Read More
SQL

SQL Server: Fix a Partition SWITCH That Rejects a Staging Table Whose Schema Looks Identical but Isn’t

- 10.10.26 - ErcanOPAK comment on SQL Server: Fix a Partition SWITCH That Rejects a Staging Table Whose Schema Looks Identical but Isn’t

🗄️ The Partition Swap That Needed a Perfect Mirror ALTER TABLE … SWITCH PARTITION only succeeds when the source and target genuinely share an identical structure – same columns and types, same indexes with matching key order and included columns, same constraints, and the same compression setting – and SQL Server checks this strictly before […]

Read More
SQL

SQL Server: Fix an OUTPUT Clause That Returns the Wrong Values Because a Trigger Already Fired Before It Could Capture the Rows

- 10.10.26 - ErcanOPAK comment on SQL Server: Fix an OUTPUT Clause That Returns the Wrong Values Because a Trigger Already Fired Before It Could Capture the Rows

🗄️ The OUTPUT That Captured a Trigger’s Work, Not Yours The OUTPUT clause on an INSERT, UPDATE, or DELETE statement captures the affected rows as they exist at the moment that statement’s own data change is applied – it is not documented to reflect any further change an AFTER trigger on the same table makes […]

Read More
SQL

SQL Server: Fix a Query That Gets Slower After You Add an Index Because the Optimizer Now Picks a Worse Plan

- 04.10.26 - ErcanOPAK comment on SQL Server: Fix a Query That Gets Slower After You Add an Index Because the Optimizer Now Picks a Worse Plan

🗄️ The Index That Made the Query Slower, Not Faster Adding an index is supposed to give the query optimizer another option, never take one away, so a newly indexed query that suddenly runs slower than before feels backwards – but a new index can change which plan the optimizer’s cost estimates actually favor, and […]

Read More
SQL

SQL Server: Fix a Trigger That Fires Once Per Statement Instead of Once Per Row and Silently Processes Only the First Row

- 04.10.26 - ErcanOPAK comment on SQL Server: Fix a Trigger That Fires Once Per Statement Instead of Once Per Row and Silently Processes Only the First Row

🗄️ The Trigger That Only Ever Saw One Row A trigger fires exactly once per statement, not once per affected row, and the inserted and deleted pseudo-tables inside it can hold any number of rows at once when a single statement updates or inserts several rows together – a batch import, a bulk status change, […]

Read More
SQL

SQL Server: Fix a Recursive CTE That Runs Forever Because the Anchor and Recursive Parts Never Agree on a Stop Condition

- 04.10.26 - ErcanOPAK comment on SQL Server: Fix a Recursive CTE That Runs Forever Because the Anchor and Recursive Parts Never Agree on a Stop Condition

🗄️ The Recursive Query That Never Agreed On When to Stop A recursive common table expression has two halves that have to cooperate to actually terminate – the anchor member seeds the first set of rows, and the recursive member joins back to the expression’s own name to produce the next set, repeating until a […]

Read More
SQL

SQL Server: Fix a Query That Fails With a Collation Conflict Only When Joining Tables From Two Different Databases

- 04.10.26 - ErcanOPAK comment on SQL Server: Fix a Query That Fails With a Collation Conflict Only When Joining Tables From Two Different Databases

🗄️ The Join That Refused to Compare Two Perfectly Normal Columns A text column’s collation is normally inherited from its own database’s default collation, not from the server as a whole, and two databases created at different times or restored from backups taken on different servers can easily end up with different default collations even […]

Read More
SQL

SQL Server: Why ANSI_NULLS Being Set to OFF Silently Breaks Every = NULL Comparison in Legacy Code

- 04.10.26 - ErcanOPAK comment on SQL Server: Why ANSI_NULLS Being Set to OFF Silently Breaks Every = NULL Comparison in Legacy Code

🗄️ The NULL Comparison That Quietly Stopped Comparing Anything Standard SQL treats any comparison against NULL – including column = NULL – as unknown rather than true or false, which is why the correct way to test for NULL is always IS NULL, never = NULL. SQL Server’s ANSI_NULLS setting controls whether it actually enforces […]

Read More
SQL

SQL Server: Fix an Orphaned User That Can’t Log In After Restoring a Database to a New Server

- 04.10.26 - ErcanOPAK comment on SQL Server: Fix an Orphaned User That Can’t Log In After Restoring a Database to a New Server

🗄️ The Login That Moved Servers and Left Its Identity Behind A SQL Server login and a database user are two separate objects linked internally by a security identifier, not by name – the login lives at the server level, the user lives inside the database, and the SID is what ties them together. Restoring […]

Read More
SQL

SQL Server: Fix a Filtered Index That Gets Silently Ignored Because the Query’s WHERE Clause Doesn’t Match It Exactly

- 03.10.26 - ErcanOPAK comment on SQL Server: Fix a Filtered Index That Gets Silently Ignored Because the Query’s WHERE Clause Doesn’t Match It Exactly

🗄️ The Filtered Index That Was Never Actually Considered A filtered index narrows an index down to the rows that matter – active orders, non-null email addresses, a specific status – which keeps it small and fast for the queries that only ever care about that subset. The optimizer will only use it, though, when […]

Read More
SQL

SQL Server: Why a GUID Primary Key Fragments a Table Far Faster Than an Identity Column Ever Would

- 03.10.26 - ErcanOPAK comment on SQL Server: Why a GUID Primary Key Fragments a Table Far Faster Than an Identity Column Ever Would

🗄️ The Primary Key That Scattered Every New Row Across the Whole Table A clustered index physically orders the table’s data pages to match the key, which is cheap to maintain when new values always sort to the end – an IDENTITY column does exactly that, so every insert simply appends a new row to […]

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

Posts pagination

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