When I try to create a statistics on a filestream column, I get the following error message:
Column 'MyColumn' in table 'MyTable' is of a type
that is invalid for use as a key column in an index or statistics.
This is documented in the BOL page for CREATE STATISTICS and it's not an issue.
However, when I run a query with a predicate on the column (IS NOT NULL, for the record), a statistics is auto-created. Why is this disallowed for manual creation but still possible for auto creation?
The reason why I want to create a statistics manually is that I want to mark it NORECOMPUTE in order to avoid having to scan several GBs of data. I know I can let SQL Server auto create it and then mark it NORECOMPUTE later, but auto creation is triggered with the default sample, which I could override to 0 ROWS in manual creation. I would also like to know if there is a reason for this.
Best Answer
If you have applied the latest update for SQL Server 2012 - Cumulative Update 2 for Service Pack 3 as at the time of writing - and the issue persists, you should open a support ticket, or report the issue on Connect.
The underlying issue shares some common features with an existing report:
...which might give you some ideas for a workaround.
For example, there is a small chance that creating statistics on a computed column casting the column to varbinary(200) might work. That cast may also explain why the auto-stats succeeds while you cannot create statistics manually.