John's questions

O SQL Server requer que o usuário tenha permissões ALTER TABLE para inserir valores específicos em uma coluna IDENTITY.

Isso parece um pouco estranho, visto que as inserções são claramente uma operação DML.

Para explicar por que isso é um problema para mim, lembre-se primeiro de que os bancos de dados não revertem valores de identidade em uma reversão de transação. Isso ocorre porque os bancos de dados permitem a inserção simultânea de várias transações.

Portanto, se uma transação que inseriu uma linha posteriormente falhar e precisar ser repetida, pode-se reutilizar com segurança o valor de identidade já recuperado na primeira tentativa.

Estou escrevendo um editor de banco de dados e essa abordagem facilita a implementação de inserções complicadas: o usuário está construindo gradualmente um gráfico de linhas de tabela relacionadas para as quais as chaves são necessárias, mas as chaves são geradas pelo banco de dados. Os ORMs usam chaves temporárias para isso e reescrevem tudo após um commit, mas isso é obviamente mais difícil de implementar. É mais fácil sempre ir ao banco de dados para cada nova etapa de inserção, obter o valor de identidade e reverter. Somente quando o usuário clica em salvar no final, a transação é confirmada e os valores ainda devem estar corretos.

Isso funciona para alguns outros bancos de dados como MySQL e Postgres, mas o SQL Server tem a peculiaridade de precisar de um nível surpreendentemente alto de privilégio.

Minha pergunta é por que isso acontece e se isso pode ser indicativo de algum outro problema sobre a inserção explícita de valores de identidade que eu perdi até agora.

Como desenvolvedor de aplicativos, estou acostumado a usar transações de banco de dados apenas como forma de jogar nas modificações depois que um usuário clica em "salvar".

É assim que a maioria dos servidores de banco de dados com os quais estou familiarizado espera que suas transações sejam usadas e não gostam de transações de longa duração. Sob muitas circunstâncias, eles levam ao bloqueio de outros escritores ou mesmo leitores com muito pouca ajuda para lidar com esses bloqueios.

Mas estou familiarizado apenas com uma fatia do mundo do banco de dados - uso principalmente o SQL Server e já vi alguns MySQL. Esses bancos de dados são usados ​​principalmente como armazenamento de aplicativos com lógica de negócios no próprio banco de dados sendo reduzido principalmente para gerar IDs exclusivos de uma forma ou de outra.

Eu poderia imaginar que outros servidores, como o Oracle, tenham expectativas diferentes.

A abordagem que me interessa é aquela em que quando o usuário clica em "editar", uma transação é aberta e todas as edições que o usuário faz são enviadas imediatamente para o banco de dados. Toda a lógica de negócios é, portanto, aplicada imediatamente e, como tal, visível na interface do usuário, mesmo antes do "salvar" (também conhecido como "commit").

Esse paradigma facilitaria várias coisas:

• O aplicativo que faz a edição não precisa gerenciar ids preliminares para linhas não salvas, como fazem os ORMs.
• O usuário pode obter feedback da lógica de negócios no servidor de banco de dados, como valores de colunas computadas ou os efeitos resultantes de gatilhos, mesmo antes de se comprometer com as alterações.
• Violações de restrição podem ser detectadas mais cedo na edição se muitas alterações forem feitas antes do commit.
• Se o servidor de banco de dados tiver um bom suporte para ele, a edição conflitante de várias transações pode levar a mensagens de erro melhores, como "usuário abc está editando a linha xyz".

Eu investiguei o estado do suporte do SQL Server para essa abordagem e é algo prestes a ser possível, mas provavelmente geralmente uma má ideia na prática. O principal problema é que os escritores de lá bloqueiam uns aos outros mesmo sob isolamento de instantâneo. Em particular, o escritor que escreve primeiro ganha, não aquele que se compromete primeiro.

Minha pergunta é: Existem servidores de banco de dados que suportam melhor esse cenário? O que a Oracle tem a dizer sobre isso, por exemplo? Em particular, o servidor teria que

• Permitir gravações simultâneas na mesma linha sem bloquear e fazer o primeiro committer vencer.
• Portanto, uma transação não confirmada e pendente que foi gravada não deve afetar outros usuários. Se um usuário diferente confirmar uma gravação conflitante que deve funcionar, e a transação pendente se tornar não confirmável (ou será revertida automaticamente nesse ponto).

Estou procurando isso para o meu navegador de banco de dados genérico ;

O instantâneo confirmado de leitura e os níveis de isolamento de instantâneo no SQL Server eliminam a maioria dos bloqueios, exceto um: um gravador ainda bloqueia outros gravadores .

A documentação anda na ponta dos pés dizendo isso e, posteriormente, não documenta mais nada que seria muito interessante saber:

É realmente apenas uma linha modificada que fica bloqueada exclusivamente? Ou também podem ser linhas não relacionadas (por exemplo, adjacentes em um índice) ou páginas?

Eu dei uma olhada nos bloqueios sys.dm_tran_lockse só vi bloqueios exclusivos em linhas modificadas durante uma transação não confirmada - páginas onde meramente bloqueadas como IX.

Também testei se duas transações poderiam modificar duas linhas diferentes simultaneamente durante duas transações não confirmadas em uma tabela muito pequena que provavelmente caberia em uma página e que também funcionasse.

Se, de fato, apenas as linhas modificadas forem bloqueadas exclusivamente, isso daria a um aplicativo com acesso exclusivo ao banco de dados a garantia de escrita sem bloqueio se garantir que duas conexões não sejam gravadas simultaneamente na mesma linha.

Isso seria possível no cenário que tenho em mente - mas dificilmente há uma maneira de fazer algo assim se os bloqueios de página entrarem em jogo, pois é imprevisível quais linhas exatamente seriam afetadas.

Uma LIKEcláusula pode testar se uma string ocorre em outra e a CHARINDEXfunção pode fornecer a posição inicial da primeira correspondência.

No meu caso, estou interessado na posição final , que é, devido aos meandros dos agrupamentos, não derivável do local inicial. Por exemplo, em um agrupamento alemão ( German_PhoneBook_100_CI_AS_SC_UTF8),

• häocorre em 'Häger' na posição 1 e termina na posição 2 e
• häocorre em 'Haeger' na posição 1 e termina na posição 3.

O problema disso é marcar a parte correspondente de um texto de resultado de pesquisa para o benefício dos usuários.

Eu estive pensando em inverter as cordas, mas ainda posso obter apenas a primeira correspondência CHARINDEXe, nesse caso invertido, precisaria da última.

Alguma ideia alguém?

Considere a seguinte tabela de amostra com um índice de várias colunas:

create table BigNumbers (
    col1 tinyint not null,
    col2 tinyint not null,
    col3 tinyint not null,

    index IX_BigNumbers clustered (col1, col2, col3)
)

DECLARE @n INT = 100;

DECLARE @x1 INT = 0;
DECLARE @x2 INT = 0;
DECLARE @x3 INT = 0;

SET NOCOUNT ON;

WHILE @x3 <= @n BEGIN
    SET @x2 = 0;
    WHILE @x2 <= @n BEGIN
        SET @x1 = 0;
        WHILE @x1 <= @n BEGIN
            insert into BigNumbers values (@x1, @x2, @x3);
            SET @x1 = @x1 + 1;
        END;
        SET @x2 = @x2 + 1;
    END;

 SET @x3 = @x3 + 1;
END;

Meu objetivo agora é obter algumas linhas desse índice, começando com uma determinada chave.

O que parece trivial é um pouco complicado, pois não há uma maneira fácil no SQL de expressar a ordem lexicográfica em que o índice está:

DECLARE @x1 INT = 60;
DECLARE @x2 INT = 40;
DECLARE @x3 INT = 98;

select top 5 *
from BigNumbers 
where
  col1 > @x1 or
 (col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3)))
order by col1, col2, col3

O resultado correto é:

60  40  98
60  40  99
60  40  100
60  41  0
60  41  1

No entanto, o plano de consulta me diz que isso usa uma verificação de índice.

O índice subjacente deve ser capaz de buscar e retornar as primeiras linhas maiores ou iguais (@x1, @x2, @3)na ordem do índice, mas como não há como no SQL expressar essa intenção facilmente, o planejador de consulta parece incapaz de entender a dica e, em vez disso, faz uma varredura .

Dicas de índice não ajudam e FORCESEEKdá um plano horrendo.

Curiosamente, a seguinte versão de duas colunas funciona:

select top 5 *
from BigNumbers 
where
  col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3))
order by col1, col2, col3

Não sei por que isso acontece, mas o plano não apenas usa uma busca, como também relata corretamente ter tocado apenas 5 linhas:

Eu gostaria de saber se alguém conhece uma maneira de consultar algumas linhas de um índice maior ou igual a uma determinada tupla de valor com uma busca simples de forma confiável.

Parece estranho que o banco de dados obscureça essa capacidade fundamental sob suas abstrações de nível superior.

Se alguém estiver interessado em saber qual é o problema, estou desenvolvendo uma UI genérica para bancos de dados SQL. O lugar mais óbvio onde você precisa disso é para um botão "carregar mais" onde você deseja continuar mostrando o conteúdo do índice para um determinado ponto de partida. Se isso não for possível em geral, a solução alternativa seria primeiro consultar a correção de todas as colunas, exceto a última, e fazer uma segunda consulta e assim por diante. Seria um pouco de vergonha ter que fazer isso embora.

O recurso Change Tracking do Sql Server permite identificar as linhas modificadas de uma cópia de banco de dados modificada.

Isso é obviamente útil para trabalhos de sincronização, pois o banco de dados pode ser sincronizado de forma incremental.

Minha pergunta é: Existe uma ferramenta que usa esse recurso para realmente fazer sincronizações incrementais?

Eu sei sobre

• SSDT : pode sincronizar, mas não está interessado em alterar os dados de rastreamento até onde eu sei.
• MS Sync Framework : é um framework, estou procurando um aplicativo.

Eu realmente tenho que escrever algo eu mesmo?

Existe talvez algum script SQL legal que faça isso se a fonte for trazida como um servidor vinculado?

O SQL Server tem uma coisa chamada "estatísticas de várias colunas", mas não é o que se pensa que isso significaria.

Vamos dar uma olhada na seguinte tabela de exemplo:

CREATE TABLE BadStatistics 
(
    IsArchived BIT NOT NULL,
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Mystery VARCHAR(200) NOT NULL
);

CREATE NONCLUSTERED INDEX BadIndex 
    ON BadStatistics (IsArchived, Mystery);

Com isso, estão sendo criadas duas estatísticas sobre os dois índices que temos:

Estatísticas para BadIndex:

+--------------+----------------+-------------------------+
| All density  | Average Length | Columns                 |
+--------------+----------------+-------------------------+
| 0.5          | 1              | IsArchived              |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 37             | IsArchived, Mystery     |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 41             | IsArchived, Mystery, Id |
+--------------+----------------+-------------------------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 0            | 0          | 24398   | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 216602  | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

Estatísticas para índice clusterizado:

+--------------+----------------+---------+
| All density  | Average Length | Columns |
+--------------+----------------+---------+
| 4.149378E-06 | 4              | Id      |
+--------------+----------------+---------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 240999       | 240997     | 1       | 240997              | 1              |
+--------------+------------+---------+---------------------+----------------+
| 241000       | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

(Preenchi a tabela com dados de amostra aleatórios em que cerca de um décimo das linhas não são arquivadas. Executei uma atualização completa das estatísticas de verificação posteriormente.)

Por que o histograma das estatísticas de duas colunas usa apenas uma coluna? Eu sei que muitas pessoas escreveram sobre isso , mas qual é a lógica? Nesse caso, torna todo o histograma muito menos útil, porque a primeira coluna só tem dois valores. Por que as estatísticas seriam arbitrariamente restritas assim?

Observe que esta pergunta não se refere a histogramas multidimensionais, que são uma fera totalmente diferente. Trata-se de histogramas unidimensionais com a única dimensão sendo as tuplas contendo as respectivas colunas múltiplas.

Em parte por curiosidade, me perguntei se poderia usar uma exibição indexada (materializada) para acelerar uma consulta de contagem em alguma tabela base.

A consulta é algo como

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

então eu criei uma visão

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

com um índice agrupado

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

E isso funciona, agora posso escrever a consulta original como

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

e obter o resultado desejado muito mais rápido.

Infelizmente, dificilmente é muito útil para mim, pois minhas consultas geralmente não são feitas à mão. Eu realmente preciso que a consulta original se torne mais rápida usando a exibição indexada como uma espécie de índice para BaseTable- e acho que li em algum lugar que isso pode acontecer em algumas circunstâncias, mas de acordo com meus testes, não neste.

Então minha(s) pergunta(s) seria(m):

• As exibições indexadas ainda podem me ajudar neste cenário de alguma forma?
• Alguém pode recomendar fontes/literatura que explique em quais casos as exibições indexadas são usadas para otimizações de consultas nas tabelas em que se baseiam?

EDIT: Na pergunta duplicada - estou mais interessado no GROUP BY e no aspecto agregado de exibições indexadas. A resposta me ajudou a encontrar um erro estúpido que cometi, agora está funcionando para mim também.

JOYOUS ADDENDUM : Agora que funcionou, testei com sucesso que funciona até mesmo nos casos em que a consulta contém uma junção à esquerda naqueles casos em que a junção à esquerda pode de fato ser otimizada (ou seja, a cláusula on cobre um índice exclusivo na tabela unida).

Isso é realmente incrível, porque significa que, mesmo em casos de consulta com junções à esquerda, é possível projetar o esquema de forma que obter uma contagem total de linhas de tudo ou agrupamentos específicos seja rápido.

Eu encontrei uma pergunta semelhante e estou ciente de que para uma consulta do formulário

SELECT COUNT(1)
FROM foo f
LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2

para executar sem tocar bar, é necessário que haja um índice exclusivo nas duas colunas em questão.

E, de fato, isso funciona até agora, as tabelas sendo definidas como:

CREATE TABLE [dbo].[Foo](
    [Value] [varchar](255) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Bar](
    [Value] [varchar](1024) NULL,
    [Value2] [varchar](1024) NULL
)

CREATE UNIQUE CLUSTERED INDEX [IX] ON [dbo].[Bar]
(
    [Value] ASC,
    [Value2] ASC
)

O plano de consulta não toca bar, ótimo.

Em minha busca de por que isso não funciona na minha consulta da vida real de algum aplicativo que escrevo, reduzi a consulta lá para o seguinte teste que é simples e também tem o planejador de consulta falha em deixar as mãos fora da barmesa:

WITH numbers AS (
    SELECT 1 AS i
    UNION ALL SELECT i + 1
    FROM numbers
    WHERE i < 10
)
SELECT COUNT(1)
FROM numbers n
LEFT OUTER JOIN bar b ON n.i = b.Value AND n.i = b.Value2
;

Isso realmente toca a barra. Hmmm.

Alguma ideia? Por que o planejador de consulta acha que isso é diferente da outra consulta?

(Meu problema real não usa essa expressão de tabela recursiva, na verdade é exatamente como o caso de demonstração, uma junção simples em duas colunas - e não consigo entender por que não deixaria a tabela unida sozinha em um cenário de contagem.)