cubetwo1729's questions

Suponha que eu tenha os seguintes dados XML, onde

• <root>pode ter um número arbitrário de <child>filhos
• <child>pode ter um número arbitrário de <grandchild>filhos
• <grandchild>pode ter um número arbitrário de <greatgrandchild>filhos
• <child>e <grandchild>pode ter um número arbitrário de <parameter>filhos
• cada <parameter>um tem um <name>e um <value>filho
• a profundidade pára no bisneto (não há necessidade de profundidades arbitrárias)

Por exemplo,

<root name="root">
  <child name="a">
    <grandchild name="a1">
      <parameter>
        <name>param1</name>
        <value>ABC123</value>
      </parameter>
      <parameter>
        <name>param2</name>
        <value>CBC</value>
      </parameter>
      <greatgrandchild name="a1a">
        <parameter>
          <name>paramA</name>
          <value>ABC</value>
        </parameter>
        <parameter>
          <name>paramB</name>
          <value>DBC</value>
        </parameter>
      </greatgrandchild>
    </grandchild>
  </child>
</root>

O que preciso fazer é selecionar o parâmetro com um valor contendo "_BC", onde _ é um caractere que não é A.

tenho a seguinte consulta

SELECT
    TestId,
    [root],
    child,
    grandchild,
    NULL AS greatgrandchild,
    parameter
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            parameter.d.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.d.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
            CROSS APPLY [root].a.nodes('child') AS child(b)
            CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
            CROSS APPLY grandchild.c.nodes('parameter') AS parameter(d)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'
UNION ALL
SELECT
    TestId,
    [root],
    child,
    grandchild,
    greatgrandchild,
    parameter
FROM (
        SELECT
            TestId,
            [root].a.value('@name', 'varchar(max)') AS [root],
            child.b.value('@name', 'varchar(max)') AS child,
            grandchild.c.value('@name', 'varchar(max)') AS grandchild,
            greatgrandchild.d.value('@name', 'varchar(max)') AS greatgrandchild,
            parameter.e.value('(name)[1]', 'varchar(max)') AS parameter,
            parameter.e.value('(value)[1]', 'varchar(max)') AS parameter_value
        FROM
            dbo.TestTable
            CROSS APPLY TestData.nodes('/root') AS [root](a)
            CROSS APPLY [root].a.nodes('child') AS child(b)
            CROSS APPLY child.b.nodes('grandchild') AS grandchild(c)
            CROSS APPLY grandchild.c.nodes('greatgrandchild') AS greatgrandchild(d)
            CROSS APPLY greatgrandchild.d.nodes('parameter') AS parameter(e)
    ) t
WHERE
    parameter_value LIKE '%[^Aa]BC%'

Por exemplo, para os seguintes dados de teste:

CREATE TABLE TestTable (
    TestId INT PRIMARY KEY,
    TestData XML NOT NULL
);

INSERT INTO dbo.TestTable (TestId, TestData)
VALUES (1, '<root name="root">
  <child name="a">
    <grandchild name="a1">
      <parameter>
        <name>param1</name>
        <value>ABC123</value>
      </parameter>
      <parameter>
        <name>param2</name>
        <value>CBC</value>
      </parameter>
      <greatgrandchild name="a1a">
        <parameter>
          <name>paramA</name>
          <value>ABC</value>
        </parameter>
        <parameter>
          <name>paramB</name>
          <value>DBC</value>
        </parameter>
      </greatgrandchild>
    </grandchild>
  </child>
</root>');

o seguinte conjunto de resultados deve ser retornado:

TestId root child grandchild greatgrandchild parameter
------------------------------------------------------
1      root a     a1         NULL            param2
1      root a     a1         a1a             paramB

Estou me perguntando se minha consulta SELECT pode ser otimizada

Em uma tabela de 400 linhas com dados XML de 20 KB cada linha, a consulta SELECT leva 40 minutos. O layout XML não pode ser alterado.

Eu tenho a seguinte tabela no SQL Server 2012:

CREATE TABLE [MyTable] (
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT NEWID(),
    [MyGroup] INT NOT NULL
);

Eu quero gerar uma tabela do formulário

    | MyGroup | Count
Max |         |
Min |         |

Por exemplo, se eu tiver 30linhas em MyTable onde MyGroup = 1, 20linhas onde MyGroup = 2, e 10linhas onde MyGroup = 3, então

    | MyGroup | Count
Max | 1       | 30
Min | 3       | 10

Que tipo de consulta produziria essas informações?

Tenho o seguinte esquema (nomes alterados), que não posso alterar:

CREATE TABLE MyTable (
    Id INT NOT NULL PRIMARY KEY,
    ParentId INT NOT NULL
);

ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);

Ou seja, cada registro é filho de outro registro. Se um registro ParentIdfor igual ao seu Id, o registro será considerado um nó raiz.

Eu quero executar a consulta que encontrará todas as referências circulares. Por exemplo, com os dados

INSERT INTO MyTable (Id, ParentId) VALUES
    (0, 0),
    (1, 0),
    (2, 4),
    (3, 2),
    (4, 3);

a consulta deve retornar

Id | Cycle
2  | 2 < 4 < 3 < 2
3  | 3 < 2 < 4 < 3
4  | 4 < 3 < 2 < 4

Escrevi a seguinte consulta para o SQL Server 2008 R2 e gostaria de saber se essa consulta pode ser melhorada:

IF OBJECT_ID(N'tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results (Id INT, HasParentalCycle BIT, Cycle VARCHAR(MAX));

DECLARE @i INT,
    @j INT,
    @flag BIT,
    @isRoot BIT,
    @ids VARCHAR(MAX);

DECLARE MyCursor CURSOR FAST_FORWARD FOR
    SELECT Id
    FROM MyTable;

OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @i;
WHILE @@FETCH_STATUS = 0
BEGIN
    IF OBJECT_ID(N'tempdb..#Parents') IS NOT NULL DROP TABLE #Parents;
    CREATE TABLE #Parents (Id INT);

    SET @ids = NULL;
    SET @isRoot = 0;
    SET @flag = 0;
    SET @j = @i;
    INSERT INTO #Parents (Id) VALUES (@j);

    WHILE (1=1)
    BEGIN
        SELECT
            @j = ParentId,
            @isRoot = CASE WHEN ParentId = Id THEN 1 ELSE 0 END
        FROM MyTable
        WHERE Id = @j;

        IF (@isRoot = 1)
        BEGIN
            SET @flag = 0;
            BREAK;
        END        

        IF EXISTS (SELECT 1 FROM #Parents WHERE Id = @j)
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
            SET @flag = 1;
            SELECT @ids = COALESCE(@ids + ' < ', '') + CAST(Id AS VARCHAR) FROM #Parents;
            BREAK;
        END
        ELSE
        BEGIN
            INSERT INTO #Parents (Id) VALUES (@j);
        END        
    END

    INSERT INTO #Results (Id, HasParentalCycle, Cycle) VALUES (@i, @flag, @ids);

    FETCH NEXT FROM MyCursor INTO @i;
END
CLOSE MyCursor;
DEALLOCATE MyCursor;

SELECT Id, Cycle
FROM #Results
WHERE HasParentalCycle = 1;

Tenho as seguintes tabelas:

CREATE TABLE Revision (
    RevisionId INT PRIMARY KEY IDENTITY,
    UserName NVARCHAR(256) NOT NULL,
    DateTime DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE TABLE MyEntity (
    MyEntityId INT NOT NULL,
    RevisionId INT NOT NULL FOREIGN KEY REFERENCES Revision (RevisionId),
    Deleted BIT NOT NULL DEFAULT 0,
    Name NVARCHAR(256) NOT NULL,
    Body NVARCHAR(MAX) NOT NULL,
    PRIMARY KEY (MyEntityId, RevisionId)
)

Ou seja, a tabela MyEntity contém todas as alterações em uma entidade. Quando uma entidade é criada, modificada ou excluída, um novo registro é inserido em MyEntity e em Revision, para que todo o histórico seja rastreado.

Gostaria de uma view para ter a última versão de cada entidade:

CREATE VIEW MyEntityLatest WITH SCHEMABINDING AS
SELECT
    Latest.MyEntityId,
    Latest.Name,
    Latest.Body
FROM dbo.MyEntity
INNER JOIN (
    SELECT
        MyEntityId,
        RevisionId,
        Name,
        Body,
        RANK() OVER (PARTITION BY MyEntityId ORDER BY RevisionId DESC) AS RevisionIdDesc
    FROM MyEntity
    WHERE Deleted = 0
) AS Latest
    ON dbo.MyEntity.MyEntityId = Latest.MyEntityId
    AND dbo.MyEntity.RevisionId = Latest.RevisionId
WHERE Latest.RevisionIdDesc = 1

No entanto, gostaria de criar um índice exclusivo (e, portanto, restrição) de modo que o Nome seja exclusivo apenas para a revisão mais recente. Não consigo criar um índice na exibição, devido à subconsulta na exibição.

Como posso fazer isso?

Quero ter um relacionamento um-para-muitos em que, para cada pai, um ou zero dos filhos seja marcado como "favorito". No entanto, nem todos os pais terão um filho. (Pense nos pais como perguntas neste site, filhos como respostas e favoritos como a resposta aceita.) Por exemplo,

TableA
    Id            INT PRIMARY KEY

TableB
    Id            INT PRIMARY KEY
    Parent        INT NOT NULL FOREIGN KEY REFERENCES TableA.Id

A meu ver, posso adicionar a seguinte coluna à TabelaA:

    FavoriteChild INT NULL FOREIGN KEY REFERENCES TableB.Id

ou a seguinte coluna para TableB:

    IsFavorite    BIT NOT NULL

O problema com a primeira abordagem é que ela introduz uma chave estrangeira anulável, que, pelo que entendi, não está na forma normalizada. O problema com a segunda abordagem é que mais trabalho precisa ser feito para garantir que no máximo um filho seja o favorito.

Que tipo de critério devo usar para determinar qual abordagem usar? Ou existem outras abordagens que não estou considerando?

Estou usando o SQLServer 2012.