SQL Server: implementing Slow Query Performance After Adding Computed Column with Index
I've been banging my head against this for hours. I'm trying to implement I'm working on a personal project and I'm relatively new to this, so bear with me. I'm experiencing significantly slower query performance after adding a computed column to my table in SQL Server 2019. The computed column is defined as follows: ```sql ALTER TABLE dbo.MyTable ADD ComputedValue AS (Col1 + Col2); ``` After creating this computed column, I decided to add an index to improve performance: ```sql CREATE INDEX IX_ComputedValue ON dbo.MyTable(ComputedValue); ``` However, I've noticed that a simple SELECT query that previously executed in under a second now takes over 10 seconds: ```sql SELECT * FROM dbo.MyTable WHERE ComputedValue > 100; ``` I ran the query plan and found that SQL Server is performing a full scan instead of using the index. The estimated number of rows is much higher than it was before the computed column was added. I also checked the statistics for the table and updated them using: ```sql UPDATE STATISTICS dbo.MyTable; ``` Even after updating statistics, the scenario continues. I suspect it might be related to how the computed column is being calculated or indexed. Could this be a data type scenario, or is there something I need to consider when indexing computed columns? Furthermore, I tried rewriting the query to explicitly cast the computed column: ```sql SELECT * FROM dbo.MyTable WHERE CAST(ComputedValue AS INT) > 100; ``` But this still results in a full table scan. Any insights into why this is happening or how to improve the performance would be greatly appreciated! Is there a better approach? This is part of a larger CLI tool I'm building. I'm using Sql LTS in this project. Is there a simpler solution I'm overlooking? I recently upgraded to Sql LTS. I appreciate any insights!