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?
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."
- Creating Indexes on Computed Columns
- User-Defined Function Design Guidelines
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).
