All perguntas(dba)

Tome o seguinte exemplo:

SELECT <CalculationA> As ColA,
       <CalculationB> As ColB,
       <CalculationA> + <CalculationB> As ColC
FROM TableA

CalculationA e CalculationB, cada um, seriam calculados duas vezes?
Ou o otimizador seria inteligente o suficiente para calculá-los uma vez e usar o resultado duas vezes?

Eu gostaria de realizar um teste para ver o resultado por mim mesmo, porém, não tenho certeza de como eu poderia verificar algo assim.

Minha suposição é que ele realizaria o cálculo duas vezes.
Nesse caso, dependendo dos cálculos envolvidos, seria melhor usar uma tabela derivada ou uma visualização aninhada? Considere o seguinte:

SELECT TableB.ColA,
       TableB.ColB,
       TableB.ColA + TableB.ColB AS ColC,
FROM(    
      SELECT <CalculationA> As ColA,
             <CalculationB> As ColB
      FROM TableA
    ) As TableB

Nesse caso, eu esperaria que os cálculos fossem realizados apenas uma vez?

Por favor, alguém pode confirmar ou refutar minhas suposições? Ou me instruir sobre como testar algo assim para mim?

Obrigado.

Propósito

Ao tentar criar um exemplo de teste de uma função de auto-referência, uma versão falha enquanto outra é bem-sucedida.

A única diferença é uma adição SELECTao corpo da função, resultando em um plano de execução diferente para ambos.

A função que funciona

CREATE FUNCTION dbo.test5(@i int)
RETURNS INT
AS 
BEGIN
RETURN(
SELECT TOP 1
CASE 
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN  dbo.test5(1) + dbo.test5(2)
END
)
END;

Chamando a função

SELECT dbo.test5(3);

Devoluções

(No column name)
3

A função que não funciona

CREATE FUNCTION dbo.test6(@i int)
RETURNS INT
AS 
BEGIN
RETURN(
SELECT TOP 1
CASE 
WHEN @i = 1 THEN 1
WHEN @i = 2 THEN 2
WHEN @i = 3 THEN (SELECT dbo.test6(1) + dbo.test6(2))
END
)END;

Chamando a função

SELECT dbo.test6(3);

ou

SELECT dbo.test6(2);

Resultados no erro

Nível máximo de procedimento armazenado, função, gatilho ou exibição de aninhamento excedido (limite 32).

Adivinhando a causa

Há um escalar de computação adicional no plano estimado da função com falha, chamando

<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="CASE WHEN [@i]=(1) THEN (1) ELSE CASE WHEN [@i]=(2) THEN (2) ELSE CASE WHEN [@i]=(3) THEN [Expr1000] ELSE NULL END END END">

E expr1000 sendo

<ColumnReference Column="Expr1000" />
<ScalarOperator ScalarString="[dbo].[test6]((1))+[dbo].[test6]((2))">

O que poderia explicar as referências recursivas superiores a 32.

A pergunta real

O adicionado SELECTfaz com que a função se chame repetidamente, resultando em um loop infinito, mas por que adicionar um está SELECTdando esse resultado?

informação adicional

Planos de execução estimados

DB<>Fiddle

Build version:
14.0.3045.24

Testado nos níveis de compatibilidade 100 e 140

Como parte de nosso processo de ETL, comparamos as linhas do teste com o banco de dados de relatórios para descobrir se alguma das colunas realmente mudou desde que os dados foram carregados pela última vez.

A comparação é baseada na chave exclusiva da tabela e em algum tipo de hash de todas as outras colunas. Atualmente, usamos HASHBYTEScom o SHA2_256algoritmo e descobrimos que ele não é dimensionado em servidores grandes se muitos threads de trabalho simultâneos estiverem chamando HASHBYTES.

A taxa de transferência medida em hashes por segundo não aumenta além de 16 threads simultâneos ao testar em um servidor de 96 núcleos. Eu testo alterando o número de MAXDOP 8consultas simultâneas de 1 a 12. O teste com MAXDOP 1mostrou o mesmo gargalo de escalabilidade.

Como solução, quero tentar uma solução SQL CLR. Aqui está minha tentativa de declarar os requisitos:

• A função deve ser capaz de participar de consultas paralelas
• A função deve ser determinística
• A função deve receber uma entrada de uma string NVARCHARou (todas as colunas relevantes são concatenadas)VARBINARY
• O tamanho de entrada típico da string será de 100 a 20.000 caracteres. 20000 não é um máximo
• A chance de uma colisão de hash deve ser aproximadamente igual ou melhor que o algoritmo MD5. CHECKSUMnão funciona para nós porque há muitas colisões.
• A função deve ser bem dimensionada em servidores grandes (a taxa de transferência por encadeamento não deve diminuir significativamente à medida que o número de encadeamentos aumenta)

Para Application Reasons™, suponha que não posso salvar o valor do hash para a tabela de relatórios. É um CCI que não suporta gatilhos ou colunas computadas (há outros problemas também nos quais não quero entrar).

O que é uma maneira escalável de simular HASHBYTESusando uma função SQL CLR? Meu objetivo pode ser expresso como obter o maior número possível de hashes por segundo em um servidor grande, portanto, o desempenho também importa. Eu sou terrível com CLR, então não sei como fazer isso. Se isso motivar alguém a responder, pretendo adicionar uma recompensa a essa pergunta assim que puder. Abaixo está um exemplo de consulta que ilustra muito aproximadamente o caso de uso:

DROP TABLE IF EXISTS #CHANGED_IDS;

SELECT stg.ID INTO #CHANGED_IDS
FROM (
    SELECT ID,
    CAST( HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)))
     AS BINARY(32)) HASH1
    FROM HB_TBL WITH (TABLOCK)
) stg
INNER JOIN (
    SELECT ID,
    CAST(HASHBYTES ('SHA2_256', 
        CAST(FK1 AS NVARCHAR(19)) + 
        CAST(FK2 AS NVARCHAR(19)) + 
        CAST(FK3 AS NVARCHAR(19)) + 
        CAST(FK4 AS NVARCHAR(19)) + 
        CAST(FK5 AS NVARCHAR(19)) + 
        CAST(FK6 AS NVARCHAR(19)) + 
        CAST(FK7 AS NVARCHAR(19)) + 
        CAST(FK8 AS NVARCHAR(19)) + 
        CAST(FK9 AS NVARCHAR(19)) + 
        CAST(FK10 AS NVARCHAR(19)) + 
        CAST(FK11 AS NVARCHAR(19)) + 
        CAST(FK12 AS NVARCHAR(19)) + 
        CAST(FK13 AS NVARCHAR(19)) + 
        CAST(FK14 AS NVARCHAR(19)) + 
        CAST(FK15 AS NVARCHAR(19)) + 
        CAST(STR1 AS NVARCHAR(500)) +
        CAST(STR2 AS NVARCHAR(500)) +
        CAST(STR3 AS NVARCHAR(500)) +
        CAST(STR4 AS NVARCHAR(500)) +
        CAST(STR5 AS NVARCHAR(500)) +
        CAST(COMP1 AS NVARCHAR(1)) + 
        CAST(COMP2 AS NVARCHAR(1)) + 
        CAST(COMP3 AS NVARCHAR(1)) + 
        CAST(COMP4 AS NVARCHAR(1)) + 
        CAST(COMP5 AS NVARCHAR(1)) )
 AS BINARY(32)) HASH1
    FROM HB_TBL_2 WITH (TABLOCK)
) rpt ON rpt.ID = stg.ID
WHERE rpt.HASH1 <> stg.HASH1
OPTION (MAXDOP 8);

Para simplificar um pouco as coisas, provavelmente usarei algo como o seguinte para benchmarking. Vou postar resultados com HASHBYTESna segunda-feira:

CREATE TABLE dbo.HASH_ME (
    ID BIGINT NOT NULL,
    FK1 BIGINT NOT NULL,
    FK2 BIGINT NOT NULL,
    FK3 BIGINT NOT NULL,
    FK4 BIGINT NOT NULL,
    FK5 BIGINT NOT NULL,
    FK6 BIGINT NOT NULL,
    FK7 BIGINT NOT NULL,
    FK8 BIGINT NOT NULL,
    FK9 BIGINT NOT NULL,
    FK10 BIGINT NOT NULL,
    FK11 BIGINT NOT NULL,
    FK12 BIGINT NOT NULL,
    FK13 BIGINT NOT NULL,
    FK14 BIGINT NOT NULL,
    FK15 BIGINT NOT NULL,
    STR1 NVARCHAR(500) NOT NULL,
    STR2 NVARCHAR(500) NOT NULL,
    STR3 NVARCHAR(500) NOT NULL,
    STR4 NVARCHAR(500) NOT NULL,
    STR5 NVARCHAR(2000) NOT NULL,
    COMP1 TINYINT NOT NULL,
    COMP2 TINYINT NOT NULL,
    COMP3 TINYINT NOT NULL,
    COMP4 TINYINT NOT NULL,
    COMP5 TINYINT NOT NULL
);

INSERT INTO dbo.HASH_ME WITH (TABLOCK)
SELECT RN,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000, RN % 1000000,
REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 30)
,REPLICATE(CHAR(65 + RN % 10 ), 1000),
0,1,0,1,0
FROM (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

SELECT MAX(HASHBYTES('SHA2_256',
CAST(N'' AS NVARCHAR(MAX)) + N'|' +
CAST(FK1 AS NVARCHAR(19)) + N'|' +
CAST(FK2 AS NVARCHAR(19)) + N'|' +
CAST(FK3 AS NVARCHAR(19)) + N'|' +
CAST(FK4 AS NVARCHAR(19)) + N'|' +
CAST(FK5 AS NVARCHAR(19)) + N'|' +
CAST(FK6 AS NVARCHAR(19)) + N'|' +
CAST(FK7 AS NVARCHAR(19)) + N'|' +
CAST(FK8 AS NVARCHAR(19)) + N'|' +
CAST(FK9 AS NVARCHAR(19)) + N'|' +
CAST(FK10 AS NVARCHAR(19)) + N'|' +
CAST(FK11 AS NVARCHAR(19)) + N'|' +
CAST(FK12 AS NVARCHAR(19)) + N'|' +
CAST(FK13 AS NVARCHAR(19)) + N'|' +
CAST(FK14 AS NVARCHAR(19)) + N'|' +
CAST(FK15 AS NVARCHAR(19)) + N'|' +
CAST(STR1 AS NVARCHAR(500)) + N'|' +
CAST(STR2 AS NVARCHAR(500)) + N'|' +
CAST(STR3 AS NVARCHAR(500)) + N'|' +
CAST(STR4 AS NVARCHAR(500)) + N'|' +
CAST(STR5 AS NVARCHAR(2000)) + N'|' +
CAST(COMP1 AS NVARCHAR(1)) + N'|' +
CAST(COMP2 AS NVARCHAR(1)) + N'|' +
CAST(COMP3 AS NVARCHAR(1)) + N'|' +
CAST(COMP4 AS NVARCHAR(1)) + N'|' +
CAST(COMP5 AS NVARCHAR(1)) )
)
FROM dbo.HASH_ME
OPTION (MAXDOP 1);

É mesmo possível?

Meu caso de uso é uma tabela contábil, com a exigência de que uma vez que um registro seja criado, ele deve ser somente leitura, ou seja, ninguém deve poder editá-lo ou excluí-lo. Isso se aplica apenas à tabela contábil e tabelas com relação direta a ela - existem outras tabelas no mesmo esquema que serão atualizadas/excluídas normalmente.

Meu entendimento é que, para fins de integridade de dados, esses tipos de restrições devem ser aplicados na camada de banco de dados, mas não consigo encontrar uma maneira limpa e amplamente aceita de fazer isso - esse é um caso de uso em que seria melhor fazê-lo na camada de aplicação?

O ideal seria uma maneira de fazer isso em SQL simples, de modo a ser agnóstico de qual plataforma de banco de dados é usada, pois isso pode estar sujeito a alterações, mas percebo que pode ser pedir demais, então se tiver para ser dependente da plataforma, é preferível algum tipo de MySQL.

Obrigada!

Quero saber se as chaves primárias compostas são uma prática ruim e, se não, em quais cenários seu uso é benéfico?

Minha pergunta é baseada neste artigo

Observe a parte sobre chaves primárias compostas:

Má Prática Nº 6: Chaves Primárias Compostas

Esse é um ponto controverso, já que muitos designers de banco de dados falam hoje em dia sobre o uso de um campo gerado automaticamente por ID inteiro como chave primária em vez de um composto definido pela combinação de dois ou mais campos. Isso é atualmente definido como a “melhor prática” e, pessoalmente, tendo a concordar com isso.

No entanto, isso é apenas uma convenção e, claro, os DBEs permitem a definição de chaves primárias compostas, que muitos designers consideram inevitáveis. Portanto, assim como a redundância, as chaves primárias compostas são uma decisão de design.

Cuidado, porém, se sua tabela com uma chave primária composta tiver milhões de linhas, o índice que controla a chave composta pode crescer até um ponto em que o desempenho da operação CRUD seja muito degradado. Nesse caso, é muito melhor usar uma chave primária de ID de inteiro simples cujo índice será compacto o suficiente e estabelecerá as restrições DBE necessárias para manter a exclusividade.

Muito foi escrito sobre os perigos das UDFs escalares no SQL Server. Uma pesquisa casual retornará muitos resultados.

Existem alguns lugares onde uma UDF escalar é a única opção.

Como exemplo: ao lidar com XML: XQuery não pode ser usado como definição de coluna computada. Uma opção documentada pela Microsoft é usar uma UDF escalar para encapsular sua XQuery em uma UDF escalar e, em seguida, usá-la em uma coluna computada.

Isso tem vários efeitos e algumas soluções alternativas.

• Executa linha por linha quando a tabela é consultada
• Força todas as consultas na tabela a serem executadas em série

Você pode contornar a execução linha por linha vinculando a função ao esquema e persistindo a coluna computada ou indexando-a. Nenhum desses métodos pode impedir a serialização forçada de consultas que atingem a tabela, mesmo quando a UDF escalar não é referenciada.

Existe uma maneira conhecida de fazer isso?

Estou tendo dificuldade em entender a ideia dos prós e contras do particionamento de tabelas. Estou prestes a começar a trabalhar em um projeto que teria 8 tabelas e uma delas será a tabela de dados principal que conterá 180-260 milhões de registros. Como vai ser tabela devidamente indexada, então estou pensando em limitar os registros da tabela para 20 milhões desta forma eu teria que criar de 9 a 13 tabelas.

Mas não tenho certeza de como isso melhorará o desempenho porque eles estarão na mesma máquina (32 GB de RAM)?

Estou usando o MySQL e as tabelas seriam MyISAM e a tabela grande teria um índice no campo id e não há mais complexidades como pesquisa de texto completo, etc.

Por favor, também esclareça o particionamento de tabela versus o particionamento de banco de dados.

Estou tendo problemas para fazer backup de meus bancos de dados após uma atualização. Eu estive bisbilhotando no meu sistema tentando descobrir o porquê. Uma consulta que executei retornou esse resultado.

Got error: 1449: The user specified as a definer ('cittool'@'%') does not exist when using LOCK TABLES

Após alguma investigação, parece que o definidor dessas visualizações é uma conta de desenvolvedor antiga que foi removida do sistema. Os bancos de dados e visualizações com esse problema são usados ​​com pouca frequência e a maioria é mantida para fins de arquivamento.

Existem cerca de 40 visualizações com um definidor que não existe mais. Existe uma maneira fácil de alterar o definidor para uma conta diferente em tudo de uma só vez? Existe uma maneira de fazer o mysqldump simplesmente despejar todas as visualizações em um arquivo para que eu possa editar esse arquivo e recriar as visualizações?

Estou interessado em saber quais métodos outras pessoas estão usando para acompanhar as alterações feitas no banco de dados, incluindo alterações de definição de tabela, novos objetos, alterações de pacotes, etc. Você usa arquivos simples com um sistema de controle de versão externo? Gatilhos? Outros softwares?

Arquivos de banco de dados criados usando o SQL 2008 não são compatíveis com o 2005. Existe uma solução alternativa?