Statistics on filestream column

Question

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.

Answer 1

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:

• String summary - slow statistics creation on VARBINARY(MAX) column

...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.