This is a question that came up at the Cleveland SQL Server User Group last night. An attendee had a wager with his colleagues about how the names of auto-created column-level stats are generated.

Let's find out. I'm going to create a table without any indexes and then do some queries.

USE master;
GO
CREATE DATABASE StatsTest;
GO
USE StatsTest;
GO

EXEC sp_dboption 'StatsTest', 'auto create statistics', 'on';
GO

CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
GO
INSERT INTO t1 VALUES (1, 1, 1);
INSERT INTO t1 VALUES (1, 1, 2);
INSERT INTO t1 VALUES (1, 1, 3);
GO

Now I'll do a little query. My database has AUTO_CREATE_STATISTICS turned out, so the query optimizer recognizes that it could use a statistic to potentially help choose a more efficient query plan more quickly – so it generates it.

SELECT * FROM t1 WHERE c3 < 2;
GO

SELECT [object_id], [name] FROM sys.stats
WHERE [object_id] = OBJECT_ID ('t1');
GO

object_id   name
———– ————————–
2073058421  _WA_Sys_00000003_7B905C75

I've got an automatically created statistic – but what does the name mean? Well, the 7B905C75 is the hex equivalent of the table's object ID. The Sys is because it was automatically created. The WA is because the SQL team is based in Washington State in the US (I'm absolutely serious!). The 00000003 is because the statistic is on column ID 3 of the table. I can prove this last one by doing a select on column ID 1 and checking the new statistic that is created.

SELECT * FROM t1 WHERE c1 < 2;
GO

SELECT [object_id], [name] FROM sys.stats
WHERE [object_id] = OBJECT_ID ('t1');
GO

object_id   name
———– ————————–
2073058421  _WA_Sys_00000003_7B905C75
2073058421  _WA_Sys_00000001_7B905C75

The new statistic has the exact same name as the previous one, but the first number is now 00000001 – clearly matching the column ID of the column I queried.

So there you go – wager settled!