All perguntas(dba)

Por que essa CASEexpressão:

SELECT CASE column 
        WHEN 'a' THEN '1' 
        WHEN 'b' THEN '2' 
        ... c -> i
        WHEN 'j' THEN '10' 
        WHEN 'k' THEN '11'  
    END [col] 
FROM LinkedServer.database.dbo.table

Produzir este resultado?

Mensagem de erro: Msg 8180, Nível 16, Estado 1, Linha 1
Instrução(ões) não pôde(m) ser preparada(s).
Msg 125, Nível 15, Estado 4, Linha 1 As
expressões de caso só podem ser aninhadas no nível 10.

Claramente não há uma CASEexpressão aninhada aqui, embora haja mais de 10 "ramificações".

Outra esquisitice. Esta função com valor de tabela embutida produz o mesmo erro:

ALTER FUNCTION [dbo].[fn_MyFunction]
(   
     @var varchar(20)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT CASE column 
            WHEN 'a' THEN '1' 
            WHEN 'b' THEN '2' 
            ... c -> i
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table
)

Mas um TVF de várias instruções semelhante funciona bem:

ALTER FUNCTION [dbo].[fn_MyFunction]
(   
    @var varchar(20)
)
RETURNS @result TABLE 
(
    value varchar(max)
)
AS
BEGIN
    INSERT INTO @result
    SELECT CASE column 
            WHEN 'a' THEN '1' 
            WHEN 'b' THEN '2' 
            ... c -> i
            WHEN 'j' THEN '10' 
            WHEN 'k' THEN '11'  
        END [col] 
    FROM LinkedServer.database.dbo.table

RETURN;
END

Considere esta declaração select:

SELECT *, 
       1 AS query_id 
FROM players 
WHERE username='foobar';

Ele retorna a coluna query_idcom valor 1junto com as outras colunas de um jogador.

Como alguém faria o SQL acima retornar pelo menos o query_idmesmo 1que o select não encontrasse nenhuma linha que corresponda?

BTW, é o PostgreSQL 8.4.

Atualmente, estou usando o Tortoise SVN para controlar a origem de um aplicativo da Web .NET. Qual seria a melhor maneira de trazer nossos procedimentos armazenados do SQL Server para o controle de origem? Atualmente, estou usando o VS 2010 como meu ambiente de desenvolvimento e me conectando a um banco de dados SQL Server 2008 R2 externo usando o SQL Server Data Tools (SSDT).

O que tenho feito no passado é salvar os procs em um arquivo .sql e manter esses arquivos sob controle de origem. Tenho certeza que deve haver uma maneira mais eficiente do que isso? Existe uma extensão que eu possa instalar no VS2010, SSDT ou mesmo SQL Server na máquina de produção?

Eu tenho algumas tabelas com números como este (status é FREE ou ASSIGNED)

status do número id_set         
-----------------------
1 000001 ATRIBUÍDO
1 000002 GRÁTIS
1 000003 ATRIBUÍDO
1 000004 GRÁTIS
1 000005 GRÁTIS
1 000006 ATRIBUÍDO
1 000007 ATRIBUÍDO
1 000008 GRÁTIS
1 000009 GRÁTIS
1 000010 GRÁTIS
1 000011 ATRIBUÍDO
1 000012 ATRIBUÍDO
1 000013 ATRIBUÍDO
1 000014 GRÁTIS
1 000015 ATRIBUÍDO

e eu preciso encontrar "n" números consecutivos, então para n = 3, a consulta retornaria

1 000008 GRÁTIS
1 000009 GRÁTIS
1 000010 GRÁTIS

Deve retornar apenas o primeiro grupo possível de cada id_set (na verdade, seria executado apenas para id_set por consulta)

Eu estava verificando as funções do WINDOW, tentei algumas consultas como COUNT(id_number) OVER (PARTITION BY id_set ROWS UNBOUNDED PRECEDING), mas foi tudo o que consegui :) Não consegui pensar em lógica, como fazer isso no Postgres.

Eu estava pensando em criar uma coluna virtual usando funções WINDOW contando linhas anteriores para cada número em que status = 'FREE' e, em seguida, selecione o primeiro número, onde contagem é igual ao meu número "n".

Ou talvez agrupe números por status, mas apenas de um ASSIGNED para outro ASSIGNED e selecione apenas grupos contendo pelo menos "n" números

EDITAR

Eu encontrei esta consulta (e mudei um pouco)

WITH q AS
(
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id_set, status ORDER BY number) AS rnd,
         ROW_NUMBER() OVER (PARTITION BY id_set ORDER BY number) AS rn
  FROM numbers
)
SELECT id_set,
       MIN(number) AS first_number,
       MAX(number) AS last_number,
       status,
       COUNT(number) AS numbers_count
FROM q
GROUP BY id_set,
         rnd - rn,
         status
ORDER BY
     first_number

que produz grupos de números FREE/ASSIGNED, mas gostaria de ter todos os números apenas do primeiro grupo que atende à condição

SQL Fiddle

Estou executando alguns relatórios de uso de índice e estou tentando obter uma definição de Leaf e Non-leaf . Parece haver inserções, atualizações, exclusões, mesclagens de páginas e alocações de páginas. Eu realmente não sei o que isso significa, ou se um é melhor que o outro.

Se alguém pudesse dar uma definição simples de cada um e também explicar por que a Folha ou Não Folha é importante, seria apreciado!

create table T(ID int identity primary key)
insert into T default values
insert into T default values

go

select cast(ID as varchar(10)) as ID
from T
where ID = 1

A consulta acima possui um aviso no plano de consulta.

<Warnings>
  <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(10),[xx].[dbo].[T].[ID],0)" />
</Warnings>

Por que tem o aviso?

Como uma conversão na lista de campos pode afetar a estimativa de cardinalidade?

Eu quero uma maneira de estabelecer quais colunas em um determinado banco de dados são unidas por meio de relacionamentos PK/FK. Eu posso retornar as informações PK/FK para uma determinada tabela via

SELECT *  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu 
WHERE EXISTS (
    SELECT tc.* 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc 
    WHERE tc.CONSTRAINT_CATALOG = 'MyDatabase'  
        AND tc.TABLE_NAME = 'MyTable'  
        /*AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'*/
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME);
GO

mas para um PK retornado de tal consulta, como faço para estabelecer o FK associado (supondo que haja um)?

Eu sei que você também pode obter as tabelas referenciadas via:

SELECT CONSTRAINT_NAME = name, 
       FOREIGN_SCHEMA = OBJECT_SCHEMA_NAME(parent_object_id), 
       FOREIGN_TABLE = OBJECT_NAME(parent_object_id), 
       REFERENCED_SCHEMA = OBJECT_SCHEMA_NAME(referenced_object_id), 
       REFERENCED_TABLE = OBJECT_NAME(referenced_object_id) 
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) = 'MyTable';
GO

mas estou lutando agora para obter as referências de coluna explícitas.

Estou criando um gerador de script para o QlikView. Para gerar o script, preciso das restrições e dos links associados. Eu preciso de todas as informações de restrição para qualquer coluna (se houver).

Eu quero construir uma classe de banco de dados que contenha todas as informações de um determinado banco de dados. Essa estrutura de classe database.table.column.constraintsserá usada para obter as correspondências entre diferentes colunas em PK/FKs.

Claramente algumas colunas terão apenas FKs e neste caso eu também quero recuperar as informações de PK da chave correspondente; alguns terão apenas PKs e aí eu quero o inverso. Alguns, claro, podem ter ambos.

Normalmente, crio guias de plano construindo primeiro uma consulta que usa o plano correto e copiando-a para a consulta semelhante que não usa. No entanto, isso às vezes é complicado, especialmente se a consulta não for exatamente a mesma. Qual é a maneira correta de criar guias de plano do zero?

O SQLKiwi mencionou a elaboração de planos no SSIS, existe uma maneira ou ferramenta útil para auxiliar na elaboração de um bom plano para o SQL Server?

A instância específica em questão é esta CTE: SQLFiddle

with cte(guid,other) as (
  select newid(),1 union all
  select newid(),2 union all
  select newid(),3)
select a.guid, a.other, b.guid guidb, b.other otherb
from cte a
cross join cte b
order by a.other, b.other;

Existe ALGUMA maneira de fazer com que o resultado apareça com exatamente 3 guids distintos e não mais? Espero poder responder melhor às perguntas no futuro, incluindo guias de plano com consultas do tipo CTE que são referenciadas várias vezes para superar algumas peculiaridades do SQL Server CTE.

Resumidamente
, quais fatores entram na seleção do otimizador de consulta do índice de uma exibição indexada?

Para mim, exibições indexadas parecem desafiar o que eu entendo sobre como o Optimizer escolhe índices. Já vi isso ser perguntado antes , mas o OP não foi muito bem recebido. Estou realmente procurando por guias , mas vou inventar um pseudo-exemplo e, em seguida, postar um exemplo real com muito DDL, saída, exemplos.

Suponha que eu esteja usando o Enterprise 2008+, entendawith(noexpand)

Pseudo Exemplo

Veja este pseudo exemplo: eu crio uma visão com 22 junções, 17 filtros e um pônei de circo que cruza um monte de tabelas de 10 milhões de linhas. Essa visão é cara (sim, com E maiúsculo) para se materializar. Vou SCHEMABIND e Indexar a exibição. Então um SELECT a,b FROM AnIndexedView WHERE theClusterKeyField < 84. Na lógica do Optimizer que me escapa, as junções subjacentes são executadas.

O resultado:

• Nenhuma dica: 4825 leituras para 720 linhas, 47 cpu em 76 ms e um custo estimado de subárvore de 0,30523.
• Com dica: 17 leituras, 720 linhas, 15 cpu em 4ms e um custo estimado de subárvore de 0,007253

Então, o que está acontecendo aqui? Eu tentei no Enterprise 2008, 2008-R2 e 2012. Por cada métrica que posso pensar em usar o índice da exibição é muito mais eficiente. Não tenho problema de detecção de parâmetro ou dados distorcidos, pois isso é ad hock.

Um exemplo real (longo)

A menos que você seja um pouco masoquista, provavelmente não precisa ou não quer ler esta parte.

A versão
Sim, empresa.

Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 10 de fevereiro de 2012 19:39:15 Copyright (c) Microsoft Corporation Enterprise Edition (64 bits) no Windows NT 6.2 (Build 9200: ) (Hypervisor)

A vista

CREATE VIEW dbo.TimelineMaterialized    WITH SCHEMABINDING
AS
SELECT  TM.TimelineID,
        TM.TimelineTypeID,
        TM.EmployeeID,
        TM.CreateUTC,
        CUL.CultureCode,
        CASE 
           WHEN TM.CustomerMessageID    > 0 THEN TM.CustomerMessageID
           WHEN TM.CustomerSessionID    > 0 THEN TM.CustomerSessionID
           WHEN TM.NewItemTagID         > 0 THEN TM.NewItemTagID
           WHEN TM.OutfitID             > 0 THEN TM.OutfitID
           WHEN TM.ProductTransactionID > 0 THEN TM.ProductTransactionID
           ELSE 0 END  As HrefId,
        CASE 
          WHEN TM.CustomerMessageID    > 0 THEN IsNull(C.Name, 'N/A')   
          WHEN TM.CustomerSessionID    > 0 THEN IsNull(C.Name, 'N/A')
          WHEN TM.NewItemTagID         > 0 THEN IsNull(NI.Title, 'N/A')
          WHEN TM.OutfitID             > 0 THEN IsNull(O.Name, 'N/A')
          WHEN TM.ProductTransactionID > 0 THEN IsNull(PT_PL.NameLocalized, 'N/A')
                 END as HrefText

FROM       dbo.Timeline TM
INNER JOIN dbo.CustomerSession    CS    ON TM.CustomerSessionID    = CS.CustomerSessionID
INNER JOIN dbo.CustomerMessage    CM    ON TM.CustomerMessageID    = CM.CustomerMessageID
INNER JOIN dbo.Outfit             O     ON PO.OutfitID             = O.OutfitID
INNER JOIN dbo.ProductTransaction PT    ON TM.ProductTransactionID = PT.ProductTransactionID
INNER JOIN dbo.Product            PT_P  ON PT.ProductID            = PT_P.ProductID
INNER JOIN dbo.ProductLang        PT_PL ON PT_P.ProductID          = PT_PL.ProductID
INNER JOIN dbo.Culture            CUL   ON PT_PL.CultureID         = CUL.CultureID
INNER JOIN dbo.NewsItemTag        NIT   ON TM.NewsItemTagID        = NIT.NewsItemTagID
INNER JOIN dbo.NewsItem           NI    ON NIT.NewsItemID          = NI.NewsItemID
INNER JOIN dbo.Customer           C     ON  C.CustomerID = CASE 
                                             WHEN TM.TimelineTypeID = 1 THEN CM.CustomerID 
                                             WHEN TM.TimelineTypeID = 5 THEN CS.CustomerID
                                             ELSE 0 END

WHERE        CUL.IsActive = 1

índice agrupado

CREATE UNIQUE CLUSTERED INDEX PK_TimelineMaterialized  ON 
                   TimelineMaterialized (EmployeeID, CreateUTC, CultureCode, TimelineID)

SQL de teste

-- NO HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *                 --yes yes, star is bad ...just a test example
FROM    TimelineMaterialized TM 
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

-- WITH HINT - - -  - - -  - - -  - - -  - - - 
SELECT  *               
FROM    TimelineMaterialized TM with(noexpand)
WHERE 
            TM.EmployeeID   = 2
        AND TM.CultureCode  = 'en-US'
        AND TM.CreateUTC    > '9/10/2012'
        AND TM.CreateUTC    < '9/11/2012'

Resultado = 11 Linhas de Saída

Saída do Profiler
As 4 primeiras linhas não contêm nenhuma dica. As 4 linhas inferiores estão usando a dica.

Planos de execução
GitHub Gist para ambos os planos de execução no formato SQLPlan

Nenhum plano de Hint Execution - por que não usar o índice clusterizado que lhe dei, Sr. SQL? Está agrupado nos 3 campos de filtro. Experimente, você pode gostar.

Plano simples ao usar uma dica.

Estou tendo dificuldade em encontrar explicações 'lay' de como os índices são armazenados em cache no PostgreSQL, então gostaria de uma verificação da realidade em qualquer uma ou todas essas suposições:

1. Índices PostgreSQL, como linhas, vivem no disco, mas podem ser armazenados em cache.
2. Um índice pode estar inteiramente no cache ou não estar.
3. Se é armazenado em cache ou não, depende da frequência com que é usado (conforme definido pelo planejador de consulta).
4. Por esta razão, a maioria dos índices 'sensatos' estarão no cache o tempo todo.
5. Os índices residem no mesmo cache (o buffer cache?) que as linhas e, portanto, o espaço de cache usado por um índice não está disponível para as linhas.

Antes de fazer isso, gostaria de deixar claro que empregar um índice parcial traz duas vantagens:

1. Reduzimos o tamanho do índice no cache, liberando mais espaço para as próprias linhas no cache.
2. Reduzimos o tamanho do B-Tree, resultando em uma resposta de consulta mais rápida.