A Not So Silent Bug With Filtered Indexes On Computed Columns In SQL Server

Bugbash


At some point in the past, I blogged about a silent bug with computed columns and clustered column store indexes.

In this post, I’m going to take a quick look at a very loud bug.

Normally, you can’t add a filtered index to a computed column. I’ve always hated that limitation. How nice would that be for so many currently difficult tasks?

I wonder if Oracle…

You Can’t Do That On Television


If you run this code to completion — and boy are you missing out if you don’t — you get an error.

CREATE TABLE dbo.ohno
(
    id int NOT NULL, 
    crap AS id * 2
);
GO 

CREATE NONCLUSTERED INDEX c 
ON dbo.ohno
    (crap) 
WHERE crap > 0;
GO

Here’s the error:

Msg 10609, Level 16, State 1, Line 13
Filtered index 'c' cannot be created on table 'dbo.ohno' because the column 'crap' in the filter expression is a computed column. 
Rewrite the filter expression so that it does not include this column.

Okay, great! Works on my machine.

Kapow, Kapow


However, you can totally create this table using the inline index creation syntax.

CREATE TABLE dbo.ohyeah
(
    id int NOT NULL, 
    crap AS id * 2,
    INDEX c 
        (crap) 
    WHERE crap > 0
);
GO

However,  if you try to query the table, you’re met with a buncha errors.

SELECT
    id
FROM dbo.ohyeah AS o;

SELECT
c = COUNT_BIG(*)
FROM dbo.ohyeah AS o;

Even without explicitly referencing the computed column, you get this error message.

Msg 207, Level 16, State 1, Procedure ohyeah, Line 1 [Batch Start Line 29]
Invalid column name 'crap'.
Msg 207, Level 16, State 1, Procedure ohyeah, Line 1 [Batch Start Line 29]
Invalid column name 'crap'.
Msg 207, Level 16, State 1, Procedure ohyeah, Line 1 [Batch Start Line 29]
Invalid column name 'crap'.
Msg 207, Level 16, State 1, Procedure ohyeah, Line 1 [Batch Start Line 29]
Invalid column name 'crap'.
Msg 207, Level 16, State 1, Procedure ohyeah, Line 1 [Batch Start Line 29]
Invalid column name 'crap'.
Msg 4184, Level 16, State 2, Line 30
Cannot retrieve table data for the query operation because the table "dbo.ohyeah" schema is being altered too frequently. 
Because the table "dbo.ohyeah" contains a filtered index or filtered statistics, changes to the table schema require a refresh of all table data. 
Retry the query operation, and if the problem persists, use SQL Server Profiler to identify what schema-altering operations are occurring.

You see what? See you what that error message says? “[I[f the problem persists, use SQL Server Profiler to identify what schema-altering operations are occurring”

Use SQL Server Profiler.

Take that, Extended Events.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

A Silent Bug With Clustered Column Store Indexes And Computed Columns In SQL Server 2019

Oh Boy


This bug has been reported to the proper authorities, and this blog post is not to dunk on the Summer Intern responsible for column store indexes, but to warn you to not do this, because I don’t know what it will break under the covers.

If you read the documentation for column store indexes, it says that column store indexes can’t be created on persisted computed columns.

And that’s true. If we step through this script, creating the column store index will fail.

/*Bye Felicia*/
DROP TABLE IF EXISTS dbo.cci;
GO 

/*Hi Felicia*/
CREATE TABLE dbo.cci(id int, fk int, sd date, ed datetime);
GO 

/*COMPUTER!*/
ALTER TABLE dbo.cci ADD cc AS id * 2;
ALTER TABLE dbo.cci ALTER COLUMN cc ADD PERSISTED;
GO 

/*Rats*/
CREATE CLUSTERED COLUMNSTORE INDEX c ON dbo.cci;
GO

The error message we get is pretty clear:

Msg 35307, Level 16, State 1, Line 76
The statement failed because column 'cc' on table 'cci' is a computed column. 
Columnstore index cannot include a computed column implicitly or explicitly.

80 Blocks from Tiffany’s


However, if we change the order of things a little bit, we’ll find that we can add a persisted computed column to a table with a clustered column store index on it.

/*Bye Felicia*/
DROP TABLE IF EXISTS dbo.cci;
GO 

/*Hi Felicia*/
CREATE TABLE dbo.cci(id int, fk int, sd date, ed datetime, INDEX c CLUSTERED COLUMNSTORE);
GO 

/*???*/
ALTER TABLE dbo.cci ADD cc AS id * 2;
ALTER TABLE dbo.cci ALTER COLUMN cc ADD PERSISTED;
GO 

/*With data?*/
INSERT dbo.cci
(
    id, fk, sd, ed
)
VALUES
(
    10, 10, GETDATE(), GETDATE()  
);

/*yepyepyepyepyepyep*/
SELECT
    c.*
FROM dbo.cci AS c

/*Eh?*/
ALTER INDEX c ON dbo.cci REBUILD;
ALTER INDEX c ON dbo.cci REORGANIZE;
DBCC CHECKTABLE('dbo.cci');

And everything seems to work. However, if we drop the column store index, it can’t be re-created.

The key here is that the computed column is added, and then the persisted attribute is added second. That is where an error should be thrown.

But what do I know? I’m just a bouncer.

Bomber


Again, I’m telling you not to do this. I’m telling you that it might break things in gruesome and horrible ways.

I don’t think that this is a preview of upcoming support for persisted computed columns in clustered column store indexes.

Thanks for reading!

p.s. Really, don’t do this

p.p.s. If you do this you’re asking for trouble

p.p.p.s. This was fixed in CU12

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

A Bug With OBJECTPROPERTYEX And Column Store Indexes

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


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, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

CHECKDB Bug With SQL Server 2017 And ColumnStore Indexes

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Thanks for watching!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

A SQL Server Query Plan Memory Grant Bug?

Yes, That’s A Question


I have no idea if this is a bug or not, but I thought it was interesting. Looking at information added to spills in SQL Server 2016

SQL Server Query Plan

If you open the linked-to picture, you’ll see (hopefully) that the full memory grant for the query was 108,000KB.

But the spill on the Sort operator lists a far larger grant: 529,234,432KB.

This is in the XML, and not an artifact of Plan Explorer.

Whaddya think, Good Lookings? Should I file a bug report?

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.