The Heap™ – Consultancy ©®

« first day (4042 days earlier) ← previous day next day → last day (816 days later) »

5:46 AM

Yep they're very good at anticipating user reactions

6:09 AM

Hi, @PaulWhite Just read your excellent blog post on computed columns.
https://sqlperformance.com/2017/05/sql-plan/properly-persisted-computed-columns
Found an issue in our code where ansi_warnings off caused matching expressions to fail and hinting the index does not produce a plan at all.
I have tried to read up on ansi_warnings but found nothing about that behavior. On the other hand, why bother it should be on.
Just thought it was interesting so perhaps some other do aswell :).

Paul White

@MikaelEriksson Isn't that documented as one of the group of settings that needs to be a certain way to get indexed view/computed column matching?

Mikael Eriksson

Perhaps, I found ansi_warnings needs to be on to be able to create the index. Don't think I have seen anything about it being used.

create table T
(
  C1 varchar(10) not null,
  C2 as C1+'1', --persisted,
  index IX_T_C2 nonclustered (C2)
);

go

set ansi_warnings off;

select count(*)
from T with (index(IX_T_C2))
where C2 = ''
--option (querytraceon 176);

set ansi_warnings on;

select count(*)
from T
where C2 = '';

It does early expansion and the does not come back from that.

Paul White

@MikaelEriksson docs.microsoft.com/en-us/sql/relational-databases/indexes/…

Mikael Eriksson

It works with 176 and persisted.

Paul White

Well yeah because it is never expanded to begin with

As I recall, the settings requirements are there to prevent a plan cache explosion rather than for correctness

Mikael Eriksson

6:14 AM

There it is "The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings."

Paul White

Yep

Mikael Eriksson

They where doing a count(distinct) on a left outer join column and the issue they tried to fix with ansi_warnings is that the client code does not do well with warnings.

Paul White

Perfect

Mikael Eriksson

Sadly, it is the number one advice on the internet on how to fix such issue.

Paul White

7:06 AM

Can't trust random people on the internet

John K. N.

7:20 AM

Morning

McNets

7:40 AM

Morning

2 hours later…

Master Database

9:29 AM

Q: Why isn't AWS's sponsoring being shown on Database Administrators Meta?

Has this been just forgotten or is it intentional? My thoughts are that if you sponsor the site, then it should be shown on the Meta site as well. See more at: AWS will soon be sponsoring Database Administrators