Charlieface's questions

Dadas as duas tabelas a seguir:

CREATE TABLE SalesLedger (
  Id int PRIMARY KEY IDENTITY,
  Date date NOT NULL,
  Total decimal(38,18),
  INDEX IX (Date, Total)
);

CREATE TABLE Purchases (
  Id int PRIMARY KEY IDENTITY,
  Date date NOT NULL,
  Total decimal(38,18),
  INDEX IX (Date, Total)
);

E a seguinte visão

CREATE VIEW ViewMetrics
AS

Select
  Date,
  'Sale' as Metric,
  Total as Value
From SalesLedger

UNION ALL

Select
  Date,
  'Purchase' as Metric,
  Total as Value
From Purchases;

A consulta a seguir usa um Concatenation Sortpar:

Select SUM(Value) as Sales, Date
from ViewMetrics
Group By Date;

Colar o plano

Considerando que uma pequena reescrita dá obviamente mais desempenhoMerge Concatenation

SELECT SUM(Sales), Date
FROM (
  Select SUM(Value) as Sales, Date
  from ViewMetrics
  Group By Metric, Date
) t
GROUP BY Date;

Colar o plano

O compilador pode ver claramente que a visualização está particionada por Metric, como mostra esta consulta, não Sorté necessário:

Select SUM(Value) as Sales, Date
from ViewMetrics
where Metric = 'Sale'
Group By Date;

A questão é: por que a primeira consulta força a Sort, enquanto a segunda pode usar uma mais eficiente Merge Concatenation, visto que a Metriccoluna não possui WHEREpredicado em nenhum dos casos?

O compilador não deveria ser capaz de ver que a Mergefuncionaria, visto que os índices já estão classificados Datee o particionamento está ativado Metric? Ou se não consegue ver isso, por que GROUP BY Metric, Datede repente lhe dá essa habilidade?

banco de dados<> violino

Ainda mais estranho, como descobriu @MartinSmith, se não houver dados, o compilador usará o plano melhor, embora sem uma agregação intermediária em Metric, Date. db<>fiddle Por outro lado, a mesclagem sem agregação parcial é provavelmente mais lenta do que a classificação após a agregação parcial, porque há mais linhas para mesclar. A questão é por que não é possível fazer agregação parcial e mesclar ao mesmo tempo por padrão?

Suponho que haja alguma otimização específica para uma visualização particionada quando a agregação inclui o particionamento, porque nesse caso ela usa uma concatenação e quando uma ordem é necessária, ela usa a concatenação de mesclagem, consulte db<> fiddle . Isso ajuda quando você deseja agregar ainda mais, pois os dados agora já estão classificados na ordem correta. Mas se você não fizer a agregação intermediária não tem lógica que a aplique.

TL;DR; É um bug que o SQL Server permite que uma UDF escalar chame a si mesma recursivamente quando vinculada ao esquema, mas somente quando alterada para fazer isso usando a CREATE OR ALTERsintaxe? Ou é um bug que outras sintaxes não são permitidas?

Uma UDF escalar recursiva trivial pode ser construída da seguinte maneira

CREATE FUNCTION dbo.Try1 (@i int)
RETURNS int
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try1(@i - 1));
END;

Contanto que isso não seja vinculado ao esquema, isso é permitido.

Vamos tentar vinculá-lo ao esquema, faremos

CREATE FUNCTION dbo.Try2 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try2(@i - 1));
END;

Não.

Cannot find either column "dbo" or the user-defined function
or aggregate "dbo.Try2", or the name is ambiguous

Crie-o sem recursão e altere-o

CREATE OR ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

ALTER FUNCTION dbo.Try3 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try3(@i - 1));
END;

Um erro diferente desta vez:

Cannot schema bind function 'dbo.Try3' because name 'dbo.Try3' is invalid
for schema binding. Names must be in two-part format
and an object cannot reference itself.

Hmmm, an object cannot reference itselfquem inventou essa regra? Não está nos documentos.

Vamos tentar comCREATE OR ALTER

CREATE OR ALTER FUNCTION dbo.Try4 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try4(@i - 1));
END;

Ainda não.

Mas se primeiro fizermos CREATE sem recursão, depois CREATE OR ALTER( não ALTER ) com recursão, funcionará

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN RETURN NULL; END;

CREATE OR ALTER FUNCTION dbo.Try5 (@i int)
RETURNS int
WITH SCHEMABINDING
AS BEGIN
  RETURN IIF(@i = 0, 0, @i + dbo.Try5(@i - 1));
END;

O estranho é: isso não faz sentido, seja qual for a maneira que CREATE OR ALTERfuncione por baixo. Se ele realmente cair e recriar, por que não recebemos o primeiro erro? E se alterar, então por que esta última opção funciona?

A parte mais engraçada: se o criarmos sem vinculação de esquema primeiro, então CREATE OR ALTER WITH SCHEMABINDINGfaremos

Cannot schema bind function 'dbo.Try6'. 'dbo.Try6' is not schema bound.

O que é um absurdo total.

db<>violino

TL;DR;

Dada uma exibição indexada que tem JOINentre duas tabelas que já possuem um relacionamento de chave estrangeira junto com um WHEREpredicado na tabela pai.
Ao inserir na tabela pai da chave estrangeira, o compilador está adicionando manutenção de índice ao plano, mesmo que seja provável que não existam linhas correspondentes.

Isso é um bug ou talvez uma otimização perdida? Ou existe alguma falácia lógica ou algébrica em que estou?

Configurar

CREATE TABLE Parent (Id int identity primary key, SomeCol bit not null, OtherCol int not null);

CREATE TABLE Child (Id int identity primary key, ParentId int not null references Parent (Id) INDEX IX_Parent NONCLUSTERED);

CREATE VIEW dbo.vChild
WITH SCHEMABINDING
AS
SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
-- WHERE p.SomeCol = 0;  -- problem dependent on this line

CREATE UNIQUE CLUSTERED INDEX CX_vChild ON vChild (Id)

db<>fiddle com WHERE
db<>fiddle sem WHERE

Neste estágio, qualquer UPDATEcoluna que afete na visão, e qualquer DELETE, das tabelas da visão acionam, com razão, a manutenção da visão. O compilador pegará as linhas modificadas, as colocará em spool e as alimentará através das junções da visão, enviando quaisquer resultados para o índice da visão.

O mesmo pode ser dito para inserções para Child, porque uma linha Parentjá pode existir para (que se qualifica em relação a WHERE) e, portanto, a nova Childlinha pode se qualificar para a junção.

Problema

Ao inserir em Parent, é provável que a manutenção do índice não precise ser feita. Uma linha correspondente ainda não pode existir Childdevido ao relacionamento de chave estrangeira, portanto, não pode haver linhas da inserção que se qualifiquem para a exibição.

Se você executar o script a seguir, verá que nenhuma manutenção de exibição é feita.

INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100);

PasteThePlan

Muito claramente, o compilador pode deduzir que a manutenção da visão não é necessária aqui.

No entanto, se você descomentar a linha WHERE p.SomeCol = 0na definição de exibição, de repente você obterá manutenção de exibição. Portanto, a adição de outra coluna à exibição, que não é uma coluna de junção e não possui um relacionamento de chave estrangeira, causa isso. Isso ocorre apesar da mesma lógica relacional aplicada: a inserção provavelmente ainda não se qualifica para a exibição, devido à coluna de chave estrangeira que ainda está lá.

PasteThePlan

Curiosamente, o compilador ainda pode reconhecer alguns casos em que a inserção não se qualificará para a exibição (e apesar desse exemplo específico ser parametrizado automaticamente).

Aqui o compilador reconhece que SomeColfalha o WHERE, e não há necessidade de fazer manutenção de índice.

INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100);

PasteThePlan