I have a weird situation that I don't quite understand.
I have a table like this kinda:
CREATE TABLE dbo.cc_demo ( id INT IDENTITY PRIMARY KEY, up_action INT, down_action INT, last_action_date DATETIME ); INSERT dbo.cc_demo ( up_action, down_action, last_action_date ) SELECT TOP 5000000 nums.num % 500000, nums.num % 500000, DATEADD(MINUTE, nums.num, GETDATE()) FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS num FROM master..spt_values AS sv CROSS JOIN master..spt_values AS sv2 ) AS nums;
If I run this query, the plan is just a regular clustered index scan.
SELECT * FROM dbo.cc_demo AS cd WHERE cd.last_action_date >= '20270601' AND cd.last_action_date < '20270901';
But if I add a computed column and index it, my plan changes for the worst.
ALTER TABLE dbo.cc_demo ADD total_actions AS up_action + down_action; CREATE INDEX ix_total_actions ON dbo.cc_demo (total_actions);
Now it scans the clustered index and then filters stuff out way later on!
It asks for an index, and that changes the plan back to what I'd expect, but why do I have to add this?
CREATE NONCLUSTERED INDEX [WORLDSTAR] ON dbo.cc_demo ( last_action_date ) INCLUDE ( up_action, down_action, total_actions );
That doesn't seem logical.
Table 'cc_demo'. Scan count 1, logical reads 17990, SQL Server Execution Times: CPU time = 234 ms, elapsed time = 224 ms.
Post computed column + index:
Table 'cc_demo'. Scan count 1, logical reads 17990 SQL Server Execution Times: CPU time = 594 ms, elapsed time = 591 ms.
Here's the breakdown:
- When I add the computed column, the plan doesn't change
- When I index the computed column, it does
- I'm not too worried about the performance yet
- I'm just curious why the plan changes this way after I index the computed column