Michael Green's questions

É claro que omitir o comprimento de um varchar é uma coisa ruim . Infelizmente agora estou trabalhando com uma base de código onde isso aconteceu. Extensivamente. Eu gostaria de corrigir isso. O primeiro passo será encontrar as ocorrências. É aqui que preciso de ajuda.

Pesquisas em vários mecanismos da web usando todos os sinônimos que consigo imaginar não retornam nenhuma resposta oficial. estou pedindo

• casos de teste adicionais que perdi
• uma maneira abrangente e canônica de encontrar declarações sem extensão

Qualquer tecnologia normalmente disponível em um ambiente de desenvolvimento Windows (SSMS, Powershell, .Net etc.) é boa. Respostas que empregassem mais tecnologias de nicho seriam interessantes para a comunidade em geral, mas nem tanto para mim, pessoalmente.

Testes

Como os quatro tipos de dados em questão - char, nchar, varchar e nvarchar - terminam todos com os caracteres CHAR, utilizo isso sozinho nos testes abaixo. Isso evita o inchaço da lista e simplifica a adição de mais testes. Será fácil copiar, colar e substituir, caso seja necessário.

-- These are all legal; the regex must not return these
char(9)
char (9)            -- with a space
char    (9)         -- with a tab
char         (9)    -- tab space tab space
char(max)
char
(9)                 -- a new line between type and length

character(9)
CAST(999 AS character(9))

char varying(9)
character varying(9)
CAST(999 AS char varying(9))
CAST(999 AS character varying(9))


-- These also are legal; ugly, but legal
[char](9)
[char] (9)          -- with a space
[char]  (9)         -- with a tab
[char]       (9)    -- tab space tab space
[char](max)
[char]
(9)                 -- a new line between type and length

-- The type can also be delimited by double-quote
"char"(9)
-- All the tests using square brackets should be duplicated with other delimiters.

[character](9)
CAST(999 AS [character](9))

-- SQL Server 2022 throws an error for [character varying]
-- Msg 243, Level 16, State 1, Line 15
-- Type character varying is not a defined system type.


-- These are business terms which the regex should not return
characteristic
charge
chart

-- These are valid SQL but missing the length. These are what the search should return
char;
char ;      -- a space
char    ;   -- a tab
char,
char ,
char = 'lorem'
cast(9 as char)
convert(char, 9)

[char];
[char] ;        -- a space
[char]  ;   -- a tab
[char],
[char] ,
[char] = 'lorem'
cast(9 as [char])
convert([char], 9)

character
CAST(999 AS character)

char varying
character varying
CAST(999 AS char varying)
CAST(999 AS character varying)

Eu tenho uma pequena mesa com poucas linhas

create table dbo.p(i int);
insert dbo.p(i)
values (1), (2), (3), (4), (4);

Criei estatísticas; nenhum índice, apenas as estatísticas.

create statistics p_c on dbo.p(i) with fullscan;

Estou investigando várias coisas, então tentei definir ROWCOUNT para o limite superior de um bigint.

update statistics dbo.p p_c with rowcount = 9223372036854775807;

Isso falha com mensagem de erro

Msg 3739, Nível 11, Estado 3, Linha 346
Não é possível ATUALIZAR o índice 'p_c' porque não é uma coleção de estatísticas.

Também falha com ROWCOUNT = 1;embora update statistics dbo.p p_cseja bem-sucedido.

Este erro não está na documentação . Não consigo encontrar nada online que pareça relevante.

O que é uma coleção de estatísticas em oposição às antigas estatísticas de tabela? Por que a configuração de ROWCOUNT pode estar falhando aqui?

Servidor SQL 2017 (RTM-CU31-GDR) (KB5021126)

O SQL Server rastreia a alocação de página em vários bitmaps internos . Entre elas estão as páginas Global Allocation Map (GAM) e Page Free Space (PFS). Sabemos que as páginas GAM ocorrem em intervalos definidos de 511.232 páginas e que as páginas PFS ocorrem em intervalos definidos de 8.088 páginas.

Dado um arquivo de dados grande o suficiente, essa repetição acabará levando uma página a ser a próxima na fila para GAM e PFS. Fazendo as contas, isso acontece após 1.011 GAMs ou 63.904 páginas PFS na página número 516.855.552. Isso equivale a um único arquivo de sistema operacional com pouco menos de 4 TB. Como o tamanho máximo de um único arquivo de dados é de 16 TB ( source ), isso foi permitido.

Minha pergunta: quando um único arquivo de dados atinge 4 TB, qual tipo de página é a página 516855552 - GAM ou PFS? Para onde vai o outro? Este comentário de Paul Randal sugere que é desviado para uma das páginas não utilizadas na extensão GAM:

As extensões GAM, exceto a primeira, têm GAM, SGAM, DIFF_MAP, ML_MAP. Cada 4 TB da extensão GAM também terá uma página PFS.

Eu encontrei isso referenciado aqui , mas não explicado categoricamente:

-- There may be an issue with the ML map page position
-- on the four extents where PFS pages and GAM pages live
-- (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
-- but I think we'll be ok.

Nenhum outro resultado significativo aparece para mim nos googles.

Os bancos de dados aos quais tenho acesso possuem vários arquivos, portanto, nenhum atende ao requisito de tamanho.

O gráfico para esta outra pergunta é para uma tabela de 1 milhão de linhas. Quando desenhado para 10.000 linhas, o gráfico tem uma forma bastante diferente e mais interessante.

Há um platô quando o comprimento da coluna está entre 524 e 903 de comprimento.

Essa forma é vista em meus laptops de trabalho e domésticos. (SQL Server 2017 CU27 e 2017 CU22, 2019 CU8 respectivamente. Ambas as caixas têm 16 GB de RAM.)

As inclinações variam ligeiramente entre as versões.

Isso me faz pensar que é uma escolha de design genuína que foi ajustada em vez de um artefato.

Definir BATCH_MODE_ON_ROWSTORE = OFF para SQL Server 2019 faz pouca diferença. A memória desejada é a mesma em todos os tamanhos, exceto t800, que é 6688kB OFF e 6656kB ON.

Como é que este gráfico tem esta forma? Quais considerações significam que uma solução de três regiões é ideal em contagens de linhas moderadas (10.000 linhas), mas não em contagens de linhas maiores (1 milhão de linhas)?

Entendo que ninguém fora da equipe de desenvolvimento do SQL Server será capaz de afirmar conclusivamente o motivo pelo qual esse gráfico em particular é dessa forma. Mas, de um modo geral, a partir da experiência acadêmica e da indústria, se eu começasse a escrever um módulo de gerenciamento de memória para um DBMS hipotético, que tipo de considerações levaria a esse tipo de resultado?

O padrão Lookup-ForEach é comum no Azure Data Factory (ADF). Como os itens produzidos pelo Lookup são alocados aos trabalhadores do ForEach, cujo número é controlado pelo Batch Count?

Eu tenho duas mesas. Cada um contém alguns atributos para uma entidade comercial e o intervalo de datas para o qual esses atributos eram válidos. Eu quero combinar essas tabelas em uma, combinando linhas na chave de negócios comum e dividindo os intervalos de tempo.

O exemplo do mundo real são duas tabelas temporais de origem que alimentam uma tabela de dimensão tipo 2 no data warehouse.

A entidade pode estar presente em nenhum, um ou ambos os sistemas de origem em qualquer momento. Uma vez que uma entidade é registrada em um sistema de origem, os intervalos são bem comportados - sem lacunas, duplicatas ou outros negócios de macaco. A associação às fontes pode terminar em datas diferentes.

As regras de negócios afirmam que queremos apenas retornar intervalos em que a entidade esteja presente em ambas as fontes simultaneamente.

Qual consulta dará esse resultado?

Isso ilustra a situação:

Month          J     F     M     A     M     J     J
Source A:  <--><----------><----------><---->
Source B:            <----><----><----------------><-->
               
Result:              <----><----><----><---->

Dados de amostra

Para simplificar, usei intervalos de datas fechados; provavelmente qualquer solução poderia ser estendida para intervalos semi-abertos com um pouco de digitação.

drop table if exists dbo.SourceA;
drop table if exists dbo.SourceB;
go

create table dbo.SourceA
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);

create table dbo.SourceB
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);
GO


insert dbo.SourceA(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990101', '19990113', 'black'),
    (1, '19990114', '19990313', 'red'),
    (1, '19990314', '19990513', 'blue'),
    (1, '19990514', '19990613', 'green'),
    (2, '20110714', '20110913', 'pink'),
    (2, '20110914', '20111113', 'white'),
    (2, '20111114', '20111213', 'gray');

insert dbo.SourceB(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990214', '19990313', 'left'),
    (1, '19990314', '19990413', 'right'),
    (1, '19990414', '19990713', 'centre'),
    (1, '19990714', '19990730', 'back'),
    (2, '20110814', '20110913', 'top'),
    (2, '20110914', '20111013', 'middle'),
    (2, '20111014', '20120113', 'bottom');

Saída desejada

BusinessKey StartDate   EndDate     a_Colour  b_Placement
----------- ----------  ----------  --------- -----------
1           1999-02-14  1999-03-13  red       left     
1           1999-03-14  1999-04-13  blue      right    
1           1999-04-14  1999-05-13  blue      centre   
1           1999-05-14  1999-06-13  green     centre   
2           2011-08-14  2011-09-13  pink      top      
2           2011-09-14  2011-10-13  white     middle   
2           2011-10-14  2011-11-13  white     bottom   
2           2011-11-14  2011-12-13  gray      bottom    

O SQL Server 2019 CTP3.1 introduziu uma otimização para lidar com a contenção de inserção de última página. Isso assume a forma de uma opção de índice chamada OPTIMIZE_FOR_SEQUENTIAL_KEY.

Imagina-se que isso poderia ser uma adaptação de Bw-Tree ou Bz-Tree . No entanto, eles dependem de páginas de tamanho variável, enquanto o mecanismo de armazenamento atual requer páginas de tamanho fixo.

Como a otimização é implementada? Como os algoritmos B-Tree atuais são alterados por essa otimização? Em que circunstâncias eu escolheria não implantar essa opção?

Pesquisar

Uma patente para uma abordagem de chave reversa .

Dei uma olhada rápida usando DBCC PAGE, comparando 2017 com 2019 e 2019 com e sem OPTIMIZE_FOR_SEQUENTIAL_KEY em um índice clusterizado exclusivo para uma coluna int IDENTITY. Não havia nada que obviamente explicasse o novo comportamento. Isso me faz pensar que é uma coisa algorítmica, ao invés de uma coisa estrutural, o que faz sentido.

Uma postagem no blog do MS.

Este recurso parece centrar-se na detecção e prevenção de comboios .

Eu tenho uma hierarquia que se parece com isso:

Como JSON no TSQL é isso:

declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

Este é apenas um exemplo. Os dados reais podem ser qualquer árvore de profundidade ou amplitude indeterminada.

A documentação e todos os exemplos que encontrei mostram a travessia de cima para baixo . Cada caminho JSON começa no nó raiz e navega, por nomes de nós conhecidos, em direção ao nó desejado. Não encontrei nada sobre começar em uma profundidade indeterminada dentro da hierarquia e trabalhar para cima. Sinto que preciso de algo como fechamento transitivo .

Dado um nome, gostaria de obter a ascendência para esse nome. Por exemplo, dado "Emma" o resultado será "Emma / Chris / Amy". Dado "Bill" a resposta será "Bill/Amy". O formato de saída não é importante; pode ser JSON, uma string ou um conjunto de resultados. Os nomes são únicos.

Este é um exercício de aprendizagem para mim. Não há problema em alterar a representação JSON original para algo equivalente, desde que ainda seja JSONy. Um JSON_QUERY sobre uma representação de lista de adjacências da hierarquia não atingiria meu objetivo.

Eu tenho um banco de dados gráfico com vários tipos de borda. Por exemplo

create table Person(Name varchar(50)) as node;
create table Friend as edge;
create table Manager as edge;

insert Person(Name) values ('Alice'), ('Bob'), ('Chris');

insert Friend($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob');

insert Manager($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris');

Posso encontrar todos os nós conectados a um determinado nó (polimorfismo de grafo) por meio de consultas UNION:

select
    b.Name
from Person a, Person b, Friend f
where a.Name = 'Alice'
and match(a-(f)->b)

union

select
    b.Name
from Person a, Person b, Manager m
where a.Name = 'Alice'
and match(a-(m)->b);

No entanto, isso rapidamente se torna complicado. O SQL Server não possui uma solução mais elegante incorporada à sintaxe.

Existe um padrão de projeto que permite polimorfismo?

Os livros on-line têm a dizer sobre a otimização de consultas no PolyBase:

O otimizador de consulta toma uma decisão baseada em custo para enviar a computação para o Hadoop quando isso melhorar o desempenho da consulta. Ele usa estatísticas em tabelas externas para tomar a decisão baseada em custo. A computação push cria tarefas MapReduce e aproveita os recursos computacionais distribuídos do Hadoop.

Minha pergunta - como e quando o otimizador coleta essas estatísticas?

Dentro do mecanismo relacional do SQL Server, as estatísticas podem ser criadas implícita ou explicitamente, envelhecem conforme ocorrem as gravações e podem ser atualizadas explicitamente ou durante a manutenção da tabela. Existem estratégias semelhantes empregadas no PolyBase?

Dado que os dados podem ser carregados no blob do Hadoop/Azure sem que o SQL Server os veja no caminho, e os volumes de dados serão grandes (provavelmente) a criação de estatísticas ad hoc em tempo de execução por meio de amostragem ou similar parece uma estratégia improvável para mim.

Um requisito em um projeto recente era relatar quando um recurso seria totalmente consumido. Além da data do calendário de exaustão, fui solicitado a mostrar o tempo restante em formato semelhante ao inglês, algo como "1 ano, 3 meses para o fim".

A DATEDIFFfunção incorporada

Retorna a contagem ... dos limites especificados do datepart cruzados entre a data inicial e a data final especificadas.

Se usado como está, isso pode produzir resultados enganosos ou confusos. Por exemplo, usar um intervalo de ANO mostraria 1999-12-31 (AAAA-MM-DD) e 2000-01-01 com um ano de diferença, enquanto o senso comum diria que essas datas estão separadas por apenas 1 dia. Por outro lado, usando um intervalo de DIA 1999-12-31 e 2010-12-31 são separados por 4.018 dias, enquanto a maioria das pessoas veria "11 anos" como uma descrição melhor.

Partir do número de dias e calcular meses e anos a partir daí seria propenso a erros de ano bissexto e tamanho do mês.

Eu comecei a me perguntar como isso poderia ser implementado nos vários dialetos SQL? A saída de exemplo inclui:

create table TestData(
    FromDate date not null,
    ToDate date not null,
    ExpectedResult varchar(100) not null); -- exact formatting is unimportant

insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
       ('1999-12-31', '2000-01-01', '1 day'),
       ('2000-01-01', '2000-02-01', '1 month'),
       ('2000-02-01', '2000-03-01', '1 month'),              -- month length not important
       ('2000-01-28', '2000-02-29', '1 month, 1 day'),       -- leap years to be accounted for
       ('2000-01-01', '2000-12-31', '11 months, 30 days'),
       ('2000-02-28', '2000-03-01', '2 days'),
       ('2001-02-28', '2001-03-01', '1 day'),                -- not a leap year
       ('2000-01-01', '2001-01-01', '1 year'),
       ('2000-01-01', '2011-01-01', '11 years'),
       ('9999-12-30', '9999-12-31', '1 day'),                -- catch overflow in date calculations
       ('1900-01-01', '9999-12-31', '8099 years 11 months 30 days');  -- min(date) to max(date)

Acontece que estou usando o SQL Server 2008R2, mas estou interessado em saber como outros dialetos lidariam com isso.

Geralmente, o SQL Server aceita bloqueios durante as atualizações. Ele também oferece suporte ao escalonamento de bloqueio :

Escalação de bloqueio é o processo de conversão de muitos bloqueios de granularidade em menos bloqueios de granularidade, reduzindo a sobrecarga do sistema enquanto aumenta a probabilidade de contenção de simultaneidade.

Durante um ROLLBACK de transação, o SQL Server reduz os bloqueios, colocando de volta os bloqueios refinados?

Nesta postagem , o escritor executa uma consulta várias vezes. Percebo que as leituras lógicas variam um pouco entre as execuções. Há uma diferença de cerca de 2 páginas em um total de alguns milhares de páginas lidas. Parece claro para mim pelo contexto que não haveria atividade de gravação entre os tempos. Se o plano tivesse mudado, eu esperaria uma variação maior do que uma fração de um por cento.

P: quais fatores farão com que o SQL Server relate diferentes contagens de leitura lógica para a mesma consulta na ausência de gravações de dados?