When you are working with partition switching, SQL Server will need to verify that the source table/partition boundaries can fit in the destination table/partition boundaries. In other words, you're trying to switch data from dbo.temp_table to dbo.play_table's partition 2. Think of it like this, the data for the c1 in dbo.temp_table is constrained only by the data type (int), so you can have values ranging from -2,147,483,648 to 2,147,483,647. But conversely, you're destination (dbo.play_table partition 2) has a range from 4 to 2,147,483,647.
Your data does not violate this, but it is the metadata that can't allow this. You could just as easily insert the value -10 into dbo.temp_table. The partition switching would fail the same way and make more sense, as -10 does not fit in dbo.play_table's 2nd partition boundaries.
If you wanted to make this code work, you'd need to explicitly tell SQL Server that dbo.temp_table will never have any data that won't fit in dbo.play_table's 2nd partition. You could do this with a check constraint:
/******************************************************************************
your code omitted for brevity
******************************************************************************/
-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';
/******************************************************************************
added check constraint so that data can fit in the destination partition
******************************************************************************/
alter table dbo.temp_table
add constraint CK_TempTable_C1 check (c1 >= 4);
go
/******************************************************************************
end of added code
******************************************************************************/
-- move data back to partitioned play_table from unpartitioned temp_table
-- this will no longer FAIL
ALTER TABLE dbo.temp_table
SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';
/******************************************************************************
your code omitted for brevity
******************************************************************************/
That above sample addition to your code makes this a working solution. Now SQL Server knows that the data in dbo.temp_table can fit in partition 2 of dbo.play_table because of the added check constraint to dbo.temp_table.
I still see StatMan operations captured if I use SQL Sentry Plan Explorer PRO against SQL Server 2014:
click to enlarge
So, not exactly sure what sp_whoisactive is doing differently in this case (I've never used it to analyze statistics creation), but I can assure you that SQL Server 2014 still uses StatMan.
Best Answer
Incremental stats and filtered stats serve completely different purposes. Incremental stats are not used by the query optimizer. They are a feature designed to make maintenance easier for large tables. They can only indirectly help with query performance because they can make it more practical to keep statistics up to date on large tables.
Filtered statistics are used by the query optimizer (if you write your queries carefully enough). They technically add to database maintenance but can significantly improve query performance in some scenarios.
In summary, you should not have to worry about filtered stats and incremental stats confusing the query optimizer. If you need specific help adding filtered stats to a table please post the table DDL and explain what you're trying to do along with what you've already tried.