Jez's questions

Eu tenho uma instância do SQL Server 2022 em execução em um contêiner do Docker no Linux. Meu entendimento é que os contêineres do Docker podem simplesmente expandir seu uso de disco para preencher todo o espaço de volume disponível. O espaço no volume é de cerca de 10 GB:

$ sudo df -h /var/lib/docker/
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       47G   34G   10G  78% /

No entanto, quando entro no SSMS e vejo as propriedades do banco de dados, ele diz "Tamanho do banco de dados 80,00 MB, espaço disponível 1,78 MB". Por que diz que apenas 1,78 MB está disponível? Meu banco de dados ainda poderá usar todos esses 10 GB na partição?

Estou em uma situação em que estou recebendo uma lista (potencialmente bastante longa) de "entidades de correspondência", cada uma contendo dados do usuário para correspondência, juntamente com um ID exclusivo para essas informações de correspondência. Os dados reais dos usuários correspondentes, juntamente com o ID exclusivo dessa correspondência, precisam ser retornados da minha consulta SQL. Então, digamos que eu tenha recebido duas entidades para tentar fazer a correspondência de usuários, ambas tentando fazer a correspondência de números de telefone com o número de telefone de um usuário; Eu poderia corresponder a qualquer usuário associado às entidades enviadas junto com o ID exclusivo da "entidade de correspondência" usando uma união como esta ( client_handleé o ID exclusivo enviado):

SELECT
    [client_handle] = 'axtwe-wasst',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7747 122123', '+44 7904 223323')

UNION

SELECT
    [client_handle] = 'zjfft-albwq',
    [user_id],
    [email],
    [mobile_no],
    [firstname],
    [surname]
FROM
    [dbo].[vAPP_UsersActive]
WHERE
    [mobile_no] in ('+44 7758 444111', '+44 7758 444222', '+44 7758 444333')

O problema com esse método é que ele pode resultar em um número muito grande de UNIONs se um grande número de entidades de correspondência forem enviadas para mim. 1000 entidades de correspondência enviadas resultariam em 999 UNIONs. Isso é realmente um problema em termos de desempenho e existe uma maneira melhor de alcançar o resultado que desejo? Como alternativa, eu poderia simplesmente percorrer cada entidade de correspondência enviada e executar uma consulta para corresponder a cada uma, mas teria 1.000 consultas separadas se 1.000 entidades de correspondência fossem enviadas, o que parece ainda pior.

Tenho a seguinte tabela:

CREATE TABLE [dbo].[MP_Notification_Audit](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [type] [int] NOT NULL,
    [source_user_id] [bigint] NOT NULL,
    [target_user_id] [bigint] NOT NULL,
    [discussion_id] [bigint] NULL,
    [discussion_comment_id] [bigint] NULL,
    [discussion_media_id] [bigint] NULL,
    [patient_id] [bigint] NULL,
    [task_id] [bigint] NULL,
    [date_created] [datetimeoffset](7) NOT NULL,
    [clicked] [bit] NULL,
    [date_clicked] [datetimeoffset](7) NULL,
    [title] [nvarchar](max) NULL,
    [body] [nvarchar](max) NULL,
 CONSTRAINT [PK_MP_Notification_Audit] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MP_Notification_Audit] ADD  CONSTRAINT [DF_MP_Notification_Audit_date_created]  DEFAULT (sysdatetimeoffset()) FOR [date_created]
GO

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUserDateCreated] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC,
    [date_created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Existem mais de 10.000 linhas na tabela com um [target_user_id]de 100017.

Quando executo a seguinte consulta:

SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 9200 ROWS
FETCH NEXT 10 ROWS ONLY

... Recebo o seguinte plano de execução real:

Por que o SQL Server precisou fazer 9210 em vez de 10 pesquisas de chave em cluster? O índice [IX_MP_Notification_Audit_TargetUserDateCreated]deveria permitir que ele descobrisse os 10 RIDs necessários para recuperar para obter [patient_id]e fazer apenas 10 pesquisas de chave em cluster, certo?

Eu também descobri um comportamento mais estranho - parece que o SQL Server 'puni' você por não selecionar colunas não indexáveis. Se em vez disso OFFSET, 10.000 linhas, recebo o seguinte plano de execução:

SELECT
    [target_user_id], [patient_id]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

... com a recomendação de criar um índice que inclua [patient_id], e uma varredura de índice clusterizado ineficiente para toda a tabela. O tempo gasto foi de 0,126s, no entanto, aparentemente, isso poderia ter sido muito melhor porque quando adiciono a coluna não indexável [title]à consulta, recebo isso:

SELECT
    [target_user_id], [patient_id], [title]
FROM
    [dbo].[MP_Notification_Audit]
WHERE
    [target_user_id] = 100017
ORDER BY
    [date_created] ASC
OFFSET 10000 ROWS
FETCH NEXT 10 ROWS ONLY

... e o índice não clusterizado ainda é usado, o tempo gasto é de apenas 0,032s. O SQL Server basicamente diz "você poderia ter criado um índice para fazer isso com mais eficiência, então nem vamos usar o índice que você tem e vamos fazer a pesquisa de forma ineficiente para puni-lo", ou estou me falta alguma coisa?

Criei uma tabela com um PK não clusterizado (isso ocorre por design) e um índice não clusterizado adicional na coluna que estou filtrando com uma WHEREcláusula ( [target_user_id]):

CREATE TABLE [dbo].[MP_Notification_Audit] (
    [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,
    [type]                  INT                NOT NULL,
    [source_user_id]        BIGINT             NOT NULL,
    [target_user_id]        BIGINT             NOT NULL,
    [discussion_id]         BIGINT             NULL,
    [discussion_comment_id] BIGINT             NULL,
    [discussion_media_id]   BIGINT             NULL,
    [patient_id]            BIGINT             NULL,
    [task_id]               BIGINT             NULL,
    [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,
    [clicked]               BIT                NULL,
    [date_clicked]          DATETIMEOFFSET (7) NULL,
    [title]                 NVARCHAR (MAX)     NULL,
    [body]                  NVARCHAR (MAX)     NULL,
    CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC)
);

[...]

CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit]
(
    [target_user_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Esta tabela possui cerca de 11.700 linhas de dados, portanto deve ser suficiente para acionar o uso de índices com WHEREcláusulas. Se eu SELECTapenas a coluna que estou filtrando, apenas o índice é usado e 133 linhas correspondentes são lidas - uma verificação somente de índice:

SELECT [target_user_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

No entanto, assim que eu adiciono uma coluna extra ao SELECT, o índice é ignorado e uma varredura de tabela com um predicado é feita para obter o resultado, lendo mais de 11.700 linhas:

SELECT [target_user_id], [patient_id]
  FROM [TestDb].[dbo].[MP_Notification_Audit]
  WHERE [target_user_id] = 100017

Por que está ignorando meu índice nesta segunda consulta? Eu teria pensado que ainda seria mais eficiente usar o índice para chegar a 133 RIDs e, em seguida, consultar os dados de linha extras necessários, do que percorrer todas as linhas da tabela com um predicado? Eu sei que posso adicionar colunas ao índice com INCLUDEos campos extras necessários na SELECTcláusula para fazê-lo usar o índice novamente, mas estou interessado em saber por que ele ainda não usa o índice nesse caso.

Eu tenho uma tabela que é auto-referenciada:

CREATE TABLE [dbo].[TestTable] (
    [id] [bigint] IDENTITY(100000,1) NOT NULL,
    [referenced_id] [bigint] NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (
        [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD CONSTRAINT [FK_ReferencedId] FOREIGN KEY ([referenced_id])
    REFERENCES [dbo].[TestTable] ([id])
GO

ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_ReferencedId]
GO

Digamos que eu tenha várias linhas que fazem referência umas às outras:

id     | referenced_id
-------|--------------
100023 | 100024
100024 | 100023
100025 | 100026
100026 | 100023

Se eu tentar e DELETEa linha WHERE [id] = 100023, o FK será violado porque 100024e 100026referenciar essa linha e DELETEfalhará. No entanto, se eu apenas DELETE FROM [dbo].[TestTable], parece funcionar e excluir com êxito todas as linhas. Portanto, o SQL Server só parece estar verificando a restrição FK depois que todas as linhas a serem excluídas em uma única DELETEinstrução foram excluídas, em vez de entre cada exclusão de linha.

Posso confiar nesse comportamento, ou DELETEàs vezes isso pode falhar?

Eu tenho 3 tabelas: Room, Conference, e Participant. Roomtem muitos Conferences, e Conferencetem muitos Participants. Eu preciso que minha consulta exiba os campos de Room, bem como o número de associados Conferencesque ele possui e a soma do número de Participants associados que cada um Conferencepossui. Aqui está uma versão reduzida da SELECTconsulta que escrevi para obter essas informações; primeiro, selecionei o ID da sala:

SELECT TOP(1000)
  rm.[Id]
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

Isso foi muito rápido, pois o SQL Server conseguiu extrair os dados Roome praticamente ignorar a subconsulta (consulte Avaliação 1 - Avaliação 4 na imagem abaixo). Em seguida, adicionei no ConferenceNamecampo da subconsulta, bem como uma contagem do número de conferências por sala:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName])
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

Isso desacelerou um pouco a consulta, por um fator de cerca de 100 (veja Teste 5 - Teste 7 na imagem abaixo). Em seguida, adicionei a contagem de participantes da subconsulta, o que significa que havia 2 níveis de funções agregadas sendo usadas:

SELECT TOP(1000)
  rm.[Id],
  COUNT(confData.[ConferenceId]) AS CalcRoomConferenceCount,
  MIN(confData.[ConferenceName]),
  SUM(confData.[CalcConferenceParticipantCount]) AS CalcRoomParticipantCount
FROM
  [Room] rm
LEFT JOIN (
  SELECT
    conf.[Id] AS [ConferenceId],
    MIN(conf.[Name]) AS [ConferenceName],
    MIN(conf.[RoomId]) AS [RoomId],
    COUNT(part.[Id]) AS CalcConferenceParticipantCount
  FROM
    [Conference] conf
  LEFT JOIN
    [Participant] part on part.[ConferenceId] = conf.[Id]
  GROUP BY
    conf.[Id]
  ) confData ON confData.[RoomId] = rm.[Id]
GROUP BY
  rm.[Id]

Isso desacelerou ainda mais a consulta por um fator de cerca de 4 (veja Ensaio 8 - Ensaio 10 na imagem abaixo). Aqui estão as estatísticas do cliente com dados sobre os 10 testes:

Aqui está o plano de consulta da consulta lenta: https://www.brentozar.com/pastetheplan/?id=SJpyeec5Q

Existe uma maneira de tornar esse tipo de consulta - onde calculo uma agregação da agregação de uma subconsulta - mais eficiente?

É possível no SQL Server determinar se a autenticação de modo misto está habilitada sem fazer logon no SQL Server?

Às vezes, uma consulta demora muito e você não quer ter que executar tudo. Este é exatamente o tipo de tempo que você pode querer ver o Plano de Execução Real para descobrir por que a consulta é ineficiente. No entanto, notei que quando você diz ao SSMS para incluir o plano de execução real nos resultados, ele só o faz quando a consulta é concluída com sucesso e não é cancelada! Por que é isso? Presumivelmente, o plano deve ser gerado primeiro, para que possa ser enviado primeiro pelo SQL Server e exibido mesmo para uma consulta cancelada. Eu sei sobre o plano de execução estimado, mas não é o mesmo.

Estou PIVOTcriando uma tabela de forma que os nomes das colunas na tabela final sejam baseados no "nome da organização" na tabela de origem. O problema é que o "nome da organização" é controlado por dados e pode ser qualquer conjunto de valores; Eu uso SQL dinâmico para construir a cláusula PIVOT's . INIsso significa que o valor pode corresponder a outro nome de coluna que tenho, como a RecordIdque estou girando. Então eu tenho algo assim:

.----------------------------------------------------------------------.
| RecordId | OrganizationFoo | OrganizationBar | OrganizationBaz | ... |
|----------|-----------------|-----------------|-----------------|-----|
| 123      | 182             | 76              | 56              | ... |
| 234      | 846             | 0               | 182             | ... |
| 345      | 46              | 2               | 951             | ... |
...

O problema é que tudo isso é orientado a dados, portanto, seria bom que o nome de uma organização fosse RecordId, o que causaria a geração de uma consulta SQL inválida devido a colunas duplicadas. Existe uma maneira de prefixar automaticamente algo para cada coluna na PIVOTsaída?

Se não, as únicas 3 maneiras que posso pensar em fazer isso são:

• Em vez disso, selecione CONCAT('Prefix_', [OrgName]), mas isso provavelmente reduzirá a eficiência porque [OrgName]é indexado.
• Crie uma tabela duplicada para aquela que contém [OrgName], e cada vez que esta consulta for executada, limpe-a e insira o conteúdo da tabela original, mas com [OrgName]o prefixo.
• Apenas armazene sempre [OrgName]com o prefixo em primeiro lugar.

Alguma opção mais limpa/melhor?

Eu tenho uma operação que está indo muito devagar, provavelmente porque meu CROSS JOINprimeiro cria uma tonelada de linhas para o SQL Server trabalhar, que são posteriormente GROUPeditadas e PIVOTtransformadas em muito menos linhas e um monte de colunas. O objetivo é obter as "vezes em que cada registro foi baixado" por "nome exclusivo da organização", onde os nomes exclusivos da organização são as OrganizationNameentradas exclusivas na AdamUsertabela e as vezes baixadas é o número de entradas associadas na MaintenanceJobHistorytabela. Por fim, Recordé unido a cada linha para gerar mais informações sobre esse registro. Aqui está a consulta:

SELECT *
FROM (
    SELECT
        rec.[Id]                                      AS RecordId,
        CONCAT('Downloads_', usr.[OrganizationName])  AS OrganizationName,
        COUNT(hist.[Id])                              AS TimesDownloaded  -- To be aggregated by PIVOT
    FROM (
        SELECT
            innerRec.[Id]
        FROM
            [dbo].[Record] innerRec
        INNER JOIN
            [dbo].[RecordClassificationLink] innerLnk ON innerLnk.[RecordId] = innerRec.[Id]
        -- WHERE (classification ID is foo or bar), for optional classification filtering
        GROUP BY
            innerRec.[Id]
        -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications), for optional classification filtering
    ) rec
    CROSS JOIN [dbo].[AdamUser] usr
    LEFT JOIN (
        SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > '2016-01-01 12:00:00' AND [CreatedOn] < '2016-12-01 12:00:00'
    ) hist ON hist.[AccessingUser] = usr.[Name] AND hist.[RecordId] = rec.[Id]
    GROUP BY
        rec.[Id], usr.[OrganizationName]
) srcTable
PIVOT  -- Pivot around columns outside aggregation fn, eg. heading column [OrganizationName] & all other columns: [RecordId]
(
    MIN(srcTable.[TimesDownloaded]) FOR [OrganizationName] IN (...~200 columns...)
) pivotTable
INNER JOIN [dbo].[Record] outerRec ON outerRec.[Id] = pivotTable.[RecordId]

E aqui está o plano de execução:

https://www.brentozar.com/pastetheplan/?id=ry8tXM3mg

Ele me fornece a saída correta, mas notei que a operação Sort antes do Stream Aggregate está demorando muito e se espalha em arquivos tempdb. Pode ser por isso que a consulta leva ~ 5 minutos com as ~ 200 colunas que tenho para o conjunto de dados normal, enquanto meu conjunto de dados de teste com 6 colunas PIVOTleva apenas meio minuto. Em termos de quantas linhas está lidando, as duas tabelas sendo CROSS JOINed ( Recorde AdamUser) têm 38397 e 1017 linhas, respectivamente.

Existe uma maneira de acelerar isso ou é necessário demorar tanto para o número de linhas e colunas que tenho?

Quero construir uma PIVOTtabela, mas posso ter que usar SQL dinâmico, a menos que a INcláusula possa ser construída em SQL regular. Eu quero fazer algo assim:

SELECT *
FROM
(
    SELECT x, y, z...
) srcTable
PIVOT
(
    MIN(srcTable.TimesDownloaded) FOR OrganizationName IN (SELECT CONCAT('Download_', OrganizationName) FROM AdamUser GROUP BY OrganizationName)
) pivotTable

Então, quando eu SELECTúnico OrganizationNames de AdamUsere prefixo Download_, isso me dá uma tabela cujos valores de linha são iguais aos nomes das colunas que eu quero. Esta é uma sintaxe inválida. Eu sempre tenho que codificar os PIVOTnomes das colunas?

Tenho uma consulta que está demorando muito na minha máquina (7 minutos) para ser executada e gostaria de saber se poderia torná-la (significativamente) mais rápida:

SELECT
    rec.[Id] AS RecordId,
    MIN(rec.[CreationDate]) AS RecordCreationDate,
    MIN(rec.[LastModified]) AS RecordLastModified,
    MIN(rec.[AssetType]) AS RecordAssetType,
    MIN(rec.[MasterFilename]) AS RecordMasterFilename,
    MIN(rec.[GameName]) AS RecordGameName,
    usr.[OrganizationName],
    COUNT(hist.[Id]) AS TimesDownloaded
FROM
(
    SELECT
        innerRec.Id,
        MIN(innerRec.CreationDate) AS CreationDate,
        MIN(innerRec.LastModified) AS LastModified,
        MIN(innerRec.AssetType) AS AssetType,
        MIN(innerRec.MasterFilename) AS MasterFilename,
        MIN(innerRec.GameName) AS GameName
    FROM
        [dbo].[Record] innerRec INNER JOIN [dbo].[RecordClassificationLink] innerLnk ON innerRec.Id = innerLnk.RecordId
    -- WHERE (classification ID is foo or bar)
    GROUP BY
        innerRec.Id
    -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications)
) rec
CROSS JOIN
    [dbo].[AdamUser] usr
LEFT JOIN
    (SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > '2016-01-01 00:00:00' AND [CreatedOn] < '2016-12-01 00:00:00') hist ON usr.Name = hist.AccessingUser AND rec.Id = hist.RecordId
GROUP BY
    rec.Id, usr.OrganizationName

O que está fazendo é extrair dados para serem colocados em um relatório de planilha do Excel (se uma planilha é uma boa apresentação desses dados está fora do escopo desta questão :-))

A primeira subconsulta extrai registros opcionalmente filtrados por uma lista de IDs de classificação. Estes são, então, cruzados com a tabela de usuários, porque cada linha da tabela de usuários realmente contém as informações que realmente precisamos para isso: o nome da organização do usuário. Em seguida, deixei a tabela de histórico de trabalho de manutenção (armazenando uma entrada para cada download de registro) para criar várias linhas se um registro foi acessado várias vezes e, em seguida, agrupe por ID de registro e nome da organização para obter um "número de downloads de registro por organização" contam como TimesDownloaded.

O código que está lendo essa saída preenche uma matriz associativa cuja chave é OrganizationNamee cujo valor é TimesDownloaded, criando o equivalente a uma dinâmica PIVOTem que cada linha de registro contém uma coluna por organização, cada uma contendo a contagem do número de downloads de registro.

Como você pode imaginar, isso é executado muito lentamente em um grande conjunto de dados, como eu disse acima; aquele com o qual estou trabalhando tem ~ 38.000 Records e ~ 1.000 usuários, o que significa que a junção cruzada resulta em ~ 38.000.000 linhas, mas parece conceitualmente necessário.

Isso pode ser significativamente mais eficiente? Seria melhor se eu fizesse o PIVOTSQL dinâmico?

O DBMS que estou usando é o SQL Server 2014.

Aqui estão as definições de esquema para as tabelas:

CREATE TABLE [dbo].[AdamUser](
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [UserGroupName] [nvarchar](50) NOT NULL,
    [OrganizationName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE NONCLUSTERED INDEX [UIX_AdamUser_Name] ON [dbo].[AdamUser]
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE TABLE [dbo].[MaintenanceJobHistory](
    [Id] [uniqueidentifier] NOT NULL,
    [Data] [xml] NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [Type] [nvarchar](512) NOT NULL,
    [RecordId] [uniqueidentifier] NOT NULL,
    [AccessingUser] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_MaintenanceJobHistory] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_MaintenanceJobHistory_CreatedOn] ON [dbo].[MaintenanceJobHistory]
(
    [CreatedOn] ASC
)
INCLUDE (     [Id],
    [RecordId],
    [AccessingUser]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

CREATE TABLE [dbo].[Record](
    [Id] [uniqueidentifier] NOT NULL,
    [CreationDate] [datetime] NOT NULL,
    [LastModified] [datetime] NOT NULL,
    [AssetType] [nvarchar](max) NULL,
    [MasterFilename] [nvarchar](max) NULL,
    [GameName] [nvarchar](max) NULL,
 CONSTRAINT [PK_Record] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[RecordClassificationLink](
    [Id] [uniqueidentifier] NOT NULL,
    [RecordId] [uniqueidentifier] NOT NULL,
    [ClassificationId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_RecordClassificationLink] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Aqui está o plano de execução: https://www.brentozar.com/pastetheplan/?id=Sy6LlXDXg

Saída típica:

No entanto, essa saída é transformada em uma planilha do Excel de acordo com as linhas a seguir pelo programa de chamada (portanto, é como uma PIVOToperação):

.----------------------------------------------------------------------.
| Filename | Creation Date | #times downloaded by: CompanyA | CompanyB | ...
| foo.png  | 1/2/3         |                       0        | 2        |
| bar.png  | 1/3/4         |                       3        | 1        |
...

ATUALIZAR:

Acabei tornando as coisas significativamente mais eficientes movendo a PIVOToperação para a própria consulta SQL; dessa forma, o SQL Server só precisa gerar o número de linhas na Recordtabela, em vez do multiplicado pelo número de organizações (não é tão ruim até chegar a centenas de organizações, ponto em que é um número enorme). A operação ainda leva alguns minutos, mas é bem mais suportável. Aqui está a consulta que finalmente decidi usar:

SELECT *
FROM (
    SELECT
        rec.[Id]                         AS RecordId,
        'Org_' + usr.[OrganizationName]  AS OrganizationNamePrefixed,
        COUNT(hist.[Id])                 AS TimesDownloaded  -- To be aggregated by PIVOT
    FROM (
        SELECT
            innerRec.[Id]
        FROM
            [dbo].[Record] innerRec
        INNER JOIN
            [dbo].[RecordClassificationLink] innerLnk ON innerLnk.[RecordId] = innerRec.[Id]
        -- WHERE (classification ID is foo or bar), for optional classification filtering
        GROUP BY
            innerRec.[Id]
        -- HAVING COUNT(innerLnk.ClassificationId) = (number of specified classifications), for optional classification filtering
    ) rec
    CROSS JOIN [dbo].[AdamUser] usr
    LEFT JOIN (
        SELECT * FROM [dbo].[MaintenanceJobHistory] WHERE [CreatedOn] > 'eg. 2016-01-01 12:00:00' AND [CreatedOn] < 'eg. 2016-12-01 12:00:00'
    ) hist ON hist.[AccessingUser] = usr.[Name] AND hist.[RecordId] = rec.[Id]
    GROUP BY
        rec.[Id], usr.[OrganizationName]
) srcTable
PIVOT  -- Pivot around columns outside aggregation fn, eg. heading column [OrganizationNamePrefixed] & all other columns: [RecordId]
(
    MIN(srcTable.[TimesDownloaded]) FOR [OrganizationNamePrefixed] IN (...list of ~200 columns dynamically generated...)
) pivotTable
INNER JOIN [dbo].[Record] outerRec ON outerRec.[Id] = pivotTable.[RecordId]

Adicionei vários índices e também tornei o PIVOTmais eficiente possível selecionando apenas a coluna de agregação, a coluna de títulos e as outras colunas necessárias para girar. Por fim, refiz JOINa Recordtabela usando o RecordIdPK para obter as informações extras do registro por linha.

Esta consulta funciona:

DECLARE @Ordering NVARCHAR(50)
SET @Ordering = 'aggregateid'

DECLARE @OrderDirectionInt INT
SET @OrderDirectionInt = 0 --1

SELECT * FROM (
    SELECT
        ROW_NUMBER() OVER (ORDER BY
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 1 THEN AggregateId END DESC,
            CASE WHEN LOWER(@Ordering) = 'aggregateid' AND @OrderDirectionInt = 0 THEN AggregateId END ASC
        ) AS RowNumber,
        MyField
    FROM
        MyTable
) AS TempResults
WHERE TempResults.RowNumber BETWEEN 12 AND 23

O que não entendo é por que ASCe DESCestão depois (ou fora) das CASEinstruções. Parece que o ASCe DESCestá sendo colocado incondicionalmente, o que não faz sentido... pode resolver para:

ROW_NUMBER() OVER (ORDER BY
    DESC,
    AggregateId ASC
) AS RowNumber

Por que a CASEinstrução não contém o ASCou DESC(se você tentar fazer isso, receberá um erro de sintaxe)?

Eu tentei pesquisar no Google por uma resposta para isso, mas não cheguei a lugar nenhum.

Eu tenho uma instância do SQL Server cuja replicação está configurada para ser um publicador e um distribuidor de FooDatabase. Eu tenho uma segunda instância cuja replicação está configurada para ser um assinante FooDatabasena primeira instância. As atualizações de replicação funcionam. O que eu quero fazer é fazer backup da assinatura FooDatabase, excluí-la na instância do assinante e restaurar o backup. No entanto, quando faço isso e tento restaurar o backup, recebo o seguinte erro:

Falha na restauração do servidor 'DAVEG1525-162'. (Microsoft.SqlServer.SmoExtended)

System.Data.SqlClient.SqlError: Nome de objeto inválido 'master.dbo.MSreplication_options'. (Microsoft.SqlServer.Smo)

Eu verifiquei e dbo.MSreplication_optionsexiste como uma tabela de sistema no masterbanco de dados na instância de produção , mas não na instância de assinatura .

Minhas dúvidas são: se não existe na instância de assinatura, por que está sendo incluído no backup feito a partir da instância de assinatura do SQL Server? e como posso corrigir esse problema?

Estou usando a funcionalidade de replicação do SQL Server 2008 r2 para atualizar meu banco de dados de assinantes por meio de uma assinatura pull transacional. Quando eu o marco para reinicialização, ele corrige o esquema e os dados de quaisquer tabelas locais modificadas que existam no instantâneo da publicação, mas não remove nenhuma nova tabela (e presumivelmente SPs, gatilhos, etc.) que foram adicionados. Existe alguma maneira de conseguir a sincronização para remover objetos supérfluos como tabelas que não existem no shapshot da publicação, além de atualizar e adicionar objetos existentes?

Percebi que, quando você configura uma replicação transacional, o SQL Server define o gerenciamento de intervalo de identidade como manual. O que isso significa é que no meu banco de dados de assinatura, quando tento inserir um novo registro em uma tabela cujo PK é uma coluna de identidade, ele me dará um erro e dirá que tentou inserir um PK de "1", "2 ", "3", etc. Isso ocorre porque o valor de identidade atual para todas as colunas de identidade no assinante é redefinido para o valor inicial (geralmente 1) em vez de permanecer no que era no editor.

Entendo por que o SQL Server faz isso - você deve deixar a tabela de assinantes como somente leitura. No entanto, meu cenário é um pouco heterodoxo - atualizo meu assinante de tempos em tempos por meio de replicação, faço um backup imediato desse banco de dados e, em seguida, quero fazer algumas atualizações no assinante que NÃO serão enviadas de volta ao editor e, em seguida, quando vou atualizar o assinante novamente, restauro seu banco de dados do backup anterior e extraio as atualizações mais recentes. Como desejo fazer atualizações para o assinante entre essas atualizações ('deltas temporários', se preferir), preciso que a coluna de identidade funcione e não seja redefinida para 1 quando replicada.

Tentei ativar o gerenciamento automático de intervalo de identidade ao configurar minha publicação, mas isso me dá o seguinte erro quando tento adicionar uma tabela à publicação:

Msg 21231, nível 16, estado 1, procedimento sp_MSrepl_addarticle, linha 2243
O suporte ao intervalo de identidade automática é útil apenas para publicações que permitem a atualização de assinantes.

Existe alguma maneira de contornar esse problema? Eu meio que quero apresentar essa replicação ao SQL Server como se fosse somente leitura no final do assinante porque não pretendo fazer atualizações que serão enviadas ao editor , mas quero fazer atualizações temporárias que serão apagados antes da próxima replicação.

Também considerei que a replicação de instantâneo pode ser um método mais apropriado do que a replicação transacional para meu padrão de uso, mas o problema é que a replicação de instantâneo requer o envio de todo o maldito banco de dados a cada atualização; como estou planejando fazer um backup imediato do banco de dados após a última replicação, não devo precisar fazer toda essa transferência todas as vezes; apenas as mudanças desde a última vez.