How to prevent statistics creation on a column?
sql-server add tag
Joe Obbish (imported from SE) 
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](256) 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_NO_COLUMN_STATS VALUES ('CUSER1',107);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',108);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',110);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',111);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',112);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',113);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',114);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',116);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',117);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',118);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',121);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',123);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',124);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',125);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',126);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',129);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',132);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',137);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',139);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',140);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',144);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',145);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',147);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',152);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',153);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',154);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',155);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',162);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',163);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',165);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',168);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',169);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',170);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('CUSER1',178);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',102);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',103);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',109);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',110);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',111);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',112);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',114);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',115);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',119);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',120);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',121);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',123);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',124);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',126);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',128);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',136);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',137);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',138);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',142);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',143);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',148);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',151);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',152);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',155);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',156);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',157);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',158);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',165);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',167);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',168);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',169);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',171);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',173);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',176);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',177);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('OUSER19',178);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',104);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',108);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',109);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',111);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',112);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',113);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',114);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',116);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',117);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',118);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',121);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',123);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',124);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',125);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',126);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',129);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',132);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',137);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',139);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',140);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',144);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',145);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',147);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',152);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',154);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',155);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',162);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',163);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',165);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',168);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',169);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',170);
    INSERT INTO X_NO_COLUMN_STATS VALUES ('TUSER30',178);
    COMMIT TRANSACTION;

I know about `sp_autostats` but I need something to prevent the creation of automatic statistics. `sp_autostats` only prevents the statistics from being auto-updated.
Top Answer
Rob Dalzell (imported from SE) 
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:

```sql
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**|
Answer #2
Denis Reznik (imported from SE) 
Create а new database (let's call it TestStats), disable Auto Create Statistics on and move the X_NO_COLUMN_STATS table there. After this create a view in your database which will point to the table without histogram:

    CREATE VIEW X_NO_COLUMN_STATS
    AS
    	SELECT [COL_USER], [COL_GROUP] FROM TestStats.dbo.X_NO_COLUMN_STATS;
    GO

If I got your problem correctly at this point you will achieve what you want. Your CRUD operations will work with a table without statistics (yes, it will be located in another database, and this should be kept in mind always) through the view with the same name as your table was.
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.