It's been over a month since I've blogged, confirmed by the previous blog entry when "it's sunny in Barcelona". Thought I'd blog about a question that someone wrote to ask me a few weeks ago.
What are the limitations/requirements for using a SQLCLR function in an indexed view (aka materialized view)?
Some of the requirements came from the books online, but can be verified with a simple sample, but there were a few surprises.
1. The VIEW must be created WITH SCHEMABINDING. This is a "normal" requirement of an indexed view, but usually when creating VIEWs WITH SCHEMABINDING that reference UDFs, the UDF must be defined WITH SCHEMABINDING as well. SQLCLR UDFs can't be explicitly be defined WITH SCHEMABINDING, but they can be used in views defined as WITH SCHEMABINDING.
2. The VIEW can't use a SQLCLR derived column as part of the index key unless the derived column is declared as PERSISTED in the base table.
3. The SQLCLR function must be declared Deterministic and Precise, and do no data access using SqlCommand, etc. DataAccess = None, SystemDataAccess = None is the default in SQLCLR functions anyway. And no external access is allowed.
4. An indexed view cannot contain a SQLCLR user-defined aggregate (UDA) function.
To demonstrate, I wrote two functions AddOne in SQLCLR, TSQLAddOne in TSQL. You can use AddOne in an indexed view. You can use AddOne as a key column in an indexed view if its defined as persisted in the base table. Note that the TSQLAddOne can be used as an index only if its defined WITH SCHEMABINDING.
This is fairly similar to the restrictions for TSQL functions and indexed views, except that you can access data in TSQL function and use it as the key, only if the TSQL function is defined with schemabinding. SQLCLR functions can't be defined with schemabinding, because there is no way to figure out which tables, etc, it's accessing. So because you can declare a TSQL function with schemabinding, you can probably make it part of the key if you access data. But, as when using a SQLCLR function in a VIEW WITH SCHEMABINDING, this shouldn't be an issue when your SQLCLR function does no data access. Hmmm…
Check out "table3 and view3". It uses SQLCLR function in a persisted computed column, and then as the KEY in an indexed view derived from the table.