Cannot create index on view with User Defined Function in SQL Server
According to this page:
Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.
GetCurrentImage is not deterministic with respect to its parameters - it uses a select, which means the results may change as the data changes - so any view using it cannot be indexed.
There's a recent documentation: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2016
Under Additional Requirements
it says: The data access property of a user-defined function must be NO SQL, and external access property must be NO.
You can check you UDF with the following statement
SELECT ObjectPropertyEx(Object_Id('dbo.GetCurrentImage'), N'IsDeterministic') AS deterministic,
ObjectPropertyEx(Object_Id('dbo.GetCurrentImage'), N'IsPrecise') AS precise,
ObjectPropertyEx(Object_Id('dbo.GetCurrentImage'), N'IsSystemVerified') AS verified,
ObjectPropertyEx(Object_Id('dbo.GetCurrentImage'), N'UserDataAccess') AS UserDataAccess,
ObjectPropertyEx(Object_Id('dbo.GetCurrentImage'), N'SystemDataAccess') AS SystemDataAccess;
The first three should give 1
, the last two must be 0
for a function to be used in an indexed view.