Sql server - The provided statistics stream is corrupt

Question

I am trying to create statistics on a table that already exists. I kept getting this error : The provided statistics steam is corrupt on some of the stats i am trying to create, but not all.

Why does this happen and how do I fix it? Please help

This is one of my stats query just for information:

if not exists (select * from sys.stats where name = N'_WA_Sys_00000034_452AF57A' and object_id = object_id(N'[dbo].[PromotionBenefit]'))
CREATE STATISTICS [_WA_Sys_00000034_452AF57A] ON [dbo].[PromotionBenefit]([DeductPointValue]) WITH STATS_STREAM = 0x010000000100000000000000000000008A2F619B000000001004000000000000D0030000000000006A0300006A0000000900120000000000000000000000000007000000A731CE00C6A60000F63C000000000000172C0000000000000000803F3694573D0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000110000001100000001000000190000000000104100D87346000000000000104100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001100000000000000000000000000000064020000000000006C020000000000008800000000000000A400000000000000C000000000000000DC00000000000000F800000000000000140100000000000030010000000000004C0100000000000068010000000000008401000000000000A001000000000000BC01000000000000D801000000000000F40100000000000010020000000000002C0200000000000048020000000000001000190057147246000000000000803F0100000000000000000400001000190022290441000000000000803F01010000000000000004000010001900BD8B7A40000000000000803F010200000000000000040000100019000000803F000000000000803F0103000000000000000400001000190091640D40000000000000803F0105000000000000000400001000190091640D40000000000000803F0106000000000000000400001000190091640D40000000000000803F010A000000000000000400001000190091640D40000000000000803F011400000000000000040000100019000000803F9C9018429C9018420128000000000000000400001000190091640D40000000000000803F013C00000000000000040000100019000000803F000000000000803F015000000000000000040000100019002C27AD40000000000000803F016400000000000000040000100019000000803F000000000000803F014A01000000000000040000100019000000803F000000000000803F019001000000000000040000100019000000803F000000000000803F01E803000000000000040000100019000000803F9C9018429C90184201C409000000000000040000100019000000803F000000000000803F018813000000000000040000F63C000000000000
GO

EDIT: So basically, I am doing data archiving on some tables in my database. Only one server is in use. Let's say I have tableA and I want to archive it . The step would be : 1. Create a new table with same columns as tableA , let's call it tableA_New. Then transfer records i want from original table to the new table. Then drop original table and rename the new table to original table name,i.e tableA.

After that, I would add back those constraints,triggers,indexes,statistics associated with table A back into it. That's where some stats I tried to add, it would throw me the error, only happens with some stats i am trying to create.

Answer 1

This is possibly due to the fact that the STATS_STREAM value that you are implementing is faulty. This can have multiple reasons, none of which seem to documented outside of Microsoft.

Official Documentation

The only useful reference to STATS_STREAM comes from the official Microsoft Documentation CREATE STATISTICS (Transact-SQL) in which Microsoft states:

STATS_STREAM =stats_stream

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

That says quite a lot about this parameter.

We (Microsoft) use it, but you (end-user) shouldn't.

(non-official) use case for STATS_STREAM

There is a case that has been blogged about by Thomas Kejser in which he uses "faked" STATS_STREAMs to populate a "smaller" table with values that could possibly be produced in the future.

He goes on to explain a possible situation in which you would want to implement "wrong" statistics in order to deceive the query analyzer to use "fabricated" statistics to produce a better execution plan for values that have only just been inserted and are thus not normally updated fast enough:

There is another approach that does not rely on the optimizer making guesses: You can intentionally deceive the optimizer, lie about the distribution. Recall that we want the “desired DW plan”. Alas, as with all telling of lies, this exposes you to a lot of risk.

Reference: The Ascending Key Problem In Fact Tables –Part Two: Stat Job!

Situation at hand

However, your situation does not look like you want to deceive the query optimizer, but to just recreate the system generated statistics on your migrated table.

Now because you have modified a large amount of data, by deleting the records you no longer require, you are possibly invalidating the STATS_STREAM value that you retrieved from the original table.

Solutions

Option 1

Instead of trying to fill the system generated statistics with "fake" values that don't seem to work, why not just create new statistics for the columns that have had automatic statistics created for them?

You would just take your individual statements and instead of using the system generated name, replace it with a user defined name:

CREATE STATISTICS [PromotionBenefit_DeductPointValue] ON [dbo].[PromotionBenefit]([DeductPointValue])

This has the advantage that the statistics are created correctly and do not have to rely on fake values.

You can determine the columns of the system generated statistics by running the following statement:

SELECT s.name AS statistics_name
      ,c.name AS column_name  
      ,sc.stats_column_id  
FROM sys.stats AS s  
INNER JOIN sys.stats_columns AS sc   
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
INNER JOIN sys.columns AS c   
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id  
WHERE s.object_id = OBJECT_ID('dbo.PromotionBenefit')
AND s.name LIKE '_WA_Sys_%'
ORDER BY 1,3

This will give you a list of all system generated statistics for your table and the columns in the statistics.

Option 2

Just insert the data and let the database engine created new system generated statistics as soon as queries are performed against the table.

Answer 2

I am doing data archiving on some tables in my database.
...

Then transfer records i want from original table to the new table.

...

After that, I would add back those constraints,triggers,indexes,statistics associated with table A back into it. That's where some stats I tried to add, it would throw me the error, only happens with some stats i am trying to create.

All this is fine except for transfering old statistics.

Statistics are used to build optimal execution plan, they reflect data distribution in your columns.

When you'll query your new table, all the missing statistics will be recreated unless you disabled AUTO_CREATE_STATISTICS (the option is ON by default).

If you set AUTO_CREATE_STATISTICS to OFF and want to recreate all the statistics that existed on the old table you should just recreate them, all that you need for this is to know on what columns old statistics were created.

This way you'll get fresh statistics on your new table.

The undocumented option that you was trying to use serve another purpose: it's used with update statistics to recreate the same statistics in another environment. Here "the same" means not to use the same column to create statistics, but set another, not actual statistics, statistics taken from another table with another data distribution, on your table.

I mean if your original table had 1000 rows and half of them had col1 = 0 and another 500 rows had col1 = 1, you could create the same table on another server, put there only one row, and transfer data distribution as 500 rows having 0 + 5000 rows having 1. And this is not what you want. You want real data distribution on your new table, so just create the statistics on col1 that will reflect what is now in this table without using WITH STATS_STREAM.