-- CREATE the table for the test
IF OBJECT_ID('PersistedTest') > 0
BEGIN
  DROP TABLE PersistedTest
END

CREATE 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 num
UNION ALL
SELECT a.num + 1
FROM a
WHERE a.num < 1000
)
INSERT INTO PersistedTest
SELECT CAST(CAST(NEWID() AS BINARY(4)) AS INT) AS id , 999 AS col1
FROM a
OPTION (MAXRECURSION 0)

GO        

-- Create covering index over the persisted column
CREATE UNIQUE NONCLUSTERED INDEX IX_PersistedTest_col1Persisted ON PersistedTest (id) INCLUDE (col1Persisted)

GO

-- SELECT statement that should use covering index but doesn't
SELECT    id
        , col1Persisted
FROM      PersistedTest
ORDER BY  id

-- SELECT statement forced to use covering index but still doesn't!
SELECT    id
        , col1Persisted
FROM      PersistedTest WITH ( INDEX(IX_PersistedTest_col1Persisted))
ORDER BY  id


-- Create covering index over regular column for control test
CREATE UNIQUE NONCLUSTERED INDEX IX_PersistedTest_col1 ON PersistedTest (id) INCLUDE (col1)


-- SELECT statement that uses covering index as expected3
SELECT    id
        , col1
FROM      PersistedTest
ORDER BY  id