Doug Coats's questions

Então, eu estava lendo meu blog matinal e me deparei com este exercício divertido:

https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/

Aqui está a consulta em questão do artigo e o índice que ele propõe.

SELECT TOP (5000)
       p.LastActivityDate,
       p.PostTypeId,
       p.Score,
       p.ViewCount
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND   p.LastActivityDate >= '20110101'
ORDER BY p.Score DESC;

CREATE INDEX whatever 
    ON dbo.Posts(PostTypeId, Score DESC, LastActivityDate) 
        INCLUDE(ViewCount) WITH (DROP_EXISTING = ON);

Muito divertido construir e indexar e tentar ajustá-lo de acordo. No entanto, eu estava anteriormente sob o possível mal-entendido de que a ordem da chave do índice é importante e que certas cláusulas WHERE podem não usar determinados índices quando a ordem da chave do índice não corresponde à consulta. Ou seja, minha falta de experiência com o cenário específico listado, meu pensamento assumido seria que essa consulta não usaria esse índice porque Score está no meio da definição da chave de índice, mas não na cláusula where da consulta.

As colunas ORDER BY são avaliadas quando o otimizador decide qual índice usar e, desde que as colunas da cláusula WHERE e as colunas ORDER by estejam na definição do índice, ele será usado?

Acho que minha pergunta é mais sobre como o otimizador avalia índices em relação às cláusulas WHERE e ORDER BY.

OK, primeiro deixe-me dizer que recebi esse problema de:

• Buscando explicação de junção errada do Tsql no CTE [fechado]

Eu estava tentando ajudar a descobrir o problema, mas fiquei perplexo ao tentar depurar o código uma etapa de cada vez. Eu sei que o problema é devido aos CTEs aninhados (porque durante a depuração, se você despejar cada etapa aka cteX em tabelas temporárias, os resultados corretos são alcançados), mas não sabendo como eles funcionam "sob o capô", não posso explicá-lo de maneira sensata fora de "não funciona yo." Eu suspeito que tenha algo a ver com a forma como o compilador está tentando avaliá-los todos ao mesmo tempo durante o tempo de execução, mas sem mais contexto não posso dizer com certeza.

Minha pergunta é apenas tentar entender como eles funcionam sob o capô e como isso se relaciona com essa situação. Agora que estou envolvido, só quero entender o assunto para poder falar sobre ele no futuro e aprender algo divertido nesse meio tempo.

Quem responder aqui também pode fazer cross post no SO e responder lá também.

Configuração do código:

declare @t1 TABLE (ID varchar(max),Action varchar(max), DateTime datetime );
INSERT INTO @t1
Select *
from
(
VALUES 
('w2337','Open','2020-11-06 12:28:10.000'),
('w2337','Hold','2021-06-14 14:50:59.000'),
('w2337','Open','2021-06-14 14:51:26.000'),
('w2337','Hold','2021-06-15 14:50:59.000'),
('w2337','Open','2021-06-17 14:51:26.000'),
('w2337','Open','2021-06-18 14:51:26.000')

) t (ID, Action, DateTime);

with cte1 as (
select [ID],[Action],[DateTime]
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as [RegIndex]
,DENSE_RANK () OVER (ORDER BY ID) as [Index by ID]
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [DateTime]) as [Index by DateTimeID]
,CASE when [Action]='Hold' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesHeld]
FROM @t1 
)
,cte2 as (
select *, MAX([TimesHeld]) OVER (PARTITION BY ID ORDER BY RegIndex ROWS UNBOUNDED PRECEDING) as [FD] from cte1 
)
,cte3 as(
select *, CASE when [Action]='Open' then ROW_NUMBER() OVER (PARTITION BY ID,Action ORDER BY DateTime) end as [TimesOpened]
from cte2 
where FD is not null
)
select 
    a.*, ' ' as thing, b.DateTime  -- b.*   alternating between the direct column versus all is the issue
from cte3 a 
LEFT OUTER JOIN cte3 b
ON a.ID=b.ID and a.TimesHeld=b.TimesOpened 
where a.TimesHeld is not null and b.TimesOpened is not null

Detalhes:

Ao compilar as consultas abaixo, no LEFT OUTER JOIN final, se você selecionar b.* obterá os resultados corretos. No entanto, se você selecionar apenas uma coluna (datetime, por exemplo), os resultados não estarão corretos.

Correto:

Incorreta:

OK, eu estava olhando para reduzir isso potencialmente (como mais um exercício do que uma necessidade, lembre-se) e parece que estou recebendo este aviso sobre o que parece ser uma consulta com melhor desempenho e não na consulta que requer mais memória para executar? Isso é estranho ou estou apenas pensando demais?

Alguém quer me educar aqui (desculpem as imagens grandes)?

1ª consulta e passe o mouse sobre:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c 
    INNER JOIN SCS.scs_auto_sra.scs_contracts cc ON c.icontract_id = cc.icontract_id ;

segunda consulta e passe o mouse sobre:

    UPDATE c SET c.scust_phone = cc.scontract_holder_hphone
    FROM #Claims c 
    INNER JOIN 
        (
            SELECT icontract_id, scontract_holder_hphone 
            FROM SCS.scs_auto_sra.scs_contracts cc 
            WHERE EXISTS (SELECT icontract_id FROM #Claims x WHERE x.icontract_id = cc.icontract_id )
        ) cc ON c.icontract_id = cc.icontract_id ;

Eu fiz uma pesquisa leve sobre esse tópico e gostaria de saber quais são todos os prós e contras ou habilitar/registrar esse .dll específico no SQL Server?

Voltar informações - estamos integrando com um aplicativo de terceiros (não minha decisão, infelizmente) que requer este .dll para alguns outros .dlls. O que eu preciso da função CLR é poder escrever consultas SQL no SSMS e enviar esses dados para a API do aplicativo de terceiros que, por sua vez, faria o carregamento/alterações de dados corretos (inserções e exclusões de/para este aplicativo têm a ser feito através de sua API).

EDIT - talvez eu devesse ter incluído esse detalhe

Ao tentar registrar minha classe c#, obviamente recebi o erro "system.web não registrado blah blah blah" que, por sua vez, levou minha pesquisa sobre esse tópico.

terminar edição

Então, meu enigma é que para poder registrar minha classe C#/.dll, eu tenho que registrar todos os .dll's dependentes, no entanto, com base em minha pesquisa, sei que esse em particular pode ser bastante problemático.

Então, vendo como eu não estou muito familiarizado com as armadilhas fora da minha pesquisa no Google, eu queria saber se um de vocês pode me ajudar a entender como tomar a melhor decisão em relação a isso.

Além disso, o que mais posso adicionar a este post para que seja mais fácil fornecer insights? Eu não tinha certeza de que o código C# era relevante? Eu entendo que isso pode ser um pouco amplo, mas eu esperava que fosse específico o suficiente para não ser sinalizado?

Para ser mais específico sobre o que está ocorrendo aqui (por solicitação de Salomão)

1. O aplicativo de terceiros usa uma "API" (usada vagamente porque me disseram que não é uma ótima API) para enviar dados de um lado para o outro. Você notará que ele chama uma função Importer que recebe apenas uma tabela de dados ou um arquivo do Excel que converte. Não tenho outra opção, pois a empresa me disse que inserções e exclusões via XML normal são terrivelmente lentas e têm um comportamento inesperado.

2. A .DLL que faz referência a System.Web é referenciada em minha classe C#, que é necessária para poder enviar dados em primeiro lugar.

3. Em relação a:

   Por que você não seria capaz de usar os métodos que mencionei? Eles já existem no host CLR do SQL Server. Isso é para um serviço da web, certo

Não tenho certeza se sei o suficiente sobre APIs em geral para responder a isso. Eu poderia e só falta o conhecimento e experiência para fazê-lo. Também pode ser devido ao fato de eu ter limitações sobre como posso interagir com essa API específica e não tenho certeza de como essas limitações se aplicam a esses métodos. (Vou investigar mais e ver se posso responder a esta pergunta eu mesmo).

Embora quanto mais eu penso sobre isso, eu poderia criar uma classe "intermediário" que o SQL Server pode chamar, que então chamaria outra classe, que teria todas as referências corretas e isso poderia me fazer passar pela minha situação atual. Eu ainda, no entanto, estou interessado no feedback específico para que eu possa aprender com isso.

aqui está minha classe c#:

    using Perfion.Api;   -- this .DLL references System.Web. 
    using Perfion.Api.Import;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Text.RegularExpressions;
    using System.Threading.Tasks;

    namespace PerfInsert
    {
        public static class PerfionInsert 
        {
            public static bool CreateCommand(string tblString, string featureName, string connectionString, string perfionConnectionString, string logFile)
            {
                StringBuilder logInfo = new StringBuilder();
                try
                {
                    var wList = new Regex(@"[^0-9a-z\.\[\]_]#", RegexOptions.IgnoreCase);
                    if (wList.IsMatch(tblString))
                    {
                        logInfo.AppendLine($"{DateTime.UtcNow} - Regex Validation Failed for Table Name!");
                        return false;
                    }
                    using (SqlConnection connection = new SqlConnection(connectionString))
                    {
                        var qryString = "SELECT * FROM " + tblString;
                        using (SqlCommand command = new SqlCommand(qryString, connection))
                        {
                            connection.Open();
                            using (var dataReader = command.ExecuteReader())
                            using (var dataTable = new DataTable())
                            {
                                dataTable.Load(dataReader);
                                PerfionApi api = new PerfionApi(perfionConnectionString);
                                Importer importer = new Importer(api.Connection);
                                importer.Status += (sender, e) => { logInfo.AppendLine($"{DateTime.UtcNow} - {e.Title}"); };
                                importer.LoadData(dataTable);
                                importer.ImportToDatabase(featureName);
                            }
                        }
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    logInfo.AppendLine($"{DateTime.UtcNow} - {ex.ToString()}");
                }
                finally
                {
                    File.AppendAllText(logFile, logInfo.ToString());
                }
                return false;
            }
        }
    }

Ok, como o título sugere, isso está ocorrendo.

Ao usar a REPLACEfunção em char(185) para dados em uma tabela temporária, os ("1") também estão sendo removidos.

Dados do SQL SERVER 2008R2
UNICODE

Agrupamento de banco de dados - agrupar SQL_Latin1_General_CP1_CS_AS Tempdb tem padrão

Qualquer outro detalhe que eu possa fornecer que possa ser útil, me avise.