Does Persisting sampling rate work on Databases with compatibility levels below 130?

Short answer: Yes, it does. Long answer:

What is persisting sampling rate and why does it exist?

When you create or update statistics (without specifying a sample rate) the sample rate is automatically calculated by SQL Server.

If you would want this statistic (or the whole table) to use a Flat sample rate, say 5 percent, then you could specify a persisting sample rate after updating your stats.

 UPDATE STATISTICS dbo.test2 WITH SAMPLE 5 PERCENT, PERSIST_SAMPLE_PERCENT = ON 
UPDATE STATISTICS dbo.test2 WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON 
(I would not advise changing this to fullscan on big tables) 

From Microsoft:

Applies to: SQL Server 2016 (13.x) (starting with SQL Server 2016 (13.x) SP1 CU4) through SQL Server 2017 (starting with SQL Server 2017 (14.x) CU1).

(https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017)

So, what if we are on an instance of sql server 2016 or higher with a database with a lower compatibility level than 2016?

In my example, I used the 110 compatibility level (sql server 2012)

 
select compatibility_level from sys.databases where name = 'test';

Create the table and index

 create table dbo.test2 (id int identity(1,1),
 value varchar(50));
create index IX_value on dbo.test2(value);

Insert the values

set nocount on;
insert into dbo.test2 VALUES ('BlaBLa');
go 5000
Result:
Beginning execution loop
Batch execution completed 5000 times.

Some more inserts ( we need a bigger dataset so that the sample rate is no longer 100%)

insert into dbo.test2 
select value from dbo.test2;
go 7
Beginning execution loop
Batch execution completed 7 times.
Result: 640 000 Rows

Run a ‘complex’ query so no trivial plan is used and the stats are updated.

 select a.value from dbo.test2  a
inner join dbo.test2 ac 
on a.id = ac.id where a.value = 'BlaBLa' or a.value = 1;

Get the stats default sample percent

 --Check the statistics
SELECT  ss.name, persisted_sample_percent,
    (rows_sampled * 100)/rows AS sample_percent
FROM sys.stats ss
INNER JOIN sys.stats_columns sc 
    ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac 
    ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
WHERE ss.[object_id] = OBJECT_ID('[dbo].[test2]');

An extra check with DBCC SHOW STATISTICS

DBCC SHOW_STATISTICS(test2,IX_value)

As expected, we see no persisted sample percent, and a 60% Sample rate.

We rerun the commands after clearing the table and change the sample rate to fullscan (100%)

truncate table dbo.test2

set nocount on;
insert into dbo.test2 VALUES ('BlaBLa')
go 5000

UPDATE STATISTICS dbo.test2 WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON

insert into dbo.test2 
select value from dbo.test2 
go 7

select a.value from dbo.test2  a 
inner join dbo.test2 ac on a.id = ac.id where a.value = 'BlaBLa' or a.value = 1

We check the stats again

--Check the statistics
SELECT  ss.name, persisted_sample_percent,
    (rows_sampled * 100)/rows AS sample_percent
FROM sys.stats ss
INNER JOIN sys.stats_columns sc 
    ON ss.stats_id = sc.stats_id AND ss.object_id = sc.object_id
INNER JOIN sys.all_columns ac 
    ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(ss.object_id, ss.stats_id) shr
WHERE ss.[object_id] = OBJECT_ID('[dbo].[test2]');

An extra check with DBCC SHOW STATISTICS

DBCC SHOW_STATISTICS(test2,IX_value)

A test with sample rate of 70%:

UPDATE STATISTICS dbo.test2 WITH sample  70 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
(redo filling of tables after)

A test with sample rate of 80%:

UPDATE STATISTICS dbo.test2 WITH sample  80 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
(redo filling of tables after)

However, you cannot got below the sample rate that SQL Server chooses (same for compatibility level 140).

UPDATE STATISTICS dbo.test2 WITH sample  1 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
(this is the same on compat 140).

In short, it still works, I tried it with compatibility level 100 aswell, which also works.

Source used:

https://blogs.msdn.microsoft.com/sql_server_team/persisting-statistics-sampling-rate/