Eu preciso calcular uma soma rolante em um intervalo de datas. Para ilustrar, usando o banco de dados de exemplo do AdventureWorks. A seguinte sintaxe hipotética faria exatamente o que eu preciso: Infelizmente, a extensão da moldura da janela RANGE atualmente não permite um intervalo no SQL Server. Eu sei que posso escrever uma solução usando uma subconsulta e um agregado regular (não-janela): Dado o seguinte índice: O plano de execução é: Embora não seja horrivelmente ineficiente, parece que deveria ser possível expressar essa consulta usando somente agregado de janela E funções analíticas suportadas no SQL Server 2012, 2014 ou 2016 (até agora). Para maior clareza, procuro uma solução que realize uma passagem única sobre os dados. Em T-SQL, isso provavelmente significará que a cláusula OVER fará o trabalho, e o plano de execução contará com Janelas e Engrupos de janelas. Todos os elementos do idioma que utilizam a cláusula OVER são um jogo justo. Uma solução SQLCLR é aceitável, desde que seja garantido produzir resultados corretos. Para as soluções T-SQL, o número de Hashes, Sorts e Window SpoolsAgregados no plano de execução, melhor. Sinta-se livre para adicionar índices, mas estruturas separadas não são permitidas (portanto, nenhuma tabela pré-calculada mantida em sincronia com disparadores, por exemplo). As tabelas de referência são permitidas (tabelas de números, datas, etc.) Idealmente, as soluções produzirão exatamente os mesmos resultados na mesma ordem que a versão da subconsulta acima, mas qualquer coisa que seja discutida corretamente também é aceitável. O desempenho é sempre uma consideração, então as soluções devem ser pelo menos razoavelmente eficientes. Sala de bate-papo dedicada: criei uma sala de bate-papo pública para discussões relacionadas a esta questão e suas respostas. Qualquer usuário com pelo menos 20 pontos de reputação pode participar diretamente. Por favor, ping-me em um comentário abaixo se você tem menos de 20 representantes e gostaria de participar. Perguntou 7 de setembro 15 às 20:13 Grande pergunta, Paul eu usei algumas abordagens diferentes, uma em T-SQL e uma em CLR. A abordagem T-SQL pode ser resumida como as seguintes etapas: Pegue o produto cruzado de productsdates Mesclar nos dados de vendas observados Agregue esses dados ao nível do produto. Calcule somas rolantes ao longo dos últimos 45 dias com base nesses dados agregados (que contém qualquer Dias preenchidos) Filtre esses resultados apenas para os emparelhamentos do produto que tiveram uma ou mais vendas Usando SET STATISTICS IO ON. Esta abordagem reporta Table TransactionHistory. Número de verificação 1, leituras lógicas 484. que confirma a única passagem sobre a tabela. Para referência, a pesquisa de busca de loop original informa Table TransactionHistory. Número de verificação 113444, leituras lógicas 438366. Conforme relatado por SET STATISTICS TIME ON. O tempo da CPU é de 514 msnm. Isso se compara favoravelmente a 2231ms para a consulta original. O resumo do CLR pode ser resumido como as seguintes etapas: Leia os dados na memória, ordenados por produto e data. Ao processar cada transação, adicione um total total dos custos. Sempre que uma transação é um produto diferente da transação anterior, redefina o total em execução para 0. Mantenha um ponteiro para a primeira transação que tenha o mesmo (produto, data) como a transação atual. Sempre que a última transação com esse (produto, data) for encontrada, compute a soma rolante para essa transação e aplique-a a todas as transações com o mesmo (produto, data) Retorna todos os resultados ao usuário Usando SET STATISTICS IO ON. Esta abordagem informa que nenhum IO lógico ocorreu Wow, uma solução perfeita (Na verdade, parece que SET STATISTICS IO não relata IO incorridos dentro do CLR. Mas, a partir do código, é fácil ver que exatamente uma varredura da tabela é feita E recupera os dados em ordem pelo índice sugerido por Paul. Conforme relatado por SET STATISTICS TIME ON, o tempo de CPU é agora de 187ms. Portanto, esta é uma grande melhoria em relação à abordagem T-SQL. Infelizmente, o tempo total decorrido de ambas as abordagens é Muito semelhante em cerca de meio segundo cada. No entanto, a abordagem baseada em CLR tem que produzir linhas de 113K para o console (versus apenas 52K para a abordagem T-SQL que agrupa por produto), então é por isso que eu me concentrei em tempo de CPU em vez disso . Outra grande vantagem desta abordagem é que produz exatamente os mesmos resultados que a abordagem inicial do loopseek, incluindo uma linha para cada transação, mesmo nos casos em que um produto seja vendido várias vezes no mesmo dia. (No AdventureWorks, eu especificamente comparou a linha - por-linha re Sultas e confirmou que eles empataram com a consulta original de Pauls.) Uma desvantagem dessa abordagem, pelo menos na sua forma atual, é que ele lê todos os dados na memória. No entanto, o algoritmo que foi projetado apenas requer rigorosamente o quadro de janela atual na memória em qualquer momento e pode ser atualizado para funcionar para conjuntos de dados que excedam a memória. Paul ilustrou este ponto em sua resposta, produzindo uma implementação desse algoritmo que armazena apenas a janela deslizante na memória. Isso ocorre à custa da concessão de permissões mais elevadas para a montagem CLR, mas definitivamente valeria a pena escalar essa solução até conjuntos de dados arbitrariamente grandes. T-SQL - uma varredura, agrupada por data O plano de execução Do plano de execução, vemos que o índice original proposto por Paul é suficiente para nos permitir realizar uma única análise ordenada de Production. TransactionHistory. Usando uma junção de mesclagem para combinar o histórico de transações com cada possível combinação de produto. Há algumas suposições significativas assadas nesta abordagem. Eu suponho que será para Paul decidir se eles são aceitáveis :) Estou usando a tabela Production. Product. Esta tabela está disponível gratuitamente no AdventureWorks2012 e a relação é aplicada por uma chave estrangeira da Production. TransactionHistory. Então eu interpretei isso como um jogo justo. Essa abordagem baseia-se no fato de as transações não terem um componente de tempo no AdventureWorks2012 se o fizerem, gerando o conjunto completo de combinações de produtos não seria mais possível sem primeiro passar pelo histórico de transações. Eu estou produzindo um conjunto de linhas que contém apenas uma linha por par produtodate. Eu acho que isso é indiscutivelmente correto e, em muitos casos, um resultado mais desejável para retornar. Para cada produto, adicionei uma coluna NumOrders para indicar quantas vendas ocorreram. Veja a seguinte captura de tela para uma comparação dos resultados da consulta original versus a consulta proposta nos casos em que um produto foi vendido várias vezes na mesma data (por exemplo, 319 2007-09-05 00: 00: 00.000) CLR - uma varredura Conjunto completo de resultados não agrupados O corpo da função principal Não há uma tonelada para ver aqui, o corpo principal da função declara as entradas (que devem corresponder à função SQL correspondente), configura uma conexão SQL e abre o SQLReader. Eu separei a lógica principal, de modo que é mais fácil focar: a lógica a seguir poderia ser escrita em linha, mas é um pouco mais fácil de ler quando elas são divididas em seus próprios métodos. Juntando tudo em SQL Tudo para este ponto foi em C, então vamos ver o SQL real envolvido. (Alternativamente, você pode usar este script de implantação para criar a montagem diretamente dos bits da minha montagem em vez de se compilar.) A abordagem CLR oferece muita flexibilidade para otimizar o algoritmo, e provavelmente poderá ser ajustado ainda mais por um especialista Em C. No entanto, também há desvantagens para a estratégia CLR. Algumas coisas a ter em mente: esta abordagem CLR mantém uma cópia do conjunto de dados na memória. É possível usar uma abordagem de transmissão, mas encontrei dificuldades iniciais e descobriu que há uma questão de Lógica deslumbrante reclamando que mudanças no SQL 2008 tornam mais difícil o uso desse tipo de abordagem. Ainda é possível (como Paulo demonstra), mas requer um nível mais alto de permissões configurando o banco de dados como TRUSTWORTHY e concedendo EXTERNALACCESS ao assembly CLR. Portanto, há algumas dificuldades e potenciais implicações de segurança, mas a recompensa é uma abordagem de transmissão que pode escalar melhor para conjuntos de dados muito maiores do que aqueles no AdventureWorks. O CLR pode ser menos acessível para alguns DBAs, tornando essa função mais uma caixa preta que não é tão transparente, nem tão facilmente modificada, nem tão facilmente implantada, e talvez não seja tão facilmente depurada. Esta é uma grande desvantagem quando comparada a uma abordagem T-SQL. Bônus: T-SQL 2 - a abordagem prática que o ID realmente usa Depois de tentar pensar o problema de forma criativa por um tempo, pensei que também publicasse a maneira bastante simples e prática de que eu provavelmente escolheria enfrentar esse problema se surgisse em Meu trabalho diário. Ele faz uso da funcionalidade da janela do SQL 2012, mas não no tipo de maneira inovadora que a questão estava esperando: Isso realmente produz um plano de consulta geral bastante simples, mesmo quando se olha para ambos os dois planos de consulta relevantes: alguns Razões que eu gosto desta abordagem: ele produz o conjunto de resultados completo solicitado na declaração do problema (em oposição à maioria das outras soluções T-SQL, que retornam uma versão agrupada dos resultados). É fácil explicar, entender e depurar, não vou voltar um ano depois e me pergunto como diabos eu posso fazer uma pequena mudança sem arruinar a correção ou o desempenho. Ele funciona em cerca de 900 ms no conjunto de dados fornecido, em vez dos 2700ms de A busca original do loop se os dados eram muito mais densos (mais transações por dia), a complexidade computacional não cresce quadraticamente com o número de transações na janela deslizante (como faz para a consulta original). Eu acho que isso aborda parte de Pauls Preocupação sobre o desejo de evitar varreduras múltiplas. Ele resulta em essencialmente não IO de tempdb em atualizações recentes do SQL 2012 devido à nova funcionalidade de gravação preguiçosa tempdb Para conjuntos de dados muito grandes, é trivial dividir o trabalho em lotes separados para cada produto se a pressão de memória fosse Para tornar-se uma preocupação Algumas ressalvas potenciais: Embora, tecnicamente, escaneie a Produção. TransaçãoHistória apenas uma vez, não é verdadeiramente uma abordagem de varredura porque a tabela temporária de tamanho similar e precisará Execute também o IO de logição adicional nessa tabela. No entanto, não vejo isso como muito diferente de uma tabela de trabalho que temos mais controle manual desde que definimos sua estrutura precisa Dependendo do seu ambiente, o uso de tempdb pode ser visto como positivo (por exemplo, em um conjunto separado de SSD drives) ou um negativo (alta concorrência no servidor, muita disputa tempdb já) respondeu Sep 8 15 às 15:41 Esta é uma longa resposta, então eu decidi adicionar um resumo aqui. No começo, apresento uma solução que produz exatamente o mesmo resultado na mesma ordem que na questão. Ele verifica a tabela principal 3 vezes: para obter uma lista de ProductIDs com o intervalo de datas para cada Produto, para resumir os custos para cada dia (porque há várias transações com as mesmas datas), para juntar resultado com linhas originais. Em seguida, comparo duas abordagens que simplificam a tarefa e evitam uma última varredura da tabela principal. O resultado é um resumo diário, isto é, se várias transações em um Produto tiverem a mesma data em que são rolado em uma única linha. Minha abordagem do passo anterior analisa a tabela duas vezes. Abordagem de Geoff Patterson examina a tabela uma vez, porque ele usa conhecimento externo sobre o intervalo de datas e lista de produtos. Por fim, apresento uma solução de passagem única que novamente retorna um resumo diário, mas não requer conhecimento externo sobre o intervalo de datas ou a lista de IDs de produtos. Eu usarei o banco de dados AdventureWorks2014 e o SQL Server Express 2014. Alterações no banco de dados original: Tipo alterado de Production. TransactionHistory. TransactionDate de data e hora. O componente do tempo era zero de qualquer maneira. Tabela de calendário adicionada dbo. Calendar Índice adicionado ao artigo Production. TransactionHistory O artigo MSDN sobre a cláusula OVER tem um link para uma excelente postagem no blog sobre as funções de janela da Itzik Ben-Gan. Nessa publicação, ele explica como OVER funciona, a diferença entre as opções ROWS e RANGE e menciona esse mesmo problema de calcular uma soma rolante em um intervalo de datas. Ele menciona que a versão atual do SQL Server não implementa RANGE na íntegra e não implementa os tipos de dados do intervalo temporal. Sua explicação sobre a diferença entre ROWS e RANGE me deu uma idéia. Datas sem lacunas e duplicatas Se a tabela TransactionHistory continha datas sem lacunas e sem duplicatas, a seguinte consulta produziria resultados corretos: de fato, uma janela de 45 linhas cobriria exatamente 45 dias. Datas com lacunas sem duplicatas Infelizmente, nossos dados têm lacunas nas datas. Para resolver este problema, podemos usar uma tabela de calendário para gerar um conjunto de datas sem lacunas, depois LEFT JUNTE os dados originais para este conjunto e use a mesma consulta com ROWS ENTRE 45 GUIAS PRECEDENTES E ATUAIS. Isso produziria resultados corretos somente se as datas não forem repetidas (dentro do mesmo ProductID). Datas com lacunas com duplicatas Infelizmente, nossos dados têm as duas lacunas nas datas e as datas podem ser repetidas dentro do mesmo ID de produto. Para resolver este problema, podemos agrupar dados originais por ProductID, TransactionDate para gerar um conjunto de datas sem duplicatas. Em seguida, use a tabela Calendário para gerar um conjunto de datas sem lacunas. Então podemos usar a consulta com ROWS ENTRE 45 PRECEDING AND CURRENT ROW para calcular a SOM circulante. Isso resultaria em resultados corretos. Veja os comentários na consulta abaixo. Eu confirmei que essa consulta produz os mesmos resultados que a abordagem da questão que usa subconsulta. A primeira consulta usa subconsulta, em segundo lugar - essa abordagem. Você pode ver que a duração e o número de leituras são muito menores nessa abordagem. A maioria do custo estimado nesta abordagem é o ORDER BY final. ver abaixo. A abordagem Subconsulta tem um plano simples com loops aninhados e complexidade O (nn). O plano para esta abordagem verifica o TransactionHistory várias vezes, mas não há loops. Como você pode ver mais de 70 de custo estimado é o Classificar para o ORDER BY final. Resultado superior - subconsulta. Fundo - mais. Evitando exames extras A última análise de índice, mesclar junção e classificar no plano acima é causada pelo ININER JOIN final com a tabela original para tornar o resultado final exatamente o mesmo que uma abordagem lenta com subconsulta. O número de linhas retornadas é o mesmo que na tabela TransactionHistory. Existem linhas no TransactionHistory quando várias transações ocorreram no mesmo dia para o mesmo produto. Se for correto mostrar apenas um resumo diário no resultado, esse JOIN final pode ser removido e a consulta se torna um pouco mais simples e um pouco mais rápida. A última análise de índice, mesclar junção e classificar a partir do plano anterior são substituídas pelo filtro, que remove as linhas adicionadas pelo calendário. Ainda assim, TransactionHistory é digitalizado duas vezes. É necessária uma verificação extra para obter o intervalo de datas para cada produto. Fiquei interessado em ver como ele se compara com outra abordagem, onde usamos conhecimento externo sobre o alcance global de datas em TransactionHistory. Mais tabela extra Produto que possui todos os ID de produto para evitar essa verificação extra. Eu removi o cálculo do número de transações por dia a partir desta consulta para tornar a comparação válida. Pode ser adicionado em ambas as consultas, mas a Id gostaria de manter isso simples para comparação. Eu também tive que usar outras datas, porque eu uso a versão 2014 do banco de dados. Ambas as consultas retornam o mesmo resultado na mesma ordem. Aqui estão as estatísticas de tempo e IO. A variante de duas varredura é um pouco mais rápida e tem menos leituras, porque uma variante de varredura tem que usar o Worktable muito. Além disso, a variante one-scan gera mais linhas do que o necessário, como você pode ver nos planos. Ele gera datas para cada ProductID que está na tabela Product, mesmo que um ProductID não tenha nenhuma transação. Existem 504 linhas na tabela de produtos, mas apenas 441 produtos possuem transações no TransactionHistory. Além disso, ele gera o mesmo intervalo de datas para cada produto, o que é mais do que o necessário. Se o TransactionHistory tivesse um histórico geral mais longo, com cada produto individual com um histórico relativamente curto, o número de linhas extra desnecessárias seria ainda maior. Por outro lado, é possível otimizar a variante de dois varredores um pouco mais, criando outro índice mais estreito apenas (ProductID, TransactionDate). Este índice seria usado para calcular datas StartEnd para cada produto (CTEProducts) e teria menos páginas do que cobrir o índice e, como resultado, causa menos leituras. Então, podemos escolher, ou ter uma varredura simples, extra explícita, ou ter uma tabela de trabalho implícita. BTW, se for bom ter um resultado com apenas resumos diários, é melhor criar um índice que não inclua ReferenceOrderID. Isso usaria menos páginas menos IO. Solução de passagem única usando CROSS APPLY Isso se torna uma resposta muito longa, mas aqui está uma variante mais que retorna apenas o resumo diário novamente, mas faz apenas uma varredura dos dados e não requer conhecimento externo sobre o intervalo de datas ou a lista de ProductIDs. Também não faz operações intermediárias. O desempenho geral é semelhante às variantes anteriores, embora pareça ser um pouco pior. A idéia principal é usar uma tabela de números para gerar linhas que preencham as lacunas nas datas. Para cada data existente, use LEAD para calcular o tamanho da lacuna em dias e, em seguida, use CROSS APPLY para adicionar o número de linhas necessárias no conjunto de resultados. No começo eu tentei com uma tabela permanente de números. O plano mostrou um grande número de leituras nesta tabela, embora a duração real fosse praticamente a mesma, como quando eu gerava números usando o CTE. Esse plano é mais longo, porque a consulta usa duas funções de janela (LEAD e SUM). Uma solução alternativa SQLCLR que executa mais rápido e requer menos memória: isso requer o conjunto de permissões EXTERNALACCESS porque ele usa uma conexão de loopback para o servidor de destino e banco de dados em vez da conexão de contexto (lenta). É assim que chamar a função: produz exatamente os mesmos resultados, na mesma ordem, como a questão. Listas lógicas do Profiler: 481 A principal vantagem desta implementação é que é mais rápido do que usar a conexão de contexto e usa menos memória. Só mantém duas coisas na memória a qualquer momento: qualquer linha duplicada (mesmo produto e data da transação). Isso é necessário porque até que o produto ou a data mude, não sabemos qual será a soma final. Nos dados da amostra, existe uma combinação de produto e data que tem 64 linhas. Um intervalo deslizante de 45 dias de custo e datas de transação apenas, para o produto atual. Isso é necessário para ajustar a soma de execução simples para as linhas que deixam a janela deslizante de 45 dias. Este armazenamento em cache mínimo deve garantir que este método fique bem, certamente, melhor do que tentar manter a entrada inteira configurada na memória CLR. Se você estiver na edição Enterprise, Developer ou Avaliação de 64 bits do SQL Server 2014, você pode usar o OLTP em memória. A solução não será uma única varredura e dificilmente usará qualquer função de janela, mas pode adicionar algum valor a esta questão e o algoritmo utilizado pode ser usado como inspiração para outras soluções. Primeiro você precisa habilitar o OLTP na memória no banco de dados do AdventureWorks. O parâmetro para o procedimento é uma variável da tabela In-Memory e que deve ser definida como um tipo. ID não é exclusivo nesta tabela, é exclusivo para cada combinação de ProductID e TransactionDate. Há alguns comentários no procedimento que lhe dizem o que faz, mas, em geral, está calculando o total em execução em um loop e, para cada iteração, faz uma pesquisa para o total em execução, como era há 45 dias (ou mais). O atual total de corridas menos o total de corridas como era 45 dias atrás é a soma de 45 dias que estamos procurando. Invoque o procedimento como este. Testando isso no meu computador, as Estatísticas do Cliente relatam um tempo de execução total de cerca de 750 milissegundos. Para comparações, a versão da sub-consulta demora 3,5 segundos. Este algoritmo também pode ser usado por T-SQL regular. Calcule o total em execução, usando o intervalo não as linhas e armazene o resultado em uma tabela temporária. Então você pode consultar essa tabela com uma auto-associação para o total em execução, como era há 45 dias, e calcular a soma do rolamento. No entanto, a implementação do intervalo em comparação com as linhas é bastante lenta devido ao fato de que é necessário tratar duplicatas da ordem por cláusula de forma diferente, então não obtive todo esse bom desempenho com essa abordagem. Uma solução para isso poderia ser usar outra função de janela como lastvalue () sobre um total executado calculado usando linhas para simular um alcance total em execução. Outra maneira é usar max () over (). Ambos tiveram alguns problemas. Encontrando o índice apropriado para usar para evitar tipos e evitar carretéis com a versão max () over (). Eu desisti de otimizar essas coisas, mas se você está interessado no código que eu tenho até agora, por favor me avise. Respondeu 15 de setembro 15 às 12:38 Bem, isso foi divertido :) Minha solução é um pouco mais lenta do que GeoffPattersons, mas parte disso é o fato de estar ligando de volta à tabela original para eliminar um dos pressupostos de Geoffs (ou seja, uma linha por Par produtodate). Eu fui com o pressuposto de que esta era uma versão simplificada de uma consulta final e pode exigir informações adicionais fora da tabela original. Nota: Im emprestando a tabela do calendário Geoffs e, de fato, acabei por uma solução muito semelhante: Aqui está a própria consulta: basicamente, eu decidi que a maneira mais fácil de lidar com isso era usar a opção para a cláusula ROWS. Mas isso exigiu que eu só tivesse uma linha por ID de produto. TransactionDate e não apenas isso, mas tive que ter uma linha por ID de produto e data possível. Eu fiz isso combinando as tabelas Product, calendar e TransactionHistory em um CTE. Então eu tive que criar outro CTE para gerar a informação do rolamento. Eu tive que fazer isso porque se eu juntei-me de volta a tabela original diretamente, obtive eliminação de linha que tirou meus resultados. Depois disso, foi uma simples questão de juntar meu segundo CTE de volta à tabela original. Eu adicionei a coluna TBE (a ser eliminada) para eliminar as linhas em branco criadas nos CTEs. Também usei um CROSS APPLY no CTE inicial para gerar limites para a minha tabela de calendário. Em seguida, adicionei o índice recomendado: E obtive o plano de execução final: EDIT: No final, adicionei um índice na tabela do calendário que acelerou o desempenho por uma margem razoável. Respondeu 10 de setembro 15 às 16:34 Tenho algumas soluções alternativas que não usam índices ou tabelas de referência. Talvez eles possam ser úteis em situações nas quais você não tem acesso a tabelas adicionais e não pode criar índices. Parece ser possível obter resultados corretos ao agrupar por TransactionDate com apenas uma única passagem dos dados e apenas uma função de janela única. No entanto, não consegui descobrir uma maneira de fazê-lo com apenas uma função de janela quando você não pode agrupar pelo TransactionDate. Para fornecer um quadro de referência, na minha máquina a solução original publicada na pergunta tem um tempo de CPU de 2808 ms sem o índice de cobertura e 1950 ms com o índice de cobertura. Estou testando o banco de dados AdventureWorks2014 e o SQL Server Express 2014. Comecemos com uma solução para quando podemos agrupar por TransactionDate. Uma soma executada nos últimos X dias também pode ser expressa da seguinte maneira: Soma executando para uma soma de execução de linha de todas as linhas anteriores - execução da soma de todas as linhas anteriores para as quais a data está fora da janela de data. No SQL, uma maneira de expressar isso é fazendo duas cópias de seus dados e para a segunda cópia, multiplicando o custo por -1 e adicionando X1 dias à coluna da data. A computação de uma soma em execução sobre todos os dados implementará a fórmula acima. Eu mostro isso para alguns dados de exemplo. Abaixo está uma data de amostragem para um único ID de produto. Eu represento as datas como números para facilitar os cálculos. Iniciando dados: adicione uma segunda cópia dos dados. A segunda cópia tem 46 dias adicionados à data e o custo multiplicado por -1: Pegue a soma corrente ordenada por Data ascendente e CopiedRow decrescente: Filtre as linhas copiadas para obter o resultado desejado: O SQL a seguir é uma maneira de implementar o Algoritmo acima: na minha máquina, isso levou 702 ms de tempo de CPU com o índice de cobertura e 734 ms de tempo de CPU sem o índice. O plano de consulta pode ser encontrado aqui: brentozarpastetheplanidSJdCsGVSl Uma desvantagem desta solução é que parece haver um tipo inevitável ao ordenar pela nova coluna TransactionDate. Eu não acho que esse tipo pode ser resolvido pela adição de índices porque precisamos combinar duas cópias dos dados antes de fazer o pedido. Eu consegui me livrar de um tipo no final da consulta, adicionando uma coluna diferente para ORDER BY. Se eu pedi pela FilterFlag, descobri que o SQL Server otimizaria essa coluna do tipo e executaria um tipo explícito. As soluções para quando precisamos retornar um conjunto de resultados com valores de TransactionDate duplicados para o mesmo ProductId foram muito mais complicadas. Eu resumiria o problema ao mesmo tempo que precisaria particionar e ordenar pela mesma coluna. A sintaxe que Paul forneceu resolve essa questão, então não é surpreendente que seja tão difícil de expressar com as funções de janela atuais disponíveis no SQL Server (se não fosse difícil de expressar, não haveria necessidade de expandir a sintaxe). Se eu usar a consulta acima sem agrupar, obtendo valores diferentes para a soma de rolagem quando houver várias linhas com o mesmo ProductId e TransactionDate. Uma maneira de resolver isso é fazer o mesmo cálculo de soma em execução como acima, mas também para marcar a última linha na partição. Isso pode ser feito com LEAD (assumindo que ProductID nunca é NULL) sem um tipo adicional. Para o valor da soma de execução final, eu uso MAX como uma função de janela para aplicar o valor na última linha da partição a todas as linhas na partição. Na minha máquina, isso levou 2464ms de tempo de CPU sem o índice de cobertura. Como antes, parece haver um tipo inevitável. O plano de consulta pode ser encontrado aqui: brentozarpastetheplanidHyWxhGVBl Eu acho que há espaço para melhorias na consulta acima. Há certamente outras maneiras de usar as funções do Windows para obter o resultado desejado. A maioria das pessoas está familiarizada com a frase, então isso matará dois pássaros com uma pedra. Se você não estiver, a fase se refere a uma abordagem que aborda dois objetivos em uma ação. (Infelizmente, a expressão em si é bastante desagradável, como a maioria de nós não deseja lançar pedras em animais inocentes). Hoje, I39m vai abranger alguns conceitos básicos em dois grandes recursos no SQL Server: o índice Columnstore (disponível apenas no SQL Server Enterprise) e O SQL Query Store. A Microsoft realmente implementou o índice Columnstore no SQL 2012 Enterprise, embora eles o aprimorem nos últimos dois lançamentos do SQL Server. A Microsoft apresentou o Query Store no SQL Server 2016. Então, quais são esses recursos e por que eles são importantes? Bem, eu tenho um demo que irá apresentar os dois recursos e mostrar como eles podem nos ajudar. Antes de ir mais longe, também cubro esse (e outros recursos do SQL 2016) no meu artigo da revista CODE sobre os novos recursos SQL 2016. Como uma introdução básica, o índice Columnstore pode ajudar a acelerar as consultas que exploram as quantidades de grandes quantidades de dados e A Query Store rastreia as execuções de consultas, os planos de execução e as estatísticas de tempo de execução que você normalmente precisa colecionar manualmente. Confie em mim quando eu digo, são excelentes recursos. Para esta demo, eu estarei usando o banco de dados de demonstração do Microsoft Contoso Retail Data Warehouse. Falando vagamente, o Contoso DW é como uma quota muito grande AdventureWorksquot, com tabelas contendo milhões de linhas. (A maior tabela AdventureWorks contém aproximadamente 100.000 linhas no máximo). Você pode baixar o banco de dados do Contoso DW aqui: microsoften-usdownloaddetails. aspxid18279. O Contoso DW funciona muito bem quando você deseja testar desempenho em consultas contra tabelas maiores. O Contoso DW contém uma tabela de fatos de data warehouse padrão chamada FactOnLineSales, com 12,6 milhões de linhas. Certamente, essa não é a maior mesa de armazenamento de dados do mundo, mas também não é uma criança. Suponha que eu quero resumir o valor das vendas do produto para 2009 e classificar os produtos. Eu posso consultar a tabela de fatos e juntar-se à tabela Dimensão do produto e usar uma função RANK, assim: Aqui, um conjunto de resultados parcial das 10 melhores linhas, por Total Sales. No meu laptop (i7, 16 GB de RAM), a consulta leva entre 3-4 segundos para ser executada. Isso pode não parecer o fim do mundo, mas alguns usuários podem esperar resultados quase instantâneos (da maneira que você pode ver resultados quase instantâneos ao usar o Excel contra um cubo OLAP). O único índice que eu atualmente tenho nesta tabela é um índice agrupado em uma chave de vendas. Se eu olhar para o plano de execução, o SQL Server faz uma sugestão para adicionar um índice de cobertura para a tabela: agora, só porque o SQL Server sugere um índice, não significa que você deve criar índices cegamente em todas as mensagens de indexação quotmissing. No entanto, nessa instância, o SQL Server detecta que estamos filtrando com base no ano e usando a chave de produto e a quantidade de vendas. Assim, o SQL Server sugere um índice de cobertura, com o DateKey como o campo da chave de índice. A razão pela qual chamamos isso de quotcoveringquot index é porque o SQL Server irá recorrer ao longo dos campos não-chave que usamos na consulta, quanto ao ridequot. Dessa forma, o SQL Server não precisa usar a tabela ou o índice em cluster em todo o mecanismo do banco de dados pode simplesmente usar o índice de cobertura para a consulta. Os índices de cobertura são populares em determinados cenários de banco de dados de dados e relatórios, embora eles tenham um custo do mecanismo de banco de dados, mantendo-os. Nota: Os índices de cobertura foram durante muito tempo, então eu ainda não abordava o índice Columnstore e a Query Store. Então, vou adicionar o índice de cobertura: se eu re-executar a mesma consulta que corri um momento (o que agregou o valor das vendas para cada produto), a consulta às vezes parece executar cerca de um segundo mais rápido e recebo uma Plano de execução diferente, que usa uma pesquisa de índice em vez de uma verificação de índice (usando a chave de data no índice de cobertura para recuperar vendas para 2009). Portanto, antes do Índice Columnstore, isso poderia ser uma maneira de otimizar essa consulta em versões muito antigas do SQL Server. Ele é executado um pouco mais rápido do que o primeiro, e eu recebo um plano de execução com um Index Seek em vez de um Index Scan. No entanto, existem alguns problemas: os dois operadores de execução, quotIndex Seekquot e quotHash Match (Aggregate), ambos operam essencialmente quotrow by rowquot. Imagine isso em uma mesa com centenas de milhões de linhas. Relacionado, pense no conteúdo de uma tabela de fatos: neste caso, um valor de chave de data único e um valor de chave de produto único podem ser repetidos em centenas de milhares de linhas (lembre-se, a tabela de fato também possui chaves para geografia, promoção, vendedor , Etc.) Então, quando o quotIndex Seekquot e quotHash Matchquot funcionam por linha, eles estão fazendo isso sobre valores que podem ser repetidos em muitas outras linhas. Normalmente, esse é o caso do I39d segue para o índice SQL Server Columnstore, que oferece um cenário para melhorar o desempenho desta consulta de maneiras surpreendentes. Mas antes que eu faça isso, let39s voltem no tempo. Let39s voltam para o ano de 2010, quando a Microsoft apresentou um suplemento para o Excel conhecido como PowerPivot. Muitas pessoas provavelmente se lembravam de mostrar demonstrações do PowerPivot para Excel, onde um usuário poderia ler milhões de linhas de uma fonte de dados externa para o Excel. PowerPivot would compress the data, and provide an engine to create Pivot Tables and Pivot Charts that performed at amazing speeds against the compressed data. PowerPivot used an in-memory technology that Microsoft termed quotVertiPaqquot. This in-memory technology in PowerPivot would basically take duplicate business keyforeign key values and compress them down to a single vector. The in-memory technology would also scanaggregate these values in parallel, in blocks of several hundred at a time. The bottom line is that Microsoft baked a large amount of performance enhancements into the VertiPaq in-memory feature for us to use, right out of the proverbial box. Why am I taking this little stroll down memory lane Because in SQL Server 2012, Microsoft implemented one of the most important features in the history of their database engine: the Columnstore index. The index is really an index in name only: it is a way to take a SQL Server table and create a compressed, in-memory columnstore that compresses duplicate foreign key values down to single vector values. Microsoft also created a new buffer pool to read these compressed vector values in parallel, creating the potential for huge performance gains. So, I39m going to create a columnstore index on the table, and I39ll see how much better (and more efficiently) the query runs, versus the query that runs against the covering index. So, I39ll create a duplicate copy of FactOnlineSales (I39ll call it FactOnlineSalesDetailNCCS), and I39ll create a columnstore index on the duplicated table that way I won39t interfere with the original table and the covering index in any way. Next, I39ll create a columnstore index on the new table: Note several things: I39ve specified several foreign key columns, as well as the Sales Amount. Remember that a columnstore index is not like a traditional row-store index. There is no quotkeyquot. We are simply indicating which columns SQL Server should compress and place in an in-memory columnstore. To use the analogy of PowerPivot for Excel when we create a columnstore index, we39re telling SQL Server to essentially do the same thing that PowerPivot did when we imported 20 million rows into Excel using PowerPivot So, I39ll re-run the query, this time using the duplicated FactOnlineSalesDetailNCCS table that contains the columnstore index. This query runs instantly in less than a second. And I can also say that even if the table had hundreds of millions of rows, it would still run at the proverbial quotbat of an eyelashquot. We could look at the execution plan (and in a few moments, we will), but now it39s time to cover the Query Store feature. Imagine for a moment, that we ran both queries overnight: the query that used the regular FactOnlineSales table (with the covering index) and then the query that used the duplicated table with the Columnstore index. When we log in the following morning, we39d like to see the execution plan for both queries as they took place, as well as the execution statistics. In other words, we39d like to see the same statistics that we39d be able to see if we ran both queries interactively in SQL Management Studio, turned in TIME and IO Statistics, and viewed the execution plan right after executing the query. Well, that39s what the Query Store allows us to do we can turn on (enable) Query Store for a database, which will trigger SQL Server to store query execution and plan statistics so that we can view them later. So, I39m going to enable the Query Store on the Contoso database with the following command (and I39ll also clear out any caching): Then I39ll run the two queries (and quotpretendquot that I ran them hours ago): Now let39s pretend they ran hours ago. According to what I said, the Query Store will capture the execution statistics. So how do I view them Fortunately, that39s quite easy. If I expand the Contoso DW database, I39ll see a Query Store folder. The Query Store has tremendous functionality and I39ll try to cover much of it in subsequent blog posts. But for right now, I want to view execution statistics on the two queries, and specifically examine the execution operators for the columnstore index. So I39ll right-click on the Top Resource Consuming Queries and run that option. That gives me a chart like the one below, where I can see execution duration time (in milliseconds) for all queries that have been executed. In this instance, Query 1 was the query against the original table with the covering index, and Query 2 was against the table with the columnstore index. The numbers don39t lie the columnstore index outperformed the original tablecovering index by a factor of almost 7 to 1. I can change the metric to look at memory consumption instead. In this case, note that query 2 (the columnstore index query) used far more memory. This demonstrates clearly why the columnstore index represents quotin-memoryquot technology SQL Server loads the entire columnstore index in memory, and uses a completely different buffer pool with enhanced execution operators to process the index. OK, so we have some graphs to view execution statistics can we see the execution plan (and execution operators) associated with each execution Yes, we can If you click on the vertical bar for the query that used the columnstore index, you39ll see the execution plan below. The first thing we see is that SQL Server performed a columnstore index scan, and that represented nearly 100 of the cost of the query. You might be saying, quotWait a minute, the first query used a covering index and performed an index seek so how can a columnstore index scan be fasterquot That39s a legitimate question, and fortunately there39s an answer. Even when the first query performed an index seek, it still executed quotrow by rowquot. If I put the mouse over the columnstore index scan operator, I see a tooltip (like the one below), with one important setting: the Execution Mode is BATCH (as opposed to ROW . which is what we had with the first query using the covering index). That BATCH mode tells us that SQL Server is processing the compressed vectors (for any foreign key values that are duplicated, such as the product key and date key) in batches of almost 1,000, in parallel. So SQL Server is still able to process the columnstore index much more efficiently. Additionally, if I place the mouse over the Hash Match (Aggregate) task, I also see that SQL Server is aggregating the columnstore index using Batch mode (although the operator itself represents such a tiny percent of the cost of the query) Finally, you might be asking, quotOK, so SQL Server compresses the values in the data, treats the values as vectors, and read them in blocks of almost a thousand values in parallel but my query only wanted data for 2009. So is SQL Server scanning over the entire set of dataquot Again, a good question. The answer is, quotNot reallyquot. Fortunately for us, the new columnstore index buffer pool performs another function called quotsegment eliminationquot. Basically, SQL Server will examine the vector values for the date key column in the columnstore index, and eliminate segments that are outside the scope of the year 2009. I39ll stop here. In subsequent blog posts I39ll cover both the columnstore index and Query Store in more detail. Essentially, what we39ve seen here today is that the Columnstore index can significantly speed up queries that scanaggregate over large amounts of data, and the Query Store will capture query executions and allow us to examine execution and performance statistics later. In the end, we39d like to produce a result set that shows the following. Notice three things: The columns essentially pivot all of the possible Return Reasons, after showing the sales amount The result set contains subtotals by the week ending (Sunday) date across all clients (where the Client is NULL) The result set contains a grand total row (where the Client and Date are both NULL) First, before I get into the SQL end we could use the dynamic pivotmatrix capability in SSRS. We would simply need to combine the two result sets by one column and then we could feed the results to the SSRS matrix control, which will spread the return reasons across the columns axis of the report. However, not everyone uses SSRS (though most people should). But even then, sometimes developers need to consume result sets in something other than a reporting tool. So for this example, let39s assume we want to generate the result set for a web grid page and possibly the developer wants to quotstrip outquot the subtotal rows (where I have a ResultSetNum value of 2 and 3) and place them in a summary grid. So bottom line, we need to generate the output above directly from a stored procedure. And as an added twist next week there could be Return Reason X and Y and Z. So we don39t know how many return reasons there could be. We simple want the query to pivot on the possible distinct values for Return Reason. Here is where the T-SQL PIVOT has a restriction we need to provide it the possible values. Since we won39t know that until run-time, we need to generate the query string dynamically using the dynamic SQL pattern. The dynamic SQL pattern involves generating the syntax, piece by piece, storing it in a string, and then executing the string at the end. Dynamic SQL can be tricky, as we have to embed syntax inside a string. But in this case, it our only true option if we want to handle a variable number of return reasons. I39ve always found that the best way to create a dynamic SQL solution is by figuring out what the quotidealquot generated-query would be at the end (in this case, given the Return reasons we know about).and then reverse-engineering it by piecing it together one part at a time. And so, here is the SQL we need if we knew those Return Reasons (A through D) were static and would not change. The query does the following: Combines the data from SalesData with the data from ReturnData, where we quothard-wirequot the word Sales as an Action Type form the Sales Table, and then use the Return Reason from the Return Data into the same ActionType column. That will give us a clean ActionType column on which to pivot. We are combining the two SELECT statements into a common table expression (CTE), which is basically a derived table subquery that we subsequently use in the next statement (to PIVOT) A PIVOT statement against the CTE, that sums the dollars for the Action Type being in one of the possible Action Type values. Note that this isn39t the final result set. We are placing this into a CTE that reads from the first CTE. The reason for this is because we want to do multiple groupings at the end. The final SELECT statement, that reads from the PIVOTCTE, and combines it with a subsequent query against the same PIVOTCTE, but where we also implement two groupings in the GROUPING SETS feature in SQL 2008: GROUPING by the Week End Date (dbo. WeekEndingDate) GROUPING for all rows () So if we knew with certainty that we39d never have more return reason codes, then that would be the solution. However, we need to account for other reason codes. So we need to generate that entire query above as one big string where we construct the possible return reasons as one comma separated list. I39m going to show the entire T-SQL code to generate (and execute) the desired query. And then I39ll break it out into parts and explain each step. So first, here39s the entire code to dynamically generate what I39ve got above. There are basically five steps we need to cover. Passo 1 . we know that somewhere in the mix, we need to generate a string for this in the query: SalesAmount, Reason A, Reason B, Reason C, Reason D0160016001600160 What we can do is built a temporary common table expression that combines the hard wired quotSales Amountquot column with the unique list of possible reason codes. Once we have that in a CTE, we can use the nice little trick of FOR XML PATH(3939) to collapse those rows into a single string, put a comma in front of each row that the query reads, and then use STUFF to replace the first instance of a comma with an empty space. This is a trick that you can find in hundreds of SQL blogs. So this first part builds a string called ActionString that we can use further down. Passo 2 . we also know that we39ll want to SUM the generatedpivoted reason columns, along with the standard sales column. So we39ll need a separate string for that, which I39ll call SUMSTRING. I39ll simply use the original ActionString, and then REPLACE the outer brackets with SUM syntax, plus the original brackets. Step 3: Now the real work begins. Using that original query as a model, we want to generate the original query (starting with the UNION of the two tables), but replacing any references to pivoted columns with the strings we dynamically generated above. Also, while not absolutely required, I39ve also created a variable to simply any carriage returnline feed combinations that we want to embed into the generated query (for readability). So we39ll construct the entire query into a variable called SQLPivotQuery. Passo 4. We continue constructing the query again, concatenating the syntax we can quothard-wirequot with the ActionSelectString (that we generated dynamically to hold all the possible return reason values) Step 5 . Finally, we39ll generate the final part of the Pivot Query, that reads from the 2 nd common table expression (PIVOTCTE, from the model above) and generates the final SELECT to read from the PIVOTCTE and combine it with a 2 nd read against PIVOTCTE to implement the grouping sets. Finally, we can quotexecutequot the string using the SQL system stored proc spexecuteSQL So hopefully you can see that the process to following for this type of effort is Determine what the final query would be, based on your current set of data and values (i. e. built a query model) Write the necessary T-SQL code to generate that query model as a string. Arguably the most important part is determining the unique set of values on which you39ll PIVOT, and then collapsing them into one string using the STUFF function and the FOR XML PATH(3939) trick So whats on my mind today Well, at least 13 items Two summers ago, I wrote a draft BDR that focused (in part) on the role of education and the value of a good liberal arts background not just for the software industry but even for other industries as well. One of the themes of this particular BDR emphasized a pivotal and enlightened viewpoint from renowned software architect Allen Holub regarding liberal arts. Ill (faithfully) paraphrase his message: he highlighted the parallels between programming and studying history, by reminding everyone that history is reading and writing (and Ill add, identifying patterns), and software development is also reading and writing (and again, identifying patterns). And so I wrote an opinion piece that focused on this and other related topics. But until today, I never got around to either publishingposting it. Every so often Id think of revising it, and Id even sit down for a few minutes and make some adjustments to it. But then life in general would get in the way and Id never finish it. So what changed A few weeks ago, fellow CoDe Magazine columnist and industry leader Ted Neward wrote a piece in his regular column, Managed Coder , that caught my attention. The title of the article is On Liberal Arts. and I highly recommend that everyone read it. Ted discusses the value of a liberal arts background, the false dichotomy between a liberal arts background and success in software development, and the need to writecommunicate well. He talks about some of his own past encounters with HR personnel management regarding his educational background. He also emphasizes the need to accept and adapt to changes in our industry, as well as the hallmarks of a successful software professional (being reliable, planning ahead, and learning to get past initial conflict with other team members). So its a great read, as are Teds other CoDe articles and blog entries. It also got me back to thinking about my views on this (and other topics) as well, and finally motivated me to finish my own editorial. So, better late than never, here are my current Bakers Dozen of Reflections: I have a saying: Water freezes at 32 degrees . If youre in a trainingmentoring role, you might think youre doing everything in the world to help someone when in fact, theyre only feeling a temperature of 34 degrees and therefore things arent solidifying for them. Sometimes it takes just a little bit more effort or another ideachemical catalyst or a new perspective which means those with prior education can draw on different sources. Water freezes at 32 degrees . Some people can maintain high levels of concentration even with a room full of noisy people. Im not one of them occasionally I need some privacy to think through a critical issue. Some people describe this as you gotta learn to walk away from it. Stated another way, its a search for the rarefied air. This past week I spent hours in half-lit, quiet room with a whiteboard, until I fully understood a problem. It was only then that I could go talk with other developers about a solution. The message here isnt to preach how you should go about your business of solving problems but rather for everyone to know their strengths and what works, and use them to your advantage as much as possible. Some phrases are like fingernails on a chalkboard for me. Use it as a teaching moment is one. (Why is it like fingernails on a chalkboard Because if youre in a mentoring role, you should usually be in teaching moment mode anyway, however subtly). Heres another I cant really explain it in words, but I understand it. This might sound a bit cold, but if a person truly cant explain something in words, maybe they dont understand. Sure, a person can have a fuzzy sense of how something works I can bluff my way through describing how a digital camera works but the truth is that I dont really understand it all that well. There is a field of study known as epistemology (the study of knowledge). One of the fundamental bases of understanding whether its a camera or a design pattern - is the ability to establish context, to identify the chain of related events, the attributes of any components along the way, etc. Yes, understanding is sometimes very hard work, but diving into a topic and breaking it apart is worth the effort. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Ler. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some. NET code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff. net November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. Então, você tem isso. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from. NET, youre in luck. You can pass an ADO. NET data table (with the same tablename property as the name of the Table Type) to the procedure. For. NET developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment.
No comments:
Post a Comment