Thomas Franz's questions

Problema:

1. você quer/precisa usar SQL dinâmico para criar procedimento/função/view/trigger em outros bancos de dados que não o atual
2. você não pode especificar o nome do banco de dados na instrução, CREATE VIEW tempdb.dbo.v_test AS SELECT 1; falhará com o Erro 166: 'CREATE/ALTER VIEW' não permite especificar o nome do banco de dados como um prefixo para o nome do objeto.
3. a instrução CREATE(ou ALTER) precisa ser a primeira no lote, caso contrário, você obterá o erro 111: 'CREATE ...' deve ser a primeira instrução em um lote de consulta. . Por esta razão, você não pode simplesmente escrever USE <database>antes de CREATE:

DECLARE @create_sql NVARCHAR(max);
SET @create_sql = CONCAT('USE tempdb', CHAR(13) + CHAR(10),
                         'CREATE VIEW dbo.v_test AS SELECT 1 AS n;'
                        );
EXEC (@create_sql) -- fails
-- or
EXEC sys.sp_executesql @create_sql -- fails too

4. você não pode usar GOdentro de uma instrução EXEC()or EXEC sys.sp_executesql
   , porque não está no padrão SQL (apenas uma palavra fictícia configurável usada dentro do SSMS e várias outras ferramentas SQL), então você não pode simplesmente adicioná-lo entre o USE e o comando CREATE em o script acima como você faria no SSMS ao criar um script de implantação "normal"
5. o procedimento interno sys.sp_executesqlexiste em cada banco de dados e executa o comando fornecido em seu próprio banco de dados, mas pode haver muitos (por exemplo, centenas) bancos de dados, de modo que você não pode realmente usar várias IFinstruções para "codificar" onde deseja executá-lo

Veja o plano de execução em https://www.brentozar.com/pastetheplan/?id=SyLQIPDtF (SQL 2016 Enterprise)

• Eu tenho uma tabela de datawarehouse peak_reporting_data que rastreia a atividade por dia e hora e contém cerca de 4 bilhões de linhas por meses com um columnstoreindex clusterizado particionado por date_key (uma partição por dia)
• na tabela peak_reporting_monats_peaks eu agreguei esta tabela e a ordenei/classifiquei pelo pico do mês. Existem 3 tipos de atividade (kpi_type), para cada eu tenho até 24h * 31 dias = 744 linhas por mês com [monats_peak] ranqueado de 1 a 744. Tem um índice único sobre month_key, kpi_type, monats_peak.
• Para a hora mais ativa (por kpi_type) preciso de mais alguns detalhes, então escrevi a seguinte consulta/visualização:

    SELECT prmp.month_key
         , prd.*
      FROM mba.peak_reporting_monats_peaks        AS prmp
      LEFT LOOP JOIN (SELECT prd.date_key
                           , prd.hour
                           , prd.kpi_type
                           , prd.is_dr_brand
                           , prd.type_id_usage
                           , prd.product_identifier
                           , SUM(prd.kb) / 1024.0 / 1024.0 AS gb
                           , SUM(CAST(prd.sek AS BIGINT))  AS sek
                           , SUM(prd.anzahl)               AS anzahl
                           , SUM(prd.kb) / 439453125.0     AS gbits
                        FROM db1.mba.peak_reporting_data AS prd 
                       GROUP BY prd.date_key
                              , prd.kpi_type
                              , prd.is_dr_brand
                              , prd.hour
                              , prd.type_id_usage
                              , prd.product_identifier
                      ) AS prd
        ON prd.date_key  = prmp.date_key
       AND prd.hour      = prmp.hour
     WHERE prmp.monats_peak = 1
       AND prmp.month_key = 202107

Como existem exatamente 3 linhas com monats_peak = 1 por mês em peak_reporting_monats_peaks, seria lógico para o SQL Server fazer 3 loops e consultas aninhados / agregar a tabela grande com base em date_key, hour e kpi_type (o que seria feito em talvez 2 segundos , como testei com um cursor).

Mas infelizmente ele lê sempre a tabela inteira (36 bilhões de linhas no momento) sem nenhum predicado pedicate/busca no operador ColumnstoreIndexScan, independente do que eu tente. Em vez de 2 segundos, a consulta precisa de 2 a 3 minutos para ser concluída por esse motivo.

Se eu usar um INNER LOOP JOIN em vez de LEFT LOOP JOIN ele remove o operador de filtro, mas adiciona um spool de tabela preguiçoso antes do JOIN, um INNER JOIN usual (sem LOOP) levaria a um HASH JOIN (novamente em toda a tabela).

Alguma idéia de como (exceto um procedimento ou função de valor de tabela de várias linhas com um cursor) eu poderia "forçar" o servidor SQL a fazer as 3 pesquisas simples (+ agregações) em vez de ler toneladas de dados irrelevantes?

As estatísticas etc. estão corretas, ele sabe exatamente que haverá 3 linhas em peak_reporting_monats_peaks e adicionei uma estatística explícita em date_key + hour ao grande peak_reporting_data

DDL:

USE tempdb
GO
CREATE SCHEMA [mba] AUTHORIZATION dbo
GO
CREATE PARTITION FUNCTION pf_mba_cdr (INT)
    AS RANGE RIGHT FOR VALUES (20201101 , 20201102 , 20201103 , 20201104 , 20201105 , 20201106 , 20201107 , 20201108 , 20201109 , 20201110 , 20201111 , 20201112 , 20201113 , 20201114 , 20201115 , 20201116 , 20201117 , 20201118 , 20201119 , 20201120 , 20201121 , 20201122 , 20201123 , 20201124 , 20201125 , 20201126 , 20201127 , 20201128 , 20201129 , 20201130 , 20201201 , 20201202 , 20201203 , 20201204 , 20201205 , 20201206 , 20201207 , 20201208 , 20201209 , 20201210 , 20201211 , 20201212 , 20201213 , 20201214 , 20201215 , 20201216 , 20201217 , 20201218 , 20201219 , 20201220 , 20201221 , 20201222 , 20201223 , 20201224 , 20201225 , 20201226 , 20201227 , 20201228 , 20201229 , 20201230 , 20201231 , 20210101 , 20210102 , 20210103 , 20210104 , 20210105 , 20210106 , 20210107 , 20210108 , 20210109 , 20210110 , 20210111 , 20210112 , 20210113 , 20210114 , 20210115 , 20210116 , 20210117 , 20210118 , 20210119 , 20210120 , 20210121 , 20210122 , 20210123 , 20210124 , 20210125 , 20210126 , 20210127 , 20210128 , 20210129 , 20210130 , 20210131 , 20210201 , 20210202 , 20210203 , 20210204 , 20210205 , 20210206 , 20210207 , 20210208 , 20210209 , 20210210 , 20210211 , 20210212 , 20210213 , 20210214 , 20210215 , 20210216 , 20210217 , 20210218 , 20210219 , 20210220 , 20210221 , 20210222 , 20210223 , 20210224 , 20210225 , 20210226 , 20210227 , 20210228 , 20210301 , 20210302 , 20210303 , 20210304 , 20210305 , 20210306 , 20210307 , 20210308 , 20210309 , 20210310 , 20210311 , 20210312 , 20210313 , 20210314 , 20210315 , 20210316 , 20210317 , 20210318 , 20210319 , 20210320 , 20210321 , 20210322 , 20210323 , 20210324 , 20210325 , 20210326 , 20210327 , 20210328 , 20210329 , 20210330 , 20210331 , 20210401 , 20210402 , 20210403 , 20210404 , 20210405 , 20210406 , 20210407 , 20210408 , 20210409 , 20210410 , 20210411 , 20210412 , 20210413 , 20210414 , 20210415 , 20210416 , 20210417 , 20210418 , 20210419 , 20210420 , 20210421 , 20210422 , 20210423 , 20210424 , 20210425 , 20210426 , 20210427 , 20210428 , 20210429 , 20210430 , 20210501 , 20210502 , 20210503 , 20210504 , 20210505 , 20210506 , 20210507 , 20210508 , 20210509 , 20210510 , 20210511 , 20210512 , 20210513 , 20210514 , 20210515 , 20210516 , 20210517 , 20210518 , 20210519 , 20210520 , 20210521 , 20210522 , 20210523 , 20210524 , 20210525 , 20210526 , 20210527 , 20210528 , 20210529 , 20210530 , 20210531 , 20210601 , 20210602 , 20210603 , 20210604 , 20210605 , 20210606 , 20210607 , 20210608 , 20210609 , 20210610 , 20210611 , 20210612 , 20210613 , 20210614 , 20210615 , 20210616 , 20210617 , 20210618 , 20210619 , 20210620 , 20210621 , 20210622 , 20210623 , 20210624 , 20210625 , 20210626 , 20210627 , 20210628 , 20210629 , 20210630 , 20210701 , 20210702 , 20210703 , 20210704 , 20210705 , 20210706 , 20210707 , 20210708 , 20210709 , 20210710 , 20210711 , 20210712 , 20210713 , 20210714 , 20210715 , 20210716 , 20210717 , 20210718 , 20210719 , 20210720 , 20210721 , 20210722 , 20210723 , 20210724 , 20210725 , 20210726 , 20210727 , 20210728 , 20210729 , 20210730 , 20210731 , 20210801 , 20210802 , 20210803 , 20210804 , 20210805 , 20210806 , 20210807 , 20210808 , 20210809 , 20210810 , 20210811 , 20210812 , 20210813 , 20210814 , 20210815 , 20210816 , 20210817 , 20210818 , 20210819 , 20210820 , 20210821 , 20210822 , 20210823 , 20210824 , 20210825 , 20210826 , 20210827 , 20210828 , 20210829 , 20210830 , 20210831 , 20210901 , 20210902 , 20210903 , 20210904 , 20210905 , 20210906 , 20210907 , 20210908 , 20210909 , 20210910 , 20210911 , 20210912 , 20210913 , 20210914 , 20210915 , 20210916 , 20210917 , 20210918 , 20210919 , 20210920 , 20210921 , 20210922 , 20210923 , 20210924 , 20210925 , 20210926 , 20210927 , 20210928 , 20210929 , 20210930 , 20211001 , 20211002 , 20211003 , 20211004 , 20211005 , 20211006 , 20211007 , 20211008 , 20211009 , 20211010 , 20211011 , 20211012 , 20211013 , 20211014 , 20211015 , 20211016 , 20211017 , 20211018 , 20211019 , 20211020 , 20211021 , 20211022 , 20211023 , 20211024 , 20211025 , 20211026 , 20211027 , 20211028 , 20211029 , 20211030 , 20211031 , 20211101 , 20211102 , 20211103 , 20211104 , 20211105 , 20211106 , 20211107 , 20211108 , 20211109 , 20211110 , 20211111 , 20211112 , 20211113 , 20211114 , 20211115 , 20211116 , 20211117 , 20211118 , 20211119 , 20211120 , 20211121 , 20211122 , 20211123 , 20211124 , 20211125 , 20211126 , 20211127 , 20211128 , 20211129 , 20211130 , 20211201 , 20211202 , 20211203 , 20211204 , 20211205 , 20211206 , 20211207 , 20211208 , 20211209 , 20211210 , 20211211 , 20211212 , 20211213 , 20211214 , 20211215 , 20211216 , 20211217 , 20211218 , 20211219 , 20211220 , 20211221 , 20211222 , 20211223 , 20211224 , 20211225 , 20211226 , 20211227 , 20211228 , 20211229 , 20211230 , 20211231 , 20220101 , 20220102 , 20220103 , 20220104 , 20220105 , 20220106 , 20220107 , 20220108 , 20220109 , 20220110 , 20220111 , 20220112 , 20220113 , 20220114 , 20220115 , 20220116 , 20220117 , 20220118 , 20220119 , 20220120 , 20220121 , 20220122 , 20220123 , 20220124 , 20220125 , 20220126 , 20220127 , 20220128 , 20220129 , 20220130 , 20220131 , 20220201 , 20220202 , 20220203 , 20220204 , 20220205 , 20220206 , 20220207 , 20220208 , 20220209 , 20220210 , 20220211 , 20220212 , 20220213 , 20220214 , 20220215 , 20220216 , 20220217 , 20220218 , 20220219 , 20220220 , 20220221 , 20220222 , 20220223 , 20220224 , 20220225 , 20220226 , 20220227 , 20220228 , 20220301 , 20220302 , 20220303 , 20220304 , 20220305 , 20220306 , 20220307 , 20220308 , 20220309 , 20220310 , 20220311 , 20220312 , 20220313 , 20220314 , 20220315 , 20220316 , 20220317 , 20220318 , 20220319 , 20220320 , 20220321 , 20220322 , 20220323 , 20220324 , 20220325 , 20220326 , 20220327 , 20220328 , 20220329 , 20220330 , 20220331 , 20220401 , 20220402 , 20220403 , 20220404 , 20220405 , 20220406 , 20220407 , 20220408 , 20220409 , 20220410 , 20220411 , 20220412 , 20220413 , 20220414 , 20220415 , 20220416 , 20220417 , 20220418 , 20220419 , 20220420 , 20220421 , 20220422 , 20220423 , 20220424 , 20220425 , 20220426 , 20220427 , 20220428 , 20220429 , 20220430 , 20220501 , 20220502 , 20220503 , 20220504 , 20220505 , 20220506 , 20220507 , 20220508 , 20220509 , 20220510 , 20220511 , 20220512 , 20220513 , 20220514 , 20220515 , 20220516 , 20220517 , 20220518 , 20220519 , 20220520 , 20220521 , 20220522 , 20220523 , 20220524 , 20220525 , 20220526 , 20220527 , 20220528 , 20220529 , 20220530 , 20220531 , 20220601 , 20220602 , 20220603 , 20220604 , 20220605 , 20220606 , 20220607 , 20220608 , 20220609 , 20220610 , 20220611 , 20220612 , 20220613 , 20220614 , 20220615 , 20220616 , 20220617 , 20220618 , 20220619 , 20220620 , 20220621 , 20220622 , 20220623 , 20220624 , 20220625 , 20220626 , 20220627 , 20220628 , 20220629 , 20220630 , 20220701 , 20220702 , 20220703 , 20220704 , 20220705 , 20220706 , 20220707 , 20220708 , 20220709 , 20220710 , 20220711 , 20220712 , 20220713 , 20220714 , 20220715 , 20220716 , 20220717 , 20220718 , 20220719 , 20220720 , 20220721 , 20220722 , 20220723 , 20220724 , 20220725 , 20220726 , 20220727 , 20220728 , 20220729 , 20220730 , 20220731 , 20220801 , 20220802 , 20220803 , 20220804 , 20220805 , 20220806 , 20220807 , 20220808 , 20220809 , 20220810 , 20220811 , 20220812 , 20220813 , 20220814 , 20220815 , 20220816 , 20220817 , 20220818 , 20220819 , 20220820 , 20220821 , 20220822 , 20220823 , 20220824 , 20220825 , 20220826 , 20220827 , 20220828 , 20220829 , 20220830 , 20220831 , 20220901 , 20220902 , 20220903 , 20220904 , 20220905 , 20220906 , 20220907 , 20220908 , 20220909 , 20220910 , 20220911 , 20220912 , 20220913 , 20220914 , 20220915 , 20220916 , 20220917 , 20220918 , 20220919 , 20220920 , 20220921 , 20220922 , 20220923 , 20220924 , 20220925 , 20220926 , 20220927 , 20220928 , 20220929 , 20220930 , 20221001 , 20221002 , 20221003 , 20221004 , 20221005 , 20221006 , 20221007 , 20221008 , 20221009 , 20221010 , 20221011 , 20221012 , 20221013 , 20221014 , 20221015 , 20221016 , 20221017 , 20221018 , 20221019 , 20221020 , 20221021 , 20221022 , 20221023 , 20221024 , 20221025 , 20221026 , 20221027 , 20221028 , 20221029 , 20221030 , 20221031 , 20221101 , 20221102 , 20221103 , 20221104 , 20221105 , 20221106 , 20221107 , 20221108 , 20221109 , 20221110 , 20221111 , 20221112 , 20221113 , 20221114 , 20221115 , 20221116 , 20221117 , 20221118 , 20221119 , 20221120 , 20221121 , 20221122 , 20221123 , 20221124 , 20221125 , 20221126 , 20221127 , 20221128 , 20221129 , 20221130 , 20221201 , 20221202 , 20221203 , 20221204 , 20221205 , 20221206 , 20221207 , 20221208 , 20221209 , 20221210 , 20221211 , 20221212 , 20221213 , 20221214 , 20221215 , 20221216 , 20221217 , 20221218 , 20221219 , 20221220 , 20221221 , 20221222 , 20221223 , 20221224 , 20221225 , 20221226 , 20221227 , 20221228 , 20221229 , 20221230 , 20221231);
GO
CREATE PARTITION SCHEME ps_mba_cdr AS PARTITION pf_mba_cdr ALL TO ([PRIMARY]);
GO


CREATE TABLE mba.peak_reporting_data
     (date_key            INT            NOT NULL
    , hour                TINYINT        NOT NULL
    , kb                  DECIMAL(19, 6) NULL
    , msisdn_key          INT            NOT NULL
    , sp_account          SMALLINT       NOT NULL
    , is_dr_brand         BIT            NULL
    , type_id_usage       BIGINT         NOT NULL
    , product_identifier  BIGINT         NOT NULL
    , kpi_service         VARCHAR(15)    NULL
    , kpi_group           VARCHAR(15)    NULL
    , sek                 INT            NULL
    , anzahl              INT            NULL
    , kpi_type            CHAR(1)        NOT NULL
    , anzahl_begonnen     INT            NULL
    , anzahl_geendet      INT            NULL
    , anzahl_durchgaengig INT            NULL
    , anzahl_nur_in_hour  INT            NULL)
    ON ps_mba_cdr(date_key);
GO

ALTER TABLE mba.peak_reporting_data SET (LOCK_ESCALATION = AUTO);
GO
CREATE CLUSTERED COLUMNSTORE INDEX icc_peak_reporting_data ON mba.peak_reporting_data ON ps_mba_cdr(date_key);
GO


CREATE TABLE mba.peak_reporting_monats_peaks
     (month_key           INT            NOT NULL
    , date_key            INT            NOT NULL
    , week_day            VARCHAR(30)    NOT NULL
    , hour                TINYINT        NOT NULL
    , kpi_type            CHAR(1)        NOT NULL
    , gb                  DECIMAL(38, 6) NULL
    , sek                 BIGINT         NULL
    , anzahl              INT            NOT NULL
    , gigabit_pro_sekunde DECIMAL(38, 6) NULL
    , prozent_dr_brand    DECIMAL(9, 6)  NOT NULL
    , tages_peak          TINYINT        NOT NULL
    , monats_peak         SMALLINT       NOT NULL
    , refresh_date        DATETIME2(0)   NOT NULL) ON [PRIMARY];
GO

CREATE UNIQUE CLUSTERED INDEX iuc_peak_reporting_monats_peaks__month_key__kpi_type__monats_peak
    ON mba.peak_reporting_monats_peaks (month_key, kpi_type, monats_peak)
    WITH (DROP_EXISTING = OFF, FILLFACTOR = 98, DATA_COMPRESSION = ROW, SORT_IN_TEMPDB = ON
        , STATISTICS_INCREMENTAL = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY];
GO
CREATE NONCLUSTERED INDEX inc_peak_reporting_monats_peaks__date_key
    ON mba.peak_reporting_monats_peaks (date_key, kpi_type, tages_peak)
    WITH (DROP_EXISTING = OFF, FILLFACTOR = 99, DATA_COMPRESSION = PAGE, SORT_IN_TEMPDB = ON
        , STATISTICS_INCREMENTAL = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    ON [PRIMARY];
GO

Eu adicionei o DDL e com um INNER JOIN normal sem nenhuma dica demora 2 min pois adiciona o filtro logo após o scan, mas desta vez fez um NESTED LOOP.

Se eu forçar um HASH JOIN recebo um novo plano agora: https://www.brentozar.com/pastetheplan/?id=HyLDWiPFt de 43k lidos). Ele diz ainda que a tabela é particionada, mas usou 0 partições (errado, caso contrário eu não teria resultado). A questão em aberto ainda é: por que usa um filtro para NESTED LOOPS.

Eu sei que você não pode obter um Columnstore Index Seek, mas se a tabela for particionada, ela poderia pelo menos adicionar um SEEK Predicate (geralmente a partição) ao operador Columnstore Index Scan.

É bem conhecido que o SSMS (ou talvez o SQL Server, mas acho que mais o SSMS) atrasa as PRINTmensagens até que várias linhas (40) sejam reunidas no buffer, antes de serem realmente impressas na janela de mensagens no SSMS (exceção : algo mais é impresso, por exemplo, o número de linhas afetadas por um UPDATE ou o lote está pronto).

Então eu costumo usar RAISEERROR(<text>, 0, 0) WITH NOWAITpara imprimir imediatamente. Isso funciona bem para as primeiras 500 linhas, depois disso o SSMS parece começar a buffer novamente (50 linhas, começando em 1000 linhas, o buffer parece aumentar para 1000).

Alguém sabe como posso evitar esse "recurso de buffer" (por exemplo, se eu executar manualmente as atualizações estatísticas usando a solução de manutenção Ola Hallengreens, prefiro saber o que ele realmente faz no momento sem precisar usar sp_whoisactive etc. ).

PS: Você pode simular esse comportamento usando o seguinte "script"

DECLARE @i INT = 0

WHILE @i < 10000
BEGIN
    SET @i += 1
    RAISERROR('Step %i', 0, 0, @i) WITH NOWAIT
    --PRINT @i
    WAITFOR DELAY '00:00:01.0' -- wait 1 second, feel free to decrease
END

Quando crio um índice COLUMNSTORE, posso especificar COMPRESSION_DELAY = x [minutes]no CREATE-Statement. Ele atrasa o movimento das linhas em um segmento fechado do armazenamento delta para o armazenamento columnstore compactado. Razão para fazer isso: seu processo de importação não é apenas um simples INSERT, mas tem alguns UPDATEs e talvez DELETEs também.

Existe uma maneira de definir esse valor depois sem recriar todo o índice (o que levaria muito tempo em nossa tabela grande)?

Já testei REBUILD e REORGANIZE, mas ambos não entendem a COMPRESSION_DELAYopção no WITH().

Eu tenho um procedimento armazenado, que consulta uma exibição em um servidor vinculado.

Isso funciona bem no prod, mas nosso servidor de desenvolvimento tem algumas restrições de firewall, que impedem a compilação. Isso não é problema em termos de dados (já que não é muito importante no dev), mas não consigo ALTER o procedimento no servidor de desenvolvimento, porque o servidor SQL tenta compilá-lo / verificar a sintaxe / nomes de colunas usadas etc. alterar o procedimento (e recebo um tempo limite).

Existe uma maneira (opção, sinalizador de rastreamento etc.) aviso)?

Existe um blog / página da web / informações (não encontrei nada) por aí, se tem benefícios de desempenho, se eu usar uma versão mais recente do Windows Server para o meu SQL Server?

Exemplo: executo o SQL 2016 em um Windows Server 2012. Faz sentido atualizar este servidor para o Windows Server 2016 ou o próximo Windows Server 2019 ao lado desses tópicos de ciclo de vida sem suporte? Faz diferença se eu instalar um novo SQL Server 2019 (se estiver fora) no Windows Server 2016 ou no Windows Server 2019?

PS: O único ponto que encontrei neste tópico foi que - se eu quiser usar NVDIMMs, devo usar o Windows Server 2016 para melhor suporte.

Esta semana, nosso banco de dados em um servidor SQL2014 SP2 CU11 começou a gerar erros fatais (gravidade 20), na instrução de código FETCH, que pode ser reduzido para:

DECLARE @dummy int
DECLARE CurName CURSOR LOCAL FOR
        SELECT TOP (1) t.id FROM dbo.t_table AS t;
OPEN CurName;

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM CurName INTO @dummy;
    IF @@fetch_status <> 0 BREAK;
    PRINT @dummy
END;

DBCC CHECKTABLE e DBCC CHECKDB não encontraram nenhum problema, uma reinicialização do servidor não ajudou (e como ocorreu em várias versões do mesmo banco de dados ao mesmo tempo, era muito improvável que isso fosse um problema de estrutura de banco de dados).

Em um servidor de desenvolvimento com SQL2014 SP2 e apenas CU6, as mesmas instruções são executadas sem problemas (em um SQL2017 Server também funciona).

A mesa

• tem cerca de 4 milhões de linhas
• é particionado por uma coluna inteira
• tem um PK clusterizado em duas colunas (a coluna de partição mais um bigint)
• tem 25 índices (eu acho, eu deveria limpar um pouco :-))

Preciso de ajuda com a seguinte tarefa (desistir depois de algumas horas)

CREATE TABLE #cities (cityId INT, city VARCHAR(100));
INSERT INTO #cities (cityId, city) 
VALUES (1, 'Metropolis A'),
       (2, 'Town B'),
       (3, 'Village C');

CREATE TABLE #assignements (assignmentID INT, cityId INT, desciption VARCHAR(100));
INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (1, 1, 'Test Alpha Metropol'),
       (1, 2, 'Test Alpha Town'),
       (1, 3, 'Test Alpha Village'),
       (2, 1, 'Test Beta Metropol');

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (2, 3, 'Test Beta Village');  

Eu preciso de uma consulta, que me retorne todos os IDs de atribuição associados a #cidades (fácil :-)), mas liste apenas as cidades que são usadas em TODOS os IDs de atribuição.

No exemplo acima, devo retornar após o primeiro INSERTapenas

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'

já que a cidade e a vila são usadas apenas uma vez (mas existem dois IDs de atribuição diferentes)

Após o segundo INSERTdeve retornar

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'
1             'Village C'
2             'Village C'

porque o Village C agora também está atribuído a ambos os assignIDs.

Quando eu faria

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (3, 2, 'Test Gamma Town');  

a consulta não deve retornar nenhuma linha, porque não há cidade, que é atribuída a todos os três IDs de atribuição (Alfa, Beta e Gama).

É claro que este exemplo é simplificado, na realidade pode haver qualquer número de cidades atribuídas a (atualmente) até 11 IDs de atribuição

Alguma ideia, por que a primeira das consultas a seguir retorna uma linha com -1, NULL, embora tenha um WHERE 1=2?

Somente quando coloco a consulta em outra subconsulta ela funciona corretamente (e retorna um conjunto de resultados vazio).

Testado no Microsoft SQL Server 2014 e 2016

DECLARE @i INT = 1

SELECT @i i, MAX(sub.id) mid
  FROM (SELECT TOP(@i) x.id
          FROM (VALUES(1), (2), (3), (4)) x(id)
         WHERE x.id > 2 + @i
         ORDER BY x.id) sub
WHERE 1 = 2

SELECT s1.i, s1.mid
  FROM (
        SELECT @i i, MAX(sub.id) mid
          FROM (SELECT TOP(@i) x.id
                  FROM (VALUES(1), (2), (3), (4)) x(id)
                 WHERE x.id > 2 + @i
                 ORDER BY x.id) sub
       ) s1
WHERE 1 = 2

Eu tenho uma tabela temporária #terminal que contém 3 linhas

Quando executo a seguinte consulta

SELECT t.termin, ttw.tourid, twt.va_nummer_int
  FROM #term AS t
 INNER JOIN plinfo.t_touren_werbeflaechentermine AS ttw
     ON ttw.termin = t.termin
  INNER JOIN wtv.t_werbeflaechentermine AS twt 
     ON twt.jahr   = t.jahr
    AND twt.termin = t.termin
    AND twt.ID_Wt  = ttw.id_wt
    GROUP BY t.termin, ttw.tourid, twt.va_nummer_int

Vou obter o seguinte plano de execução:

Cada tabela é unida usando um índice correspondente. Ambas as tabelas são particionadas por ps_termin(termin).

Para a primeira tabela (t_touren_werbeflaechentermine) ele faz uma eliminação de partição e lê apenas um subconjunto de linhas, enquanto para a segunda tabela (t_werbeflaechentermine) ele varre todo o índice ( jahr, termin, id_wt include (va_nummer_int)).

Então minha pergunta: por que faz uma varredura de índice (e não busca) e por que não elimina as partições para a segunda tabela.

PS: ao usar WITH (FORCESEEK)na segunda tabela, ele alterna as duas tabelas no plano de execução e faz a varredura completa do índice na primeira...

PPS: O plano de execução pode ser encontrado aqui

Eu tenho uma consulta muito complexa (quem não tem :-)). Nesta consulta está um JOIN para uma tabela como esta (claro que a original tem mais algumas colunas):

CREATE TABLE dbo.tbl_detail (id bigint PRIMARY key, 
                   descr varchar(300), 
                   txt varchar(MAX),
                   descr_p varchar(300)
                  );

Há duas junções para esta tabela na consulta:

• LEFT JOIN dbo.tbl_detail td1 ON td1.id = main.detail_id
• LEFT JOIN dbo.tbl_detail td2 ON td2.id = main.second_id
• A consulta retorna aproximadamente 200 mil linhas.
• td1 gera todas as colunas
• td2 gera todas as colunas, exceto [txt] ( varchar(max))

Problema:

• td2 usa um HASH JOIN
• mas td1 usa um NESTED LOOKUP (mais de 200k linhas; nenhum problema estatístico, pois as estimativas estão corretas)
• quando eu removo a coluna [txt] da saída td1 ela usa um HASH JOIN também
• quando eu altero a coluna para VARCHAR(5000) (ou menor) ela usa um HASH JOIN
• quando eu altero a coluna para VARCHAR(8000) ela usa o NESTED LOOKUP novamente
• OPTION (HASH JOIN)ou LEFT HASH JOINfuncionaria, mas produz um plano de consulta extremamente lento

Pergunta: Por que nem sempre usa o HASH JOIN? Existe um limite de comprimento (por coluna ou soma de todas as colunas de saída)?

PS: Microsoft SQL Server 2014 (SP2) Developer Edition