Koen (da-zero) (1/10/2011)

---

Good, but difficult question. I had to read quite some BOL pages to get this one right.
And only 1 point?

Carlo Romagnano (1/11/2011)

---

I get it wrong, because I don't want waste my time to learn BOL for a deprecated option.

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].[B]=[master].[dbo].[Comp].[B]))
|--Index Seek(OBJECT[master].[dbo].[Comp].[IComp]), SEEK[master].[dbo].[Comp].[B]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

StmtText
--------------------------------------
SELECT B FROM dbo.Comp WHERE B = 1;

StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE[master].[dbo].[Comp].[B]=[master].[dbo].[Comp].[B]))
|--Compute Scalar(DEFINE[master].[dbo].[Comp].[B]=([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)))

Altering an index on a computed column will not work

ALTER INDEX IComp ON dbo.Comp SET (STATISTICS_NORECOMPUTE = ON,
    ALLOW_PAGE_LOCKS = ON
);

ALTER INDEX IComp ON dbo.Comp REORGANIZE;

[b]Koen (da-zero)
And what if your company or your client uses an older version of SQL Server (there are still some SQL 2000 or older out there. SQL Server 2005 is still common)? Doesn't it make sense to know some of the implications of such a setting?

Carlo Romagnano (1/11/2011)

---

But the question is in SQL2008.