> Can indexed views do *anything*? *Ever*?

It actually is kind of miserable when you think of an indexed view as a solution, only to find your use case hits on one of the many limitations of indexed views (I'm looking at you, `LEFT JOIN`).

You're right that you can't include the SQLCLR in the *index keys* of the indexed view.  However, you can include it in the `SELECT` list of the view definition, which will persist the value to disk.  So you can at least avoid the cost of calculating the value on the fly when reading from the table.

In the AdventureWorks2014 sample database, I created this view on the delightfully named `Person.Person` table:

```
CREATE OR ALTER VIEW dbo.PersonWithHashForSomeReason
WITH SCHEMABINDING
AS
SELECT 
	BusinessEntityID,
	PersonType,
	dbo.SpookyHash(CONVERT(binary(50), FirstName)) AS FirstNameHash
FROM Person.Person
GO
```

*Note: I was too lazy to write my own CLR function, so this one is from [this Q&A][1].*

I can make that an indexed view by clustering it on `BusinessEntityID`:

```
CREATE UNIQUE CLUSTERED INDEX CX_BusinessEntityID 
ON dbo.PersonWithHashForSomeReason (BusinessEntityID);
GO
```

Looking for a specific set of rows in the table produces an index scan ([execution plan link][2]).  Notice the lack of a `Compute Scalar` operator, which would normally be used to produce the hash value.  Since the hash is persisted to disk in the indexed view, that's not necessary:

```
SELECT BusinessEntityID, FirstNameHash 
FROM dbo.PersonWithHashForSomeReason WITH (NOEXPAND)
WHERE FirstNameHash = 0x910C426C533F2C0AAF350158331E3B01;
```

[![Screenshot of execution plan in Plan Explorer][3]][3]

I had to use a `NOEXPAND` hint to get it to use the view.

You'll notice the warnings in the plan are due to the fact that the whole table was scanned in order to find those values, since there is no index keyed on `FirstNameHash`.

Unfortunately, trying to create a nonclustered index on this persisted value fails:

```
CREATE NONCLUSTERED INDEX IX_FirstNameHash 
ON dbo.PersonWithHashForSomeReason (FirstNameHash);
```

> Msg 1976, Level 16, State 1, Line 48  
> Cannot create index or statistics 'IX_FirstNameHash' on view 'dbo.PersonWithHashForSomeReason' because cannot verify key column 'FirstNameHash' is precise and deterministic. Consider removing column from index or statistics key, marking column persisted in base table if it is computed, or using non-CLR-derived column in key.

This is because SQL Server [doesn't trust us][4]:

> Why don’t we trust the user? What can really go wrong here??
> 
> Assume if the user incorrectly sets the custom attribute
> IsDeterminsitic to true for a non-deterministic function and assume he
> is able to create an index on a computed column that invoked this
> function without persisting it. [...] **This might lead to index**
> **corruption** as the function might return different values for the same
> input as the function is non-deterministic.
> 
> So for the sake of user’s safety, Sql Server in this release REQUIRES
> the user to persist the computed columns (unlike the tsql case) to
> actually index the computed columns.

I imagine the same limitation exists for fulltext indexes, although I haven't tried it.

---

The takeaway from all this, as you mentioned in your own post, and as has been mentioned in the comments several times, is that you're kind of stuck here.  Really your only option is to:

- accept the table scan (not ideal, and probably not even practical based on your description), or
- use triggers to "manually" keep this value up to date in a normal column

  [1]: https://dba.stackexchange.com/a/228854/6141
  [2]: https://www.brentozar.com/pastetheplan/?id=SJg_fOnNH
  [3]: https://i.sstatic.net/UE2Qx.png
  [4]: https://blogs.msdn.microsoft.com/sqlclr/2005/10/03/tutorial-on-sqlclr-computed-columns-and-indexability/