A Bug With OBJECTPROPERTYEX And Column Store Indexes

Posted on by Erik Darling

No, Not That Bug

There’s a known bug with partitioned tables, but this is different. This one is with rows in the delta store.

Here’s a quick repro:

USE tempdb;

DROP TABLE IF EXISTS dbo.busted;

CREATE TABLE dbo.busted ( id BIGINT, INDEX c CLUSTERED COLUMNSTORE );

INSERT INTO dbo.busted WITH ( TABLOCK )
SELECT     TOP ( 50000 )
           1
FROM       master..spt_values AS t1
CROSS JOIN master..spt_values AS t2
OPTION ( MAXDOP 1 );

-- reports 0, should be 50k
SELECT CAST(OBJECTPROPERTYEX(OBJECT_ID('dbo.busted'), N'Cardinality') AS BIGINT) AS [where_am_i?];
SELECT COUNT_BIG(*) AS records
FROM   dbo.busted;

INSERT INTO dbo.busted WITH ( TABLOCK )
SELECT     TOP ( 150000 )
           1
FROM       master..spt_values AS t1
CROSS JOIN master..spt_values AS t2
OPTION ( MAXDOP 1 );

-- reports 150k, should be 200k
SELECT CAST(OBJECTPROPERTYEX(OBJECT_ID('dbo.busted'), N'Cardinality') AS BIGINT) AS [where_am_i?];
SELECT COUNT_BIG(*) AS records
FROM   dbo.busted;

SELECT object_NAME(csrg.object_id) AS table_name, *
FROM sys.column_store_row_groups AS csrg
ORDER BY csrg.total_rows;

In Pictures

2019 09 05 17 26 10
In Particular

This is the interesting bit, because you can obviously see the difference between open and compressed row groups.

SQL Server Query Results
Give us money

The 50k rows in the delta store aren’t counted towards table cardinality.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.

Related Posts