0

I have a table TableA with a computed column FieldA bound to a function that returns a float. The function is GetFieldA which calls two sub functions.

I also have a view which includes the GetFieldA function not the computed column value as a field. I just tried to add a index to the view which said it required GetFieldA to be schema bound in order to have a index on it, I changed it to be schema bound (which involved removing the computed column) but then when i went back to try to readd the computed column it said in order to use the GetFieldA the schema bound must be removed.

Is there any way to have a computed column with a schema bound function or do i need to rework how im getting the field in order to be able to have a view index?

Manatherin
  • 4,169
  • 5
  • 36
  • 52

1 Answers1

1

It's impossible to add index on a computed column, unless it's deterministic.

"Deterministic functions always return the same result any time they are called with a specific set of input values and given the same state of the database. Nondeterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same."

Example:

CREATE FUNCTION dbo.FuncA()
RETURNS [float]
WITH SCHEMABINDING    -- required option
BEGIN
  RETURN 1.0   -- DB engine parses body, and marks this func. as 'deterministic'
END
GO

CREATE TABLE TableA (
  K int primary key clustered,
  A AS dbo.FuncA() PERSISTED    -- must be persisted
)
GO

CREATE VIEW ViewA
WITH SCHEMABINDING    -- required option
AS
  SELECT K, A FROM dbo.TableA
GO

CREATE UNIQUE CLUSTERED INDEX IDX1 ON dbo.ViewA (K, A)
GO

You have to specify PERSISTED for imprecise data types like [real] and [float], in other case you can freely create an index on a view with computed column ([decimal] will be OK).

Optillect Team
  • 1,737
  • 2
  • 11
  • 12
  • i dont want the field A in table a to be persisted and i think youve missread my question in the view its more like SELECT dbo.FuncA(float value) not select from dbo.TableA – Manatherin Jul 18 '11 at 13:44
  • You have to specify PERSISTED for imprecise data types like [real] and [float], in other case you can freely create an index on a view with computed column ([decimal] will be OK). I mean `CREATE VIEW .. SELECT dbo.Func()`, where `dbo.Func()` returns say decimal(18,4). But if `dbo.Func()` calls `dbo.Func2()` that returns [float] - that won't work. – Optillect Team Jul 18 '11 at 15:18
  • Ok thanks this seems to be a definitive answer, unfortunately my function does call another function and its also not deterministic so I will have to rework my approach – Manatherin Jul 18 '11 at 15:38