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 Server: Fix a Computed Column Index Skipped by Implicit Cast

- 16.11.26 - ErcanOPAK

🗄️ SQL Server: Fix a Computed Column Index Skipped by Implicit Cast

A computed column index can only be used when a predicate’s expression matches the computed column’s own definition closely enough for the optimizer to recognize it – including matching data types exactly. Comparing the computed column against a parameter of a different type than what it resolves to forces an implicit conversion before the comparison runs, which breaks the expression match the optimizer relies on to substitute the indexed computed column. An ORM that sends every parameter as a string by default is a common, invisible source of exactly this mismatch, and the index that sys.indexes clearly shows as present can simply stop being chosen the moment a driver changes how it types its parameters.

🔎 The Problem

-- Computed column definition
ALTER TABLE Orders ADD OrderCode AS CAST(OrderNumericId AS INT);
CREATE INDEX IX_Orders_OrderCode ON Orders(OrderCode);

-- ORM sends OrderCode as an NVARCHAR parameter by default:
SELECT * FROM Orders WHERE OrderCode = @p0;  -- @p0 nvarchar(4000)
-- the implicit CONVERT(nvarchar, OrderCode) breaks the expression
-- match -> IX_Orders_OrderCode is no longer considered by the optimizer

✅ Fix: Match the Computed Column’s Exact Resulting Type

  • Compare the actual execution plan’s predicate against the computed column’s exact definition (queryable from sys.computed_columns) to confirm the data types genuinely match, rather than assuming a query referencing the same underlying columns automatically matches the computed expression.
  • Explicitly cast the parameter or literal to the computed column’s exact resulting type in the query itself, instead of relying on an ORM’s default parameter typing to happen to match.
  • Capture the parameter types a specific client library actually sends with an extended events session, since the type mismatch is often invisible in the application’s own types and only shows up at the wire protocol level.

🚨 Why This Is Dangerous

  • A computed column index that “used to work” and silently isn’t chosen anymore, with no error and no obvious plan change in casual review, can mean a report that was fast for months starts timing out in production the moment a driver version bump changes its default parameter type.

A computed column index matches an expression, not just a column name – and an implicit cast is enough of a different expression to lose that match entirely.

— Database Engineer

Related posts:

SQL: Calculate Running Totals with ROW_NUMBER() and OVER()

SQL: Calculate Running Totals with ROW_NUMBER() and OVER()

SQL: Use Stored Procedures for Database Logic

SQL: Use Stored Procedures for Database Logic

Why MERGE Can Corrupt Data Under Concurrency

Why MERGE Can Corrupt Data Under Concurrency

Views: 2

Post navigation

HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File

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,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (976)
  • Get the First and Last Word from a String or Sentence in SQL (857)
  • How to select distinct rows in a datatable in C# (852)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (607)
  • Average of all values in a column that are not zero in SQL (569)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (546)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

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

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

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