Hi,
I almost got the question right, but failed because of the usage of index on computed columns.
I have tested it with the following script and the index seems to be unused when ANSI_PADDING is off:
SET ANSI_PADDING ON
GO
DROP TABLE dbo.Comp;
CREATE TABLE dbo.Comp (a int NOT NULL PRIMARY KEY NONCLUSTERED, B AS (a+1*2)+a);
WITH A AS (SELECT 1 AS Num UNION ALL SELECT 1 UNION ALL SELECT 1
) ,B AS (SELECT A1.Num FROM A A1,A A2,A A3)
INSERT INTO dbo.Comp
SELECT ROW_NUMBER() OVER (ORDER BY B1.Num) Num FROM B B1, B B2, B B3;
GO
CREATE UNIQUE INDEX IComp ON dbo.Comp(B);
GO
SET NOCOUNT ON
SET ANSI_PADDING ON
GO
SET SHOWPLAN_TEXT ON
GO
SELECT B FROM dbo.Comp WHERE B = 1;
GO
SET SHOWPLAN_TEXT OFF
GO
SET ANSI_PADDING OFF
GO
SET SHOWPLAN_TEXT ON
GO
SELECT B FROM dbo.Comp WHERE B = 1;
GO
SET SHOWPLAN_TEXT OFF
GO
StmtText
--------------------------------------
SELECT B FROM dbo.Comp WHERE B = 1;
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([master].[dbo].[Comp].=[master].[dbo].[Comp].))
|--Index Seek(OBJECT:([master].[dbo].[Comp].[IComp]), SEEK:([master].[dbo].[Comp].=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
StmtText
--------------------------------------
SELECT B FROM dbo.Comp WHERE B = 1;
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([master].[dbo].[Comp].=[master].[dbo].[Comp].))
|--Compute Scalar(DEFINE:([master].[dbo].[Comp].=([master].[dbo].[Comp].[a]+(2))+[master].[dbo].[Comp].[a]))
|--Table Scan(OBJECT:([master].[dbo].[Comp]), WHERE:((([master].[dbo].[Comp].[a]+(2))+[master].[dbo].[Comp].[a])=CONVERT_IMPLICIT(int,[@1],0)))
Besides this, the answer
Altering an index on a computed column will not work
may not be fully correct either, since the following examples seem to work with ANSI_PADDING being either ON or OFF:
ALTER INDEX IComp ON dbo.Comp SET (STATISTICS_NORECOMPUTE = ON,
ALLOW_PAGE_LOCKS = ON
);
or ALTER INDEX IComp ON dbo.Comp REORGANIZE;
Best Regards,
Chris Büttner