All perguntas(dba)

Eu tenho uma tabela grande com 7,5 bilhões de linhas e 5 índices. Quando excluo aproximadamente 10 milhões de linhas, percebo que os índices não clusterizados parecem aumentar o número de páginas em que estão armazenados.

Eu escrevi uma consulta dm_db_partition_statspara relatar a diferença (depois - antes) nas páginas:

O índice 1 é o índice clusterizado, o índice 2 é a chave primária. Os outros são não agrupados e não exclusivos.

Por que as páginas estão aumentando nesses índices não agrupados?
Eu esperava que os números, na pior das hipóteses, permanecessem os mesmos.
Vejo que os contadores de desempenho relatam um aumento nas divisões de página durante a exclusão.

Ao excluir, o registro fantasma precisa ser movido para outra página? Isso tem a ver com "singulares"?

Estamos no meio do lançamento do RCSI, mas agora o RCSI está desativado.

É um nó primário em um grupo de disponibilidade. Eu sei que o instantâneo é usado de alguma forma em secundários. Eu ficaria surpreso se isso fosse relevante. Eu pretendo investigar isso (procurando a saída da página dbcc) para saber mais. Aqui está esperando que alguém tenha visto algo semelhante.

Encontramos um problema interessante com o SQL Server. Considere o seguinte exemplo de reprodução:

CREATE TABLE #test (s_guid uniqueidentifier PRIMARY KEY);
INSERT INTO #test (s_guid) VALUES ('7E28EFF8-A80A-45E4-BFE0-C13989D69618');

SELECT s_guid FROM #test
WHERE s_guid = '7E28EFF8-A80A-45E4-BFE0-C13989D69618'
  AND s_guid <> NEWID();

DROP TABLE #test;

violino

Por favor, esqueça por um momento que a s_guid <> NEWID()condição parece totalmente inútil - este é apenas um exemplo mínimo de reprodução. Como a probabilidade de NEWID()corresponder a um determinado valor constante é extremamente pequena, ela deve ser avaliada como TRUE todas as vezes.

Mas não. A execução dessa consulta geralmente retorna 1 linha, mas às vezes (com bastante frequência, mais de 1 vez em 10) retorna 0 linhas. Eu o reproduzi com o SQL Server 2008 no meu sistema e você pode reproduzi-lo on-line com o violino vinculado acima (SQL Server 2014).

Observar o plano de execução revela que o analisador de consultas aparentemente divide a condição em s_guid < NEWID() OR s_guid > NEWID():

...o que explica completamente por que às vezes falha (se o primeiro ID gerado for menor e o segundo maior que o ID fornecido).

O SQL Server tem permissão para avaliar A <> Bcomo A < B OR A > B, mesmo se uma das expressões não for determinística? Se sim, onde está documentado? Ou encontramos um bug?

Curiosamente, AND NOT (s_guid = NEWID())produz o mesmo plano de execução (e o mesmo resultado aleatório).

Encontramos esse problema quando um desenvolvedor queria excluir opcionalmente uma linha específica e usou:

s_guid <> ISNULL(@someParameter, NEWID())

como um "atalho" para:

(@someParameter IS NULL OR s_guid <> @someParameter)

Estou procurando documentação e/ou confirmação de um bug. O código não é tão relevante, portanto, não são necessárias soluções alternativas.

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?

Como posso eliminar um operador Key Lookup (Clustered) em meu plano de execução?

A tabela tblQuotesjá tem um índice clusterizado (on QuoteID) e 27 índices não clusterizados, então estou tentando não criar mais.

Coloquei a coluna de índice clusterizado QuoteIDna minha consulta, esperando que ajude - mas infelizmente ainda é o mesmo.

Plano de execução aqui .

Ou veja:

Isto é o que o operador Key Lookup diz:

Consulta:

declare
        @EffDateFrom datetime ='2017-02-01',
        @EffDateTo   datetime ='2017-08-28'

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
    DROP TABLE #Data 
CREATE TABLE #Data
(
    QuoteID int NOT NULL,   --clustered index

    [EffectiveDate] [datetime] NULL, --not indexed
    [Submitted] [int] NULL,
    [Quoted] [int] NULL,
    [Bound] [int] NULL,
    [Exonerated] [int] NULL,
    [ProducerLocationId] [int] NULL,
    [ProducerName] [varchar](300) NULL,
    [BusinessType] [varchar](50) NULL,
    [DisplayStatus] [varchar](50) NULL,
    [Agent] [varchar] (50) NULL,
    [ProducerContactGuid] uniqueidentifier NULL
)
INSERT INTO #Data
    SELECT 
        tblQuotes.QuoteID,

          tblQuotes.EffectiveDate,
          CASE WHEN lstQuoteStatus.QuoteStatusID >= 1   THEN 1 ELSE 0 END AS Submitted,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 2 or lstQuoteStatus.QuoteStatusID = 3 or lstQuoteStatus.QuoteStatusID = 202 THEN 1 ELSE 0 END AS Quoted,
          CASE WHEN lstQuoteStatus.Bound = 1 THEN 1 ELSE 0 END AS Bound,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 3 THEN 1 ELSE 0 END AS Exonareted,
          tblQuotes.ProducerLocationID,
          P.Name + ' / '+ P.City as [ProducerName], 
        CASE WHEN tblQuotes.PolicyTypeID = 1 THEN 'New Business' 
             WHEN tblQuotes.PolicyTypeID = 3 THEN 'Rewrite'
             END AS BusinessType,
        tblQuotes.DisplayStatus,
        tblProducerContacts.FName +' '+ tblProducerContacts.LName as Agent,
        tblProducerContacts.ProducerContactGUID
FROM    tblQuotes 
            INNER JOIN lstQuoteStatus 
                on tblQuotes.QuoteStatusID=lstQuoteStatus.QuoteStatusID
            INNER JOIN tblProducerLocations P 
                On P.ProducerLocationID=tblQuotes.ProducerLocationID
            INNER JOIN tblProducerContacts 
                ON dbo.tblQuotes.ProducerContactGuid = tblProducerContacts.ProducerContactGUID

WHERE   DATEDIFF(D,@EffDateFrom,tblQuotes.EffectiveDate)>=0 AND DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <=0
        AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'--Surety
        AND tblQuotes.OriginalQuoteGUID is null

select * from #Data

Plano de execução:

Em inglês, podemos falar sobre a relação entre, digamos, Bob e Tim. Talvez sejam primos. O termo "relação" neste contexto faz sentido para mim.

No contexto de bancos de dados relacionais, entendo a que o termo se refere, mas não entendo por que ele é usado. Acho que entender por que ele é usado me ajudará a entender melhor o campo, então gostaria de entender por que ele é usado.

• Por que, por exemplo, uma Pessoa é considerada uma "relação"? Em inglês, uma relação é um substantivo que descreve como duas entidades estão associadas. Não se refere às próprias entidades. No contexto de bancos de dados relacionais, "relação" refere-se às próprias entidades. Por quê?
• Entendo que o modelo relacional veio depois dos modelos hierárquico e de rede (ex. pai, vizinho). Mas nesses modelos, as entidades também têm relações entre si. Então, por que chamar esse modelo de modelo relacional? Existe uma frase/termo mais específico? Ou talvez devêssemos dizer que todos os três modelos são modelos relacionais, mas os modelos hierárquicos e de rede são tipos específicos de modelos relacionais?
• E se tivermos entidades independentes que não se relacionam umas com as outras. Diga, Pessoa, Porta e Árvore. O termo "relation(al)" ainda é aplicável?

(Talvez isso devesse ser várias perguntas. Achei que as respostas estão altamente relacionadas - talvez haja apenas uma resposta - então achei que faria sentido para isso ser uma única pergunta. Se eu estiver errado, me avise e eu 'vai criar perguntas separadas.)

Edit: Este diagrama pode ser útil para visualizar que uma relação está relacionando diferentes domínios entre si:

Considere a seguinte consulta artificial, mas simples:

SELECT 
  ID
, CASE
    WHEN ID <> 0 
    THEN (SELECT TOP 1 ID FROM X_OTHER_TABLE) 
    ELSE (SELECT TOP 1 ID FROM X_OTHER_TABLE_2) 
  END AS ID2
FROM X_HEAP;

Eu esperaria que a estimativa de linha final para essa consulta fosse igual ao número de linhas na X_HEAPtabela. O que quer que eu esteja fazendo na subconsulta não deve importar para a estimativa de linha porque ela não pode filtrar nenhuma linha. No entanto, no SQL Server 2016, vejo a estimativa de linha reduzida para 1 por causa da subconsulta:

Por que isso acontece? O que posso fazer sobre isso?

É muito fácil reproduzir esse problema com a sintaxe correta. Aqui está um conjunto de definições de tabela que farão isso:

CREATE TABLE dbo.X_HEAP (ID INT NOT NULL)
CREATE TABLE dbo.X_OTHER_TABLE (ID INT NOT NULL);
CREATE TABLE dbo.X_OTHER_TABLE_2 (ID INT NOT NULL);

INSERT INTO dbo.X_HEAP WITH (TABLOCK)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values;

CREATE STATISTICS X_HEAP__ID ON X_HEAP (ID) WITH FULLSCAN;

link do violino db .

Estou usando o PostgreSQL como meu banco de dados. E eu preciso criar uma entrada no banco de dados, e caso já exista, basta atualizar seus campos, mas um dos campos deve ser atualizado somente se não estiver definido.

Eu usei informações desta pergunta: https://stackoverflow.com/questions/13305878/dont-update-column-if-update-value-is-null , está bastante relacionado ao que eu tenho.

Tentei usar esta consulta, mas quando a executo, dá erro com Column reference 'affiliate_code' is ambiguous:

INSERT INTO accounts (id, token, affiliate_code)
VALUES (value1, value2, value3)
ON CONFLICT (id) DO
UPDATE SET token = value2,
  affiliate_code = COALESCE(affiliate_code, value3);

(os valores reais são substituídos, é claro).

Se eu substituir affiliate_code = COALESCE(affiliate_code, value3)por affiliate_code = value3, tudo funciona, mas não da maneira que eu quero que funcione.

Como posso fazer isso funcionar?

Aqui está como minha tabela é definida:

CREATE TABLE accounts (
  id VARCHAR NOT NULL UNIQUE,
  token VARCHAR NOT NULL,
  affiliate_code VARCHAR
);

Estou tendo um problema ao usar o novo recurso UPSERT no Postgres 9.5

Eu tenho uma tabela que é usada para agregar dados de outra tabela. A chave composta é composta por 20 colunas, 10 das quais podem ser anuláveis. Abaixo, criei uma versão menor do problema que estou tendo, especificamente com valores NULL.

CREATE TABLE public.test_upsert (
upsert_id serial,
name character varying(32) NOT NULL,
status integer NOT NULL,
test_field text,
identifier character varying(255),
count integer,
CONSTRAINT upsert_id_pkey PRIMARY KEY (upsert_id),
CONSTRAINT test_upsert_name_status_test_field_key UNIQUE (name, status, test_field)
);

A execução desta consulta funciona conforme necessário (primeiro insira, depois as inserções subsequentes simplesmente incrementam a contagem):

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun',1,'test value','ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1 
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value';

No entanto, se eu executar esta consulta, 1 linha será inserida a cada vez, em vez de incrementar a contagem da linha inicial:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count) 
VALUES ('shaun',1,null,'ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1  
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = null;

Este é o meu problema. Preciso simplesmente incrementar o valor de contagem e não criar várias linhas idênticas com valores nulos.

Tentando adicionar um índice exclusivo parcial:

CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status, test_field, identifier);

No entanto, isso produz os mesmos resultados, várias linhas nulas sendo inseridas ou esta mensagem de erro ao tentar inserir:

ERRO: não há restrição exclusiva ou de exclusão correspondente à especificação ON CONFLICT

Já tentei adicionar detalhes extras no índice parcial, como WHERE test_field is not null OR identifier is not null. No entanto, ao inserir, recebo a mensagem de erro de restrição.

Além de fazer manipulação de string após cada JSON_EXTRACT, existe uma maneira mais simples ou mais correta de JSON_EXTRACTretornar a string SEM as aspas?

Ou essa pergunta deve estar no StachExchange?

Eu tenho uma tabela de configuração no meu banco de dados SQL Server e essa tabela deve ter apenas uma linha. Para ajudar futuros desenvolvedores a entender isso, gostaria de evitar que mais de uma linha de dados fosse adicionada. Eu optei por usar um gatilho para isso, como abaixo ...

ALTER TRIGGER OnlyOneConfigRow
    ON [dbo].[Configuration]
    INSTEAD OF INSERT
AS
BEGIN
    DECLARE @HasZeroRows BIT;
    SELECT  @HasZeroRows = CASE
        WHEN COUNT (Id) = 0 THEN 1
        ELSE 0
    END
    FROM
        [dbo].[Configuration];

    IF EXISTS(SELECT [Id] FROM inserted) AND @HasZeroRows = 0
    BEGIN
        RAISERROR ('You should not add more than one row into the config table. ', 16, 1)    
    END
END

Isso não gera um erro, mas não permite que a primeira linha entre.

Também existe uma maneira mais eficaz / mais autoexplicativa de limitar o número de linhas que podem ser inseridas em uma tabela para apenas 1, do que isso? Estou faltando algum recurso interno do SQL Server?