5

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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.