All perguntas(dba)

Este é um desdobramento dos comentários da pergunta anterior:

• A consulta do Postgres 9.4.4 leva uma eternidade

Usando o PostgreSQL 9.4, sempre parece haver uma Recheck Cond:linha após as varreduras de índice de bitmap nos planos de consulta gerados por arquivos EXPLAIN.

Como na EXPLAINsaída da pergunta referenciada:

->  Bitmap Heap Scan on table_three  (cost=2446.92..19686.74 rows=8159 width=7)
      Recheck Cond: (("timestamp" > (now() - '30 days'::interval)) AND (client_id > 0))
      ->  BitmapAnd  (cost=2446.92..2446.92 rows=8159 width=0)
            ->  Bitmap Index Scan on table_one_timestamp_idx  (cost=0.00..1040.00 rows=79941 width=0)
                  Index Cond: ("timestamp" > (now() - '30 days'::interval))
            ->  Bitmap Index Scan on fki_table_three_client_id  (cost=0.00..1406.05 rows=107978 width=0)
                  Index Cond: (client_id > 0)

Ou na saída de EXPLAIN ANALYZEuma tabela enorme e simples (com muito pouco work_mem):

EXPLAIN ANALYZE SELECT * FROM aa WHERE a BETWEEN 100000 AND 200000;

Bitmap Heap Scan on aa  (cost=107.68..4818.05 rows=5000 width=4) (actual time=27.629..213.606 rows=100001 loops=1)
  Recheck Cond: ((a >= 100000) AND (a <= 200000))
  Rows Removed by Index Recheck: 758222
  Heap Blocks: exact=693 lossy=3732
  ->  Bitmap Index Scan on aai  (cost=0.00..106.43 rows=5000 width=0) (actual time=27.265..27.265 rows=100001 loops=1)
        Index Cond: ((a >= 100000) AND (a <= 200000))

Isso significa que as condições de índice devem ser verificadas uma segunda vez após uma varredura de índice de bitmap?
O que mais podemos aprender com a EXPLAINsaída?

Supondo que tenhamos uma tabela com quatro colunas (a,b,c,d)do mesmo tipo de dados.

É possível selecionar todos os valores distintos dentro dos dados nas colunas e devolvê-los como uma única coluna ou tenho que criar uma função para conseguir isso?

Preciso de ajuda para descobrir por que a seguinte T-SQLdeclaração retorna 1(verdadeiro):

SELECT IIF( 0 = '', 1, 0)

Acho que alguém mudou uma ANSIopção como SET ANSI_NULLSou outra coisa que está causando o comportamento.

Meu problema é que estou juntando alguns valores e no conjunto de linhas final tenho valores que são unidos por 0e ''valores, o que não está correto.

Digamos que eu tenha uma tabela Foocom colunas ID1, ID2e uma chave primária composta definida por ID2, ID1. (Atualmente, estou trabalhando com um produto System Center que possui várias tabelas definidas dessa maneira com as colunas de chave primária listadas na ordem oposta em que aparecem na definição da tabela.)

CREATE TABLE dbo.Foo(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
CONSTRAINT [PK_Foo] PRIMARY KEY CLUSTERED (ID2, ID1)
);
GO

-- Add a row and update stats so that histogram isn't empty
INSERT INTO Foo (ID1, ID2) VALUES (1,2);
UPDATE STATISTICS dbo.Foo;

A key_ordinalcoluna em sys.index_columnsmostra as colunas de índice na mesma ordem em que foram declaradas na chave primária composta:

SELECT t.name, i.name, c.column_id, c.name, ic.index_column_id, ic.key_ordinal
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
JOIN sys.columns AS c
ON ic.column_id = c.column_id
AND ic.[object_id] = c.[object_id]
WHERE t.name = 'Foo';

O histograma também mostra as estatísticas na mesma ordem:

DBCC SHOW_STATISTICS ('Foo',PK_Foo);

No entanto, sys.stats_columnsmostra as colunas listadas na ordem inversa ( ID1, ID2).

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc 
ON s.stats_id = sc.stats_id 
AND s.[object_id] = sc.[object_id] 
JOIN sys.columns AS c 
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o 
ON o.[object_id] = c.[object_id] 
WHERE o.name = 'Foo'
AND s.name = 'PK_Foo';

O Books Online diz que stats_column_idé um "ordinal baseado em 1 dentro do conjunto de colunas de estatísticas", então eu esperava que o valor 1 apontasse para a primeira coluna no objeto de estatísticas.

Isso é um bug sys.stats_columnsou um mal-entendido da minha parte?

Verifiquei que esse comportamento ocorre nas compilações atuais do SQL Server 2005, 2008, 2008 R2, 2012 e 2014.

sys.stats_columnsparece refletir a ordem dentro do objeto de estatísticas em outras situações, por exemplo:

CREATE TABLE dbo.Foo2(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
  ID3 int NULL,
  String VARCHAR(10) NULL,
CONSTRAINT [PK_Foo2] PRIMARY KEY CLUSTERED (ID2, ID1)
);

GO

INSERT INTO Foo2 (ID1, ID2, ID3, String) VALUES (1,2,3,'String');

CREATE STATISTICS ST_Test ON Foo2 (ID3, String);
CREATE STATISTICS ST_Test2 ON Foo2 (String, ID3);

DBCC SHOW_STATISTICS ('Foo2',ST_Test);
DBCC SHOW_STATISTICS ('Foo2',ST_Test2);


SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc 
ON s.stats_id = sc.stats_id 
AND s.[object_id] = sc.[object_id] 
JOIN sys.columns AS c 
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o 
ON o.[object_id] = c.[object_id] 
WHERE o.name = 'Foo2'
AND s.name LIKE 'ST_Test%';

Aqui está outro exemplo onde sys.stats_columnsparece retornar os dados corretos, desta vez para estatísticas em um índice:

--drop table dbo.Foo3
CREATE TABLE dbo.Foo3(
  ID1 int NOT NULL,
  ID2 int NOT NULL,
  ID3 int NULL,
  String VARCHAR(10) NULL,
CONSTRAINT [PK_Foo3] PRIMARY KEY CLUSTERED (ID2, ID1)
);

GO

INSERT INTO Foo3 (ID1, ID2, ID3, String) VALUES (1,2,3,'String');
UPDATE STATISTICS Foo3;

CREATE INDEX IX_Test ON Foo3 (ID3, String);
CREATE INDEX IX_Test2 ON Foo3 (String, ID3);

DBCC SHOW_STATISTICS ('Foo3',IX_Test);
DBCC SHOW_STATISTICS ('Foo3',IX_Test2);

SELECT s.name, sc.stats_column_id, c.name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc 
ON s.stats_id = sc.stats_id 
AND s.[object_id] = sc.[object_id] 
JOIN sys.columns AS c 
ON c.[object_id] = s.[object_id]
AND c.column_id = sc.column_id
JOIN sys.objects AS o 
ON o.[object_id] = c.[object_id] 
WHERE o.name = 'Foo3'
AND s.name LIKE 'IX_Test%';

Eu estou fazendo a coisa certa...?

Eu tenho uma função que retorna dinheiro...

CREATE FUNCTION functionName( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )  RETURNS money AS BEGIN

  DECLARE @v_dint money   set @v_dint = computation_here
     set @v_dint = round(@v_dint, 2)

  RETURN @v_dint    
END 
GO 
Grant execute on functionName to another_user 
Go

Estou apenas querendo saber se isso é possível ser convertido para iTVF?

Eu tentei fazer isso, mas recebi um erro:

CREATE FUNCTION functionName ( @a_principal money, @a_from_date
  datetime, @a_to_date datetime, @a_rate float )  
RETURNS TABLE AS 
RETURN SELECT returnMoney = computation_here  
GO  
Grant execute on functionName to another_user  Go

ERRO:

Msg 4606, Level 16, State 1, Line 2 Privilégio concedido ou revogado EXECUTE não é compatível com o objeto.

Esta função é usada assim:

update table_name set interest = functionName(col1,col2...) where...

Desde já, obrigado!

De acordo com o oficial da Microsoft, BOL DENSE_RANK não é determinístico ( RANK() ). Mas de acordo com Ranking Functions de Itzik Ben-Gan "... as funções RANK() e DENSE_RANK() são sempre determinísticas". Quem está certo?

O que descobri até agora: Definição da Microsoft "As funções determinísticas sempre retornam o mesmo resultado sempre que são chamadas com um conjunto específico de valores de entrada e recebem o mesmo estado do banco de dados."

Assim, nas tabelas de teoria dos conjuntos Empregados

Employee            Salary
Sue Right            1.00
Robin Page           1.00
Phil Factor          1.00

e Empregados2

Employee            Salary
Phil Factor          1.00
Sue Right            1.00
Robin Page           1.00

são os mesmos. Mas as funções de classificação retornam valores diferentes:

    CREATE TABLE [dbo].[Employees](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[Employees2](
    --[ID] [int] IDENTITY(1,1) NOT NULL,
    [Employee] [varchar](150) NOT NULL,
    [Salary] [smallmoney] NULL,
) ON [PRIMARY]

INSERT INTO [dbo].[Employees]
([Employee] ,[Salary])
VALUES
('Sue Right', 1)
, ('Robin Page', 1)
,('Phil Factor', 1 )
GO
INSERT INTO [dbo].[Employees2]
([Employee] ,[Salary])
VALUES
('Phil Factor', 1 )
,('Sue Right', 1)
,('Robin Page', 1)
GO
SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees

SELECT RANK() OVER ( ORDER BY Salary) AS [Rank]
, DENSE_RANK() OVER (ORDER BY Salary ) AS [Dense_rank]
, [Employee]
FROM
dbo.Employees2

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees

SELECT NTILE(3) OVER ( ORDER BY SALARY )
, [Employee]
FROM
dbo.Employees2

Estou tentando melhorar o desempenho da seguinte consulta:

        UPDATE  [#TempTable]
        SET     Received = r.Number
        FROM    [#TempTable] 
        INNER JOIN (SELECT  AgentID,
                            RuleID,
                            COUNT(DISTINCT (GroupId)) Number
                    FROM    [#TempTable]
                    WHERE   Passed = 1
                    GROUP BY AgentID,
                            RuleID
                   ) r ON r.RuleID = [#TempTable].RuleID AND
                          r.AgentID = [#TempTable].AgentID                            

Atualmente, com meus dados de teste, leva cerca de um minuto. Eu tenho uma quantidade limitada de entrada em alterações no procedimento armazenado geral em que essa consulta reside, mas provavelmente posso fazer com que eles modifiquem essa consulta. Ou adicione um índice. Tentei adicionar o seguinte índice:

CREATE CLUSTERED INDEX ix_test ON #TempTable(AgentID, RuleId, GroupId, Passed)

E na verdade dobrou a quantidade de tempo que a consulta leva. Eu recebo o mesmo efeito com um índice NON-CLUSTERED.

Eu tentei reescrever da seguinte forma sem nenhum efeito.

        WITH r AS (SELECT  AgentID,
                            RuleID,
                            COUNT(DISTINCT (GroupId)) Number
                    FROM    [#TempTable]
                    WHERE   Passed = 1
                    GROUP BY AgentID,
                            RuleID
            ) 
        UPDATE  [#TempTable]
        SET     Received = r.Number
        FROM    [#TempTable] 
        INNER JOIN r 
            ON r.RuleID = [#TempTable].RuleID AND
               r.AgentID = [#TempTable].AgentID                            

Em seguida, tentei usar uma função de janela como esta.

        UPDATE  [#TempTable]
        SET     Received = COUNT(DISTINCT (CASE WHEN Passed=1 THEN GroupId ELSE NULL END)) 
                    OVER (PARTITION BY AgentId, RuleId)
        FROM    [#TempTable] 

Neste ponto eu comecei a receber o erro

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.

Então eu tenho duas perguntas. Primeiro você não pode fazer um COUNT DISTINCT com a cláusula OVER ou eu apenas escrevi incorretamente? E segundo, alguém pode sugerir uma melhoria que eu ainda não tenha tentado? Para sua informação, esta é uma instância do SQL Server 2008 R2 Enterprise.

EDIT: Aqui está um link para o plano de execução original. Também devo observar que meu grande problema é que essa consulta está sendo executada 30 a 50 vezes.

https://onedrive.live.com/redir?resid=4C359AF42063BD98%21772

EDIT2: Aqui está o loop completo em que a instrução está conforme solicitado nos comentários. Estou verificando com a pessoa que trabalha com isso regularmente quanto ao propósito do loop.

DECLARE @Counting INT              
SELECT  @Counting = 1              

--  BEGIN:  Cascading Rule check --           
WHILE @Counting <= 30              
    BEGIN      

        UPDATE  w1
        SET     Passed = 1
        FROM    [#TempTable] w1,
                [#TempTable] w3
        WHERE   w3.AgentID = w1.AgentID AND
                w3.RuleID = w1.CascadeRuleID AND
                w3.RulePassed = 1 AND
                w1.Passed = 0 AND
                w1.NotFlag = 0      

        UPDATE  w1
        SET     Passed = 1
        FROM    [#TempTable] w1,
                [#TempTable] w3
        WHERE   w3.AgentID = w1.AgentID AND
                w3.RuleID = w1.CascadeRuleID AND
                w3.RulePassed = 0 AND
                w1.Passed = 0 AND
                w1.NotFlag = 1        

        UPDATE  [#TempTable]
        SET     Received = r.Number
        FROM    [#TempTable] 
        INNER JOIN (SELECT  AgentID,
                            RuleID,
                            COUNT(DISTINCT (GroupID)) Number
                    FROM    [#TempTable]
                    WHERE   Passed = 1
                    GROUP BY AgentID,
                            RuleID
                   ) r ON r.RuleID = [#TempTable].RuleID AND
                          r.AgentID = [#TempTable].AgentID                            

        UPDATE  [#TempTable]
        SET     RulePassed = 1
        WHERE   TotalNeeded = Received              

        SELECT  @Counting = @Counting + 1              
    END

Para automatizar o processo de backup de um dos meus bancos de dados MySQL, gostaria de comparar a estrutura de duas tabelas (versão atual vs versão antiga).

Você consegue pensar em uma consulta que possa comparar duas tabelas?

Aqui estão algumas tabelas de exemplo que você pode comparar.

CREATE TABLE product_today
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_yesterday
(
  pname VARCHAR(150),
  price int,
  PRIMARY KEY (pname)
);

CREATE TABLE product_2days_back
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

As duas primeiras tabelas têm estruturas idênticas. A última é diferente. Eu só preciso saber se duas tabelas têm estruturas diferentes ou não. Eu não estou interessado em como eles diferem.

Ao fazer o backup do banco de dados, gostaria de usar a opção " Backup set will Expire: " para excluir/substituir backups de banco de dados antigos. Não estou conseguindo usar esta opção.

Qualquer conselho sobre como usar as opções "O conjunto de backup expirará:" ao criar o plano de manutenção será apreciado.

Hoje Seven Databases in Seven Weeks me apresentou aos índices por operador.

Você pode indexar strings para padrões correspondentes às consultas anteriores criando um text_pattern_opsíndice de classe de operador, desde que os valores sejam indexados em letras minúsculas.

CREATE INDEX moves_title_pattern ON movies (
    (lower(title) text_pattern_ops);

Usamos o text_pattern_opsporque o título é do tipo texto. Se você precisar indexar varchars, chars ou nomes, use as operações relacionadas: varchar_pattern_ops, bpchar_pattern_opse name_pattern_ops.

Achei o exemplo muito confuso. Por que é útil fazer isso?

Se a coluna for do tipo texto, os outros tipos (varchar, char, name) não seriam convertidos em texto antes de serem usados ​​como um valor de pesquisa?

Como esse índice se comporta de maneira diferente de um que usa o operador padrão?

CREATE INDEX moves_title_pattern ON movies (lower(title));