InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 22:35:03 GMT20http://www.sqlservercentral.com/Forums/Topic1384753-391-1.aspx[quote][b]waxingsatirical (11/14/2012)[/b][hr]Hi Gaz,The code I've posted is just some sample code to replicate the problem. When this happened in real life the persisted column was not just a repeat of another column in the table![/quote]Good, I'd hope not! :-)Would need a closer approximation of your specific scenario to try and reproduce - if I make the change I mentioned above then the covering index is used in query 3 & 4. So I can't really tell what's happening in your case with the information given.ThanksGazThu, 15 Nov 2012 03:39:32 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1384753-391-1.aspxHi Gaz,The code I've posted is just some sample code to replicate the problem. When this happened in real life the persisted column was not just a repeat of another column in the table!Wed, 14 Nov 2012 13:35:04 GMTwaxingsatiricalhttp://www.sqlservercentral.com/Forums/Topic1384753-391-1.aspxI think SQL optimiser is second-guessing you because your computed column is equal to the 'real' one.See if you get the behaviour you expect when the column is defined as: col1Persisted AS col1*2 PERSISTEDCheersGazWed, 14 Nov 2012 10:43:07 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1384753-391-1.aspxHi All,I came across this rather odd bit of behaviour so I thought I would share it. Either I'm missing something or this is non-ideal behaviour from the optimiser.I am attempting to use a non-clustered covering index to speed up a large join I'm doing. The value I need from the table is a persisted computed column and I need it ordered for the join so the index is made to match the join conditions.Trouble is, whatever I do the optimiser decides to ignore my covering index! Grr. If I change the computed column to a regular column then the corresponding covering index is used, so I guess this is something to do with persisted columns and covering indices not playing well together.There is some SQL below to replicate the issue. Turn on Actual Execution plan and look at queries 3, 4 & 6. You'll see that when I force the use of the covering index it still does a RID Lookup. Cheeky so-and-so![code="sql"]-- CREATE the table for the testIF OBJECT_ID('PersistedTest') > 0BEGIN DROP TABLE PersistedTestENDCREATE TABLE PersistedTest (id INT NOT NULL, col1 INT, col1Persisted AS col1 PERSISTED)-- Populate with a thousand (ish!) rows of dummy data;WITH a AS (SELECT 0 AS numUNION ALLSELECT a.num + 1FROM aWHERE a.num < 1000)INSERT INTO PersistedTestSELECT CAST(CAST(NEWID() AS BINARY(4)) AS INT) AS id , 999 AS col1FROM aOPTION (MAXRECURSION 0)GO -- Create covering index over the persisted columnCREATE UNIQUE NONCLUSTERED INDEX IX_PersistedTest_col1Persisted ON PersistedTest (id) INCLUDE (col1Persisted)GO-- SELECT statement that should use covering index but doesn'tSELECT id , col1PersistedFROM PersistedTestORDER BY id-- SELECT statement forced to use covering index but still doesn't!SELECT id , col1PersistedFROM PersistedTest WITH ( INDEX(IX_PersistedTest_col1Persisted))ORDER BY id-- Create covering index over regular column for control testCREATE UNIQUE NONCLUSTERED INDEX IX_PersistedTest_col1 ON PersistedTest (id) INCLUDE (col1)-- SELECT statement that uses covering index as expected3SELECT id , col1FROM PersistedTestORDER BY id[/code]Any ideas? I don't really want to add code to populate the column manually.Wed, 14 Nov 2012 10:14:43 GMTwaxingsatirical