World Wide DBA's questions

Estou acostumado a trabalhar em ambientes muito seguros e, por isso, projeto minhas permissões com um grau muito fino de granularidade. Uma coisa que eu normalmente faço é explicitamente DENYusuários a capacidade de UPDATEcolunas que nunca devem ser atualizadas.

Por exemplo:

create table dbo.something (
    created_by varchar(50) not null,
    created_on datetimeoffset not null
);

Essas duas colunas nunca devem ser alteradas depois que o valor for definido. Portanto, eu explicitamente DENYa UPDATEpermissão sobre eles.

Recentemente, durante uma reunião de equipe, um desenvolvedor levantou a questão de que a lógica para garantir que os campos nunca sejam atualizados deve estar contida na camada de aplicativo e não na camada de banco de dados, caso "eles precisem atualizar os valores por algum motivo". Para mim, isso soa como a mentalidade típica de um desenvolvedor (eu sei, eu costumava ser um!)

Sou o arquiteto sênior da minha empresa e sempre trabalhei com o princípio da menor quantidade de privilégios necessários para que o aplicativo funcionasse. Todas as permissões são auditadas regularmente.

Qual é a melhor prática neste cenário?

No exemplo abaixo, preciso buscar a linha que possui os dados mais recentes com base em uma combinação de datas. Não posso simplesmente fazer MAX(insert_date), MAX(update_date), pois não retorna os dados corretos. A maneira como funciona agora é obter o MAX(insert_date)then do self join para obter o MAX(update_date)then self join para retornar os valores da linha.

Existe uma maneira melhor e mais eficiente de fazer isso? O exemplo abaixo contém apenas 4 linhas, mas em produção estarei processando cerca de 1 milhão de linhas a cada poucos minutos.

Exemplo:

create table #temp (
iud char(1) not null,
id int not null,
date date not null,
value decimal(9,2) not null,
insert_date datetimeoffset not null,
update_date datetime2 not null
);

insert #temp
values
('i', 1001, '2001-01-01', 2, '2001-01-01 00:00', '2001-01-01 00:00'),
('i', 1001, '2001-01-01', 9, '2001-01-01 00:00', '2001-01-01 01:00'),
('i', 1001, '2001-01-01', 7, '2001-01-02 00:00', '2001-01-01 00:30'),
('i', 1001, '2001-01-01', 4, '2001-01-02 00:00', '2001-01-01 00:00');

-- this is wrong as it returns no results
select t.*
from #temp as t
join (select iud, id, date, max(insert_date) as insert_date, max(update_date) as update_date
      from #temp
      group by iud, id, date) as x
on t.iud = x.iud
and t.id = x.id
and t.date = x.date
and t.insert_date = x.insert_date
and t.update_date = x.update_date;

-- this works, but can it be simplified?
select n.*
from #temp as n
join (
    select n.iud, n.id, n.date, n.insert_date, max(update_date) as update_date
      from #temp as n
      join (select iud, id, date, max(insert_date) as insert_date
              from #temp
          group by iud, id, date) as i
    on i.iud = n.iud
  and i.id = n.id
  and i.insert_date = n.insert_date
group by n.iud, n.id, n.date, n.insert_date) as x
on x.date = n.date
and x.insert_date = n.insert_date
and x.iud = n.iud
and x.id = n.id
and x.update_date = n.update_date
order by n.iud, n.id, n.date;

drop table #temp;

Eu tenho a MERGEdeclaração abaixo que é emitida contra o banco de dados:

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

No entanto, isso faz com que a sessão seja encerrada com o seguinte erro:

Msg 0, Nível 11, Estado 0, Linha 67 Ocorreu um erro grave no comando atual. Os resultados, se existirem, deveriam ser descartados.

Msg 0, Level 20, State 0, Line 67 Ocorreu um erro grave no comando atual. Os resultados, se existirem, deveriam ser descartados.

Eu coloquei um pequeno script de teste que produz o erro:

USE master;
GO
IF DB_ID('TEST') IS NOT NULL
DROP DATABASE "TEST";
GO
CREATE DATABASE "TEST";
GO
USE "TEST";
GO

SET NOCOUNT ON;

IF SCHEMA_ID('MySchema') IS NULL
EXECUTE('CREATE SCHEMA "MySchema"');
GO

IF OBJECT_ID('MySchema.Region', 'U') IS NULL
CREATE TABLE "MySchema"."Region" (
"Id" TINYINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Region" PRIMARY KEY,
"Name" VARCHAR(8) NOT NULL CONSTRAINT "UK_MySchema_Region" UNIQUE
);
GO

INSERT [MySchema].[Region] ([Name]) 
VALUES (N'A'), (N'B'), (N'C'), (N'D'), (N'E'), ( N'F'), (N'G');

IF OBJECT_ID('MySchema.Location', 'U') IS NULL
CREATE TABLE "MySchema"."Location" (
"Id" SMALLINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_Location" PRIMARY KEY,
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Location_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
"Name" VARCHAR(128) NOT NULL,
CONSTRAINT "UK_MySchema_Location" UNIQUE ("Region", "Name") 
);
GO

IF OBJECT_ID('MySchema.Point', 'U') IS NULL
CREATE TABLE "MySchema"."Point" (
"ObjectId" BIGINT NOT NULL CONSTRAINT "PK_MySchema_Point" PRIMARY KEY,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL CONSTRAINT "FK_MySchema_Point_Location" FOREIGN KEY REFERENCES "MySchema"."Location"("Id"),
"Region" TINYINT NOT NULL CONSTRAINT "FK_MySchema_Point_Region" FOREIGN KEY REFERENCES "MySchema"."Region" ("Id"),
CONSTRAINT "UK_MySchema_Point" UNIQUE ("Name", "Region", "LocationId")
);
GO

-- CONTAINS HISTORIC Point DATA
IF OBJECT_ID('MySchema.PointHistory', 'U') IS NULL
CREATE TABLE "MySchema"."PointHistory" (
"Id" BIGINT IDENTITY NOT NULL CONSTRAINT "PK_MySchema_PointHistory" PRIMARY KEY,
"ObjectId" BIGINT NOT NULL,
"Name" VARCHAR(64) NOT NULL,
"LocationId" SMALLINT NULL,
"Region" TINYINT NOT NULL
);
GO

CREATE TYPE "MySchema"."PointTable" AS TABLE (
"ObjectId"      BIGINT          NOT NULL PRIMARY KEY,
"PointName"     VARCHAR(64)     NOT NULL,
"Location"      VARCHAR(16)     NULL,
"Region"        VARCHAR(8)      NOT NULL,
UNIQUE ("PointName", "Region", "Location")
);
GO

DECLARE @p1 "MySchema"."PointTable";

insert into @p1 values(10001769996,N'ABCDEFGH',N'N/A',N'E')

MERGE "MySchema"."Point" AS t
USING (
       SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region"
         FROM @p1 AS d
         JOIN "MySchema"."Region" AS i ON i."Name" = d."Region"
    LEFT JOIN "MySchema"."Location" AS z ON z."Name" = d."Location" AND z."Region" = i."Id"
       ) AS s
   ON s."ObjectId" = t."ObjectId"
 WHEN NOT MATCHED BY TARGET 
    THEN INSERT ("ObjectId", "Name", "LocationId", "Region") VALUES (s."ObjectId", s."PointName", s."LocationId", s."Region")
 WHEN MATCHED 
    THEN UPDATE 
     SET "Name" = s."PointName"
       , "LocationId" = s."LocationId"
       , "Region" = s."Region"
OUTPUT $action, inserted.*, deleted.*;

Se eu remover a OUTPUTcláusula, o erro não ocorrerá. Além disso, se eu remover a deletedreferência, o erro não ocorrerá. Então, examinei os documentos do MSDN em busca da OUTPUTcláusula que declara:

DELETED não pode ser usado com a cláusula OUTPUT na instrução INSERT.

O que faz sentido para mim, mas o ponto principal MERGEé que você pode não saber com antecedência.

Além disso, o script abaixo funciona perfeitamente, independentemente da ação executada:

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO 

Além disso, tenho outras consultas que usam o OUTPUTda mesma forma que a que está gerando um erro e funcionam perfeitamente bem - a única diferença entre elas são as tabelas que fazem parte do MERGE.

Isso está causando grandes problemas na produção para nós. Reproduzi esse erro no SQL2014 e no SQL2016 tanto na VM quanto no físico com 128 GB de RAM, 12 núcleos de 2,2 GHZ, Windows Server 2012 R2.

O plano de execução estimado gerado a partir da consulta pode ser encontrado aqui:

Plano de Execução Estimado

Dado um elemento, definido dentro de uma coleção de esquema XML como tal:

<xsd:element name="xid">
    <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="32" />
        </xsd:restriction>
    </xsd:simpleType>
</xsd:element>

Como você atualizaria o elemento usando XQuery?

O elemento é encontrado no namespace ns na coleção de esquemas. Eu tenho tentado atualizar o elemento da consulta abaixo:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com"; 
     replace value of (/ns:*/ns:xid)[1] with "X00011793" cast as element(ns{http://www.anon.com}:xid,#anonymous) ?') 
 where id = 11793

mas isso gera o seguinte erro:

Msg 9301, Nível 16, Estado 1, Linha 2 XQuery [cm.item.data.modify()]: Nesta versão do servidor, 'cast as' não está disponível. Por favor, use o 'transmitir como?' sintaxe.

Se eu remover totalmente o elenco e usar esta consulta:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com"; 
     replace value of (/ns:*/ns:xid)[1] with "X00011793"') 
 where id = 11793

Eu recebo este erro:

Msg 2247, Nível 16, Estado 1, Linha 2 XQuery [cm.item.data.modify()]: O valor é do tipo "xs:string", que não é um subtipo do tipo esperado "<anônimo>".

Se eu emitir esta consulta:

update cm.item
   set data.modify(
      'declare namespace ns="http://www.anon.com/"; 
       replace value of (/ns:*/ns:xid/text())[1] with "X00011793"')
 where id = 11793

Eu recebo este erro:

Msg 9312, Nível 16, Estado 1, Linha 2 XQuery [cm.item.data.modify()]: 'text()' não é compatível com digitação simples ou ' http://www.w3.org/2001/XMLSchema #anyType 'elements, found'(element(ns{ http://www.anon.com/ }:xid,#anonymous) ?) *'.

Estou visando o SQL Server 2008 R2.

Obrigado!

Tenho dois clusters cada um com dois nós configurados em ativo/passivo; os dois clusters participam de uma sessão de espelhamento de alta disponibilidade usando um servidor separado como testemunha.

Quando um nó falha no cluster primário, o failover de espelhamento parece entrar em ação antes que o nó secundário do cluster primário tenha tempo de ficar online.

Existe alguma maneira de dizer ao servidor testemunha para permitir que o nó passivo tente ficar online antes de iniciar o failover para o cluster secundário?