How to prevent statistics creation on a column?

I have a table with a column that I do not want statistics to be created or updated on. I get a better join cardinality estimate if I force the query optimizer to use density of statistics on the primary key as opposed to a statistics histogram on that column. Auto-update and auto-create statistics are on at the database level and I cannot change that.

If you want to suggest alternatives to preventing statistics creation keep in mind that the table is used in a view referenced by thousands of different queries. I do not have control over the queries that are run.

My initial strategy was to create statistics on the column with the NOCOMPUTE and SAMPLE 0 ROWS options. I was under the impression that SQL Server would not automatically create statistics on a column that already has a statistics object, but this has happened on our dev and QA servers.

New statistics were created for COL_GROUP. My NORECOMPUTE statistic was not updated. I don't know why the statistics were created and I've been unable to trigger that myself by running queries.

Is there a way to prevent SQL Server from automatically creating statistics on one column? My table only has two columns so a solution that prevents auto-stats from being created on a single table would also solve my problem.

Trace flags 4139 and 2371 are on in case it makes a difference.

If you want to play around with the table structure I've included it and sample data below:

```
CREATE TABLE X_NO_COLUMN_STATS(
[COL_USER] varchar NOT NULL,
[COL_GROUP] [int] NOT NULL,
CONSTRAINT [PK_X_NO_COLUMN_STATS] PRIMARY KEY CLUSTERED
(
[COL_USER] ASC,
[COL_GROUP] ASC
)WITH (DATA_COMPRESSION = PAGE)
);

-- prevent stats from being updated on COL_GROUP
CREATE STATISTICS [X_NO_COLUMN_STATS__COL_GROUP] ON X_NO_COLUMN_STATS ([COL_GROUP]) WITH NORECOMPUTE, SAMPLE 0 ROWS;

BEGIN TRANSACTION;
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',104);
INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',106);
INSERT INTO X_

I think the trick is to create the user-defined statistic using the same name that the equivalent auto-generated statistic would take.

In my testing, when the correctly-named user-defined Statistic exists, an auto-generated one doesn't get created.

I'm using 15.0.4102.2 with your trace flags 2371 and 4139.

Try dropping the auto-generated statistic (or starting with a new, empty table) and using this:

DECLARE @stmt nvarchar(4000);
    
SELECT @stmt = 
    N'CREATE STATISTICS '
    + QUOTENAME
        (
        N'_WA_Sys_' 
        + RIGHT(N'0000000' + CONVERT(nvarchar(11), column_id), 8) 
        + N'_' 
        + CONVERT(nchar(8), CAST([object_id] AS binary(4)), 2)
        )
    + N' ON '
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + N'.' 
    + QUOTENAME(OBJECT_NAME([object_id])) 
    + N' ('
    + QUOTENAME([name]) 
    + N') WITH NORECOMPUTE, SAMPLE 0 ROWS;'
FROM sys.columns
WHERE 
    [object_id] = OBJECT_ID(N'dbo.X_NO_COLUMN_STATS')
    AND [name] = N'COL_GROUP';

RAISERROR('%s', 0, 1, @stmt) WITH NOWAIT;
    
EXEC sp_executesql @stmt = @stmt

The [auto_created] and [user_created] flags indicate the new statistic was user-defined, and that it is not updated on data-modification:

SELECT stat.[name],
       stat.stats_id,
       stat.auto_created,
       stat.user_created,
       stat.no_recompute,
       sp.[rows],
       sp.rows_sampled,
       sp.modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties
    (stat.[object_id], stat.stats_id) AS sp
WHERE stat.[object_id] = OBJECT_ID(N'dbo.X_NO_COLUMN_STATS');

name
stats_id
auto_created
user_created
no_recompute
rows
rows_sampled
modification_counter

PK_X_NO_COLUMN_STATS
1
0
0
0
1000
1000
2000

_WA_Sys_00000002_04E4BC85
2
0
1
1
NULL
NULL
NULL

Without using the equivalent naming approach, the auto-generated statistic looks and behaves as expected:

name
stats_id
auto_created
user_created
no_recompute
rows
rows_sampled
modification_counter

PK_X_NO_COLUMN_STATS
1
0
0
0
1000
1000
2000

_WA_Sys_00000002_0E6E26BF
2
1
0
0
1000
1000
2000

StackExchange Database Administrators Q#156926, answer score: 5