« first day (4042 days earlier)      last day (119 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 :).
 
@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?
 
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.
 
It works with 176 and persisted.
 
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
 
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."
 
Yep
 
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.
 
Perfect
 
Sadly, it is the number one advice on the internet on how to fix such issue.
 
7:06 AM
Can't trust random people on the internet
4
 
7:20 AM
Morning
 
7:40 AM
Morning
 
 
2 hours later…
9:29 AM
0
Q: Why isn't AWS's sponsoring being shown on Database Administrators Meta?

SnostorpHas 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

 

« first day (4042 days earlier)      last day (119 days later) »