Der Kommissar's questions

Eu preciso ser capaz de localizar um elemento ausente de uma tabela com dezenas de milhões de linhas e ter uma chave primária de uma BINARY(64)coluna (que é o valor de entrada para calcular). Esses valores geralmente são inseridos em ordem, mas de vez em quando quero reutilizar um valor anterior que foi excluído. É inviável modificar os registros excluídos com uma IsDeletedcoluna, pois às vezes uma linha é inserida muitos milhões de valores à frente das linhas existentes no momento. Isso significa que os dados de amostra seriam algo como:

KeyCol : BINARY(64)
0x..000000000001
0x..000000000002
0x..FFFFFFFFFFFF

Portanto, inserir todos os valores ausentes entre 0x000000000002e 0xFFFFFFFFFFFFé inviável, a quantidade de tempo e espaço usados ​​seria indesejável. Essencialmente, quando executo o algoritmo, espero que ele retorne 0x000000000003, que é a primeira abertura.

Eu criei um algoritmo de pesquisa binária em C#, que consultaria o banco de dados para cada valor em position ie testaria se esse valor era esperado. Para contextualizar, meu terrível algoritmo: https://codereview.stackexchange.com/questions/174498/binary-search-for-a-missing-or-default-value-by-a-given-formula

Esse algoritmo executaria, por exemplo, 26-27 consultas SQL em uma tabela com 100.000.000 itens. (Isso não parece muito, mas vai ocorrer com muita frequência.) Atualmente, esta tabela tem aproximadamente 50.000.000 de linhas e o desempenho está se tornando perceptível .

Meu primeiro pensamento alternativo é traduzir isso para um procedimento armazenado, mas isso tem seus próprios obstáculos. (Eu tenho que escrever um BINARY(64) + BINARY(64)algoritmo, assim como uma série de outras coisas.) Isso seria doloroso, mas não inviável. Também considerei implementar o algoritmo de traduçãoROW_NUMBER baseado em , mas tenho um pressentimento muito ruim sobre isso. (A BIGINTnão é grande o suficiente para esses valores.)

Aceito outras sugestões, pois preciso muito que isso seja o mais rápido possível. Pelo que vale a única coluna selecionada pela consulta C# é a KeyCol, as demais são irrelevantes para esta parte.

Além disso, vale a pena, a consulta atual que busca o registro apropriado segue as linhas de:

SELECT [KeyCol]
  FROM [Table]
  ORDER BY [KeyCol] ASC
  OFFSET <VALUE> ROWS FETCH FIRST 1 ROWS ONLY

Onde <VALUE>é o índice fornecido pelo algoritmo. Eu também não tive o BIGINTproblema com OFFSETainda, mas eu vou. (Apenas ter 50.000.000 de linhas agora significa que ele nunca solicita um índice acima desse valor, mas em algum momento ele ficará acima do BIGINTintervalo.)

Alguns dados adicionais:

• A partir de exclusões, a gap:sequentialproporção é de cerca de 1:20;
• As últimas 35.000 linhas da tabela possuem valores > BIGINT's máximo;

Eu tenho uma consulta que usa uma WHEREcláusula e uso exatamente a mesma WHEREcláusula em muitas consultas nesta tabela (et al).

A consulta é:

SELECT
    DATENAME(DW, [AtDateTime]) AS [Day of Week]
    ,COUNT(*) AS [Number of Searches]
    ,CAST(CAST(COUNT(*) AS DECIMAL(10, 2)) 
         / COUNT(DISTINCT CONVERT(DATE, [AtDateTime])) AS DECIMAL(10, 2)) 
       AS [Average Searches per Day]
    ,SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END) 
       AS [Number of Searches with no Results]
    ,CAST(CAST(SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END) 
         AS DECIMAL(10, 2)) / COUNT(*) AS DECIMAL(10, 4)) 
       AS [Percent of Searches with no Results]
FROM [DB].[dbo].[SearchHistory] 
WHERE 
    [CustomerNumber] <> '1234' AND [CustomerNumber] <> '5678'
GROUP BY DATENAME(DW, [AtDateTime]), DATEPART(DW, [AtDateTime])
ORDER BY DATEPART(DW, [AtDateTime])

A parte que desejo alterar é a WHEREcláusula, para permitir que eu use uma tabela para que, se eu tiver que adicionar um número de cliente a ser ignorado, não precise atualizar todas as minhas consultas. (E há algumas consultas que têm essa mesma WHEREcláusula.)

Eu tenho uma tabela CustPassMastercom 16 colunas, uma das quais é CustNum varchar(8), e criei um índice IX_dbo_CustPassMaster_CustNum. Quando executo minha SELECTdeclaração:

SELECT * FROM dbo.CustPassMaster WHERE CustNum = '12345678'

Ele ignora o índice completamente. Isso me confunde, pois tenho outra tabela CustDataMastercom muito mais colunas (55), uma das quais é CustNum varchar(8). Criei um índice nesta coluna ( IX_dbo_CustDataMaster_CustNum) nesta tabela, e utilizo praticamente a mesma consulta:

SELECT * FROM dbo.CustDataMaster WHERE CustNum = '12345678'

E usa o índice que criei.

Existe algum raciocínio específico por trás disso? Por que ele usaria o índice de CustDataMaster, mas não o de CustPassMaster? É devido à baixa contagem de colunas?

A primeira consulta retorna 66 linhas. Para o segundo, 1 linha é retornada.

Além disso, nota adicional: CustPassMasterpossui 4.991 registros e CustDataMasterpossui 5.376 registros. Este poderia ser o raciocínio por trás de ignorar o índice? CustPassMastertambém possui registros duplicados com os mesmos CustNumvalores. Esse é outro fator?

Estou baseando essa afirmação nos resultados reais do plano de execução de ambas as consultas.

Aqui está o DDL para CustPassMaster(aquele com o índice não utilizado):

CREATE TABLE dbo.CustPassMaster(
    [CustNum] [varchar](8) NOT NULL,
    [Username] [char](15) NOT NULL,
    [Password] [char](15) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustPassMaster_CustNum] ON dbo.CustPassMaster
(
    [CustNum] 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) ON [PRIMARY]

E o DDL para CustDataMaster(omiti muitos campos irrelevantes):

CREATE TABLE dbo.CustDataMaster(
    [CustNum] [varchar](8) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustDataMaster_CustNum] ON dbo.CustDataMaster
(
    [CustNum] 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) ON [PRIMARY]

Não tenho um índice clusterizado em nenhuma dessas tabelas, apenas um índice não clusterizado.

Ignore o fato de que os tipos de dados não correspondem inteiramente ao tipo de dados que está sendo armazenado. Esses campos são um backup de um banco de dados IBM AS/400 DB2 e esses são os tipos de dados compatíveis para ele. (Tenho que ser capaz de consultar esse banco de dados de backup com exatamente as mesmas consultas e obter exatamente os mesmos resultados.)

Esses dados são usados ​​apenas para SELECTdeclarações. Eu não faço nenhuma instrução INSERT// nele, exceto quando o aplicativo de backup está copiando dados do AS/400 UPDATE.DELETE

Tenho várias centenas (atualmente 466, mas sempre crescendo) tabelas que preciso copiar de um servidor para outro.

Eu nunca tive que fazer isso antes, então não tenho certeza de como abordá-lo. Todas as tabelas estão no mesmo formato:Cart<Eight character customer number>

Isso faz parte de um projeto maior do qual estou fundindo todas essas Cart<Number>tabelas em uma única Cartstabela, mas essa é uma questão totalmente diferente.

Alguém tem um método de prática recomendada que eu possa usar para copiar todas essas tabelas? Os nomes dos bancos de dados em ambos os servidores são os mesmos, se isso ajudar. E como eu disse anteriormente, eu tenho a saconta para que eu possa fazer o que for necessário para obter os dados de A para B. Ambos os servidores também estão no mesmo farm de servidores.

Um colega meu me enviou uma pergunta interessante, que não consigo explicar totalmente.

Ele executou algum código (incluído abaixo) e obteve resultados um tanto inesperados.

Essencialmente, ao converter um tipo UniqueIdentifier(ao qual me referirei Guiddaqui em diante) para um binary(ou varbinary), a ordem da primeira metade do resultado é inversa, mas a segunda metade não.

Meu primeiro pensamento foi que a endianidade do sistema era a causa e que a Guidexibição foi preservada, mas a binaryforma não é garantida.

Obviamente, este é um detalhe de implementação, mas eu queria saber se havia uma boa explicação para isso.

Código:

declare @guid uniqueidentifier = '8A737954-CBEC-40CE-A534-2AFFB5A0E207';
declare @binary binary(16) = (select convert(binary(16), @guid));
select @guid as [GUID], @binary as [Binary];

Resultados:

GUID                                 Binary
8A737954-CBEC-40CE-A534-2AFFB5A0E207 0x5479738AECCBCE40A5342AFFB5A0E207

Como você pode ver, a primeira metade do Guid(todo o caminho até 40CE) é armazenada para trás para cada seção. Ou seja, a primeira seção do Guidestá invertida, depois a segunda seção, depois a terceira seção, mas a ordem das seções é preservada. Depois disso, as duas últimas seções são armazenadas na ordem exata em que aparecem no arquivo Guid.

Alguém pode explicar isso? (Um conjunto de teste maior está incluído abaixo.)

Código:

declare @guid_to_binary table
(
    [id] int identity(1,1),
    [guid] uniqueidentifier,
    [binary_conversion] binary(16)
);
declare @i int = 1;
while @i <= 100
begin
    insert into @guid_to_binary ( [guid] )
    select newid();
    set @i = @i + 1;
end
update @guid_to_binary
set binary_conversion = convert(binary(16), [guid]);
select * from @guid_to_binary;

Resultados:

id  guid                                    binary_conversion
1   EBA4DE58-D35F-46D9-9049-7939381F1A3B    0x58DEA4EB5FD3D94690497939381F1A3B
2   0445A8B7-B2F1-4B35-A4DC-92133570CD69    0xB7A84504F1B2354BA4DC92133570CD69
3   1006278C-1EB6-44A0-B8B0-3392A2EB0922    0x8C270610B61EA044B8B03392A2EB0922
4   EAB330DE-5984-49D6-9B84-F22823A3DB34    0xDE30B3EA8459D6499B84F22823A3DB34
5   25DA0D33-85A3-4AD8-9505-04B85DA6817F    0x330DDA25A385D84A950504B85DA6817F
6   B17B6263-C12B-46C2-BF30-D5AD0F3F765C    0x63627BB12BC1C246BF30D5AD0F3F765C
7   7B58993A-7207-45EC-AE72-B84D4BDE3C1A    0x3A99587B0772EC45AE72B84D4BDE3C1A
8   53772C37-80F6-4DBD-AE3F-3C105E857DFF    0x372C7753F680BD4DAE3F3C105E857DFF
9   D15F9608-CB18-4459-B57A-0D3ACC0421BF    0x08965FD118CB5944B57A0D3ACC0421BF
10  047B539A-80D4-4D22-9E9C-E3642E4AC5E5    0x9A537B04D480224D9E9CE3642E4AC5E5
11  467D192A-AAAF-403E-952D-82EB3B50C123    0x2A197D46AFAA3E40952D82EB3B50C123
12  88F2F0B5-D425-4B29-B774-487BFB30A4F4    0xB5F0F28825D4294BB774487BFB30A4F4
13  99ACBAC2-5F3C-4E57-A83E-088FE4FDA7E9    0xC2BAAC993C5F574EA83E088FE4FDA7E9
14  78D21192-F09B-4C14-B1FB-1DC0617CF23E    0x9211D2789BF0144CB1FB1DC0617CF23E
15  947BBFE5-B6F1-4833-9F44-4F4D6C45C742    0xE5BF7B94F1B633489F444F4D6C45C742
16  B528A36F-776B-4B43-85A2-A17D7D83BF07    0x6FA328B56B77434B85A2A17D7D83BF07
17  434D31D5-A659-475F-AC73-1D59479338F8    0xD5314D4359A65F47AC731D59479338F8
18  A9629EB2-1333-4C26-B5FA-2FB13961324C    0xB29E62A93313264CB5FA2FB13961324C
19  8D2F34CA-ADCB-4497-9568-33EBCE49DADA    0xCA342F8DCBAD9744956833EBCE49DADA
20  5A6A6C52-16AA-4CEF-B6DC-12C6AF40E1F5    0x526C6A5AAA16EF4CB6DC12C6AF40E1F5
21  ED48087D-C9F8-416B-A132-DFE6CBCAD1CF    0x7D0848EDF8C96B41A132DFE6CBCAD1CF
22  D623DF04-A284-4FA6-9CCA-3830E824EA79    0x04DF23D684A2A64F9CCA3830E824EA79
23  918E9032-535F-4FEE-9D28-FF756245A334    0x32908E915F53EE4F9D28FF756245A334
24  2307C209-DF28-4CB4-8A4B-3CB56C9A7094    0x09C2072328DFB44C8A4B3CB56C9A7094
25  9A86F2FE-353D-4BCE-81D0-9CB5BAB04FCA    0xFEF2869A3D35CE4B81D09CB5BAB04FCA
26  4C7D144E-E0E6-4D3C-BC42-D13EBC262096    0x4E147D4CE6E03C4DBC42D13EBC262096
27  AD0290F0-2A44-4FF8-AA82-B9693DF4AC16    0xF09002AD442AF84FAA82B9693DF4AC16
28  A4FC0E4E-0420-4841-AC75-19722D5C4050    0x4E0EFCA420044148AC7519722D5C4050
29  35F08F53-4E26-4CB1-B3AD-FF04898179E1    0x538FF035264EB14CB3ADFF04898179E1
30  5D4CE77D-6FE2-405B-AE1E-3DBB48C990BF    0x7DE74C5DE26F5B40AE1E3DBB48C990BF
31  019E9470-FA72-48D3-A6A3-7EC979BE7D5C    0x70949E0172FAD348A6A37EC979BE7D5C
32  841B906A-97DF-4326-8215-0CE9B0552288    0x6A901B84DF97264382150CE9B0552288
33  62922CA0-E540-490B-9B6D-258C0CF67CB3    0xA02C926240E50B499B6D258C0CF67CB3
34  607BC8CD-96A0-4460-835C-61CFF1DAB602    0xCDC87B60A0966044835C61CFF1DAB602
35  22ED2548-00C1-4E8C-AEA7-7C310CF9DFCD    0x4825ED22C1008C4EAEA77C310CF9DFCD
36  1B88FEB1-E96C-4AFA-BBFA-26A3444E4640    0xB1FE881B6CE9FA4ABBFA26A3444E4640
37  A7BFA1D4-2679-48D6-B4B5-1EB01AA93010    0xD4A1BFA77926D648B4B51EB01AA93010
38  22F3F3B3-D461-48D7-92F9-3BA6CFFFD513    0xB3F3F32261D4D74892F93BA6CFFFD513
39  22934FB8-443B-44C9-81FF-680C4C7D75F5    0xB84F93223B44C94481FF680C4C7D75F5
40  17B54276-0F8B-4FA6-8DDD-BAB02F55E6CC    0x7642B5178B0FA64F8DDDBAB02F55E6CC
41  8AA9D81D-330A-4E97-BE4B-3EA0F5937753    0x1DD8A98A0A33974EBE4B3EA0F5937753
42  50604CA3-5687-4C95-9562-277D29C67B18    0xA34C60508756954C9562277D29C67B18
43  C873B716-AAC5-4D35-BEFE-6B8069963DB8    0x16B773C8C5AA354DBEFE6B8069963DB8
44  E7638B30-A123-45A6-B93A-33DDBC359EA0    0x308B63E723A1A645B93A33DDBC359EA0
45  83F66044-5F98-45C2-A67D-01A1363FCFED    0x4460F683985FC245A67D01A1363FCFED
46  7478BC8F-E530-425B-B2CA-6A31C636F6B4    0x8FBC787430E55B42B2CA6A31C636F6B4
47  4881BB26-851E-4BF7-B62D-5A3DB3B0601C    0x26BB81481E85F74BB62D5A3DB3B0601C
48  C2A9ABB6-EF5E-4BF5-9574-81BEF6699D19    0xB6ABA9C25EEFF54B957481BEF6699D19
49  EB6637F4-08F3-4C4F-B46F-83CC74938562    0xF43766EBF3084F4CB46F83CC74938562
50  629839CE-806E-4F74-B897-A0F624A942F3    0xCE3998626E80744FB897A0F624A942F3
51  C63195B8-0458-46D0-BCA9-2012342145B5    0xB89531C65804D046BCA92012342145B5
52  08928C1B-B481-46D9-898F-84503C8B934C    0x1B8C920881B4D946898F84503C8B934C
53  3D9113FE-20CA-4136-8C6D-A18FAE50F6D0    0xFE13913DCA2036418C6DA18FAE50F6D0
54  83206C44-8F70-4952-83D5-80D8CCD02255    0x446C2083708F524983D580D8CCD02255
55  D591124E-5808-45F3-8A0E-F4BB68B733E5    0x4E1291D50858F3458A0EF4BB68B733E5
56  CE49BC3F-02AC-4536-B2FA-148845ED0EE0    0x3FBC49CEAC023645B2FA148845ED0EE0
57  F9663BCB-17FC-43B7-AF7F-B87518C17A4D    0xCB3B66F9FC17B743AF7FB87518C17A4D
58  BEE8F892-185D-49E7-8AA0-0F48E9404DC5    0x92F8E8BE5D18E7498AA00F48E9404DC5
59  DB9CC990-76C8-4F4B-A480-F82EA3BD3B6D    0x90C99CDBC8764B4FA480F82EA3BD3B6D
60  336ACE62-E185-4D1A-9217-E6A742AEA8A4    0x62CE6A3385E11A4D9217E6A742AEA8A4
61  075FBCEE-F473-40BB-82E2-D419036D9662    0xEEBC5F0773F4BB4082E2D419036D9662
62  255BD1CA-466C-4EBD-A769-0AEDA5ECDCBF    0xCAD15B256C46BD4EA7690AEDA5ECDCBF
63  604D2442-F88A-4034-9F8D-D0E36904A013    0x42244D608AF834409F8DD0E36904A013
64  0D58AAC6-47F1-4ACE-918C-AFFB639A45AB    0xC6AA580DF147CE4A918CAFFB639A45AB
65  8EE4C9E5-9C69-47A4-B4AB-6DF56FAFDD17    0xE5C9E48E699CA447B4AB6DF56FAFDD17
66  2EBA4B09-99EF-42A4-8BDF-C1F4EAEB37DD    0x094BBA2EEF99A4428BDFC1F4EAEB37DD
67  B25B5C1B-9233-411B-A781-D7F8427DB93A    0x1B5C5BB233921B41A781D7F8427DB93A
68  8747A125-F8DD-426F-8B31-A11DE7FED067    0x25A14787DDF86F428B31A11DE7FED067
69  744CE50A-5CCE-4A65-A502-3C165E7C1A13    0x0AE54C74CE5C654AA5023C165E7C1A13
70  AB990702-7B8C-427F-A1FF-22A2236B3BB2    0x020799AB8C7B7F42A1FF22A2236B3BB2
71  CDBFF8A2-7C4C-429C-AF0D-8D6AB1F376F6    0xA2F8BFCD4C7C9C42AF0D8D6AB1F376F6
72  0558FA3A-FF34-4A02-A32F-8017BBE0D98E    0x3AFA580534FF024AA32F8017BBE0D98E
73  C42E5C3B-7CEB-4B13-B0C7-B1A5436FA95F    0x3B5C2EC4EB7C134BB0C7B1A5436FA95F
74  B577B02E-2925-4F07-ABA0-FC981C126C0A    0x2EB077B52529074FABA0FC981C126C0A
75  20C77BE2-656B-4E4E-89AC-1E07D11B595F    0xE27BC7206B654E4E89AC1E07D11B595F
76  7192D091-B9B3-4D48-B44B-EC7ACDDEB800    0x91D09271B3B9484DB44BEC7ACDDEB800
77  6BD846C9-5EB8-40DE-92DB-844E3DF1827D    0xC946D86BB85EDE4092DB844E3DF1827D
78  009B2D21-1545-4C15-966E-D5EC31B45BE5    0x212D9B004515154C966ED5EC31B45BE5
79  7554AB43-1E1B-47CC-8AAA-248C86A590FA    0x43AB54751B1ECC478AAA248C86A590FA
80  6F46275B-F85F-426F-A9F6-6D923BA1F055    0x5B27466F5FF86F42A9F66D923BA1F055
81  63697CB0-68C8-4F91-9CCB-F28B04345F3A    0xB07C6963C868914F9CCBF28B04345F3A
82  7C3C36B0-7B71-4E8B-A14A-884CF61B3368    0xB0363C7C717B8B4EA14A884CF61B3368
83  8DBA33D7-83BC-4D74-A219-7AA0E25104FD    0xD733BA8DBC83744DA2197AA0E25104FD
84  917E3848-A028-41A4-9BC4-BA40625F1FF7    0x48387E9128A0A4419BC4BA40625F1FF7
85  9B87B6BA-7256-44F2-8AB7-516F8D906056    0xBAB6879B5672F2448AB7516F8D906056
86  6837D3BB-91FD-4E2A-AED5-6B8017004C54    0xBBD33768FD912A4EAED56B8017004C54
87  8E9FCD91-D9B4-465E-B1DF-2C3126FFA402    0x91CD9F8EB4D95E46B1DF2C3126FFA402
88  CB2E6016-5B09-40B5-AFD2-C989724DFED0    0x16602ECB095BB540AFD2C989724DFED0
89  F282E16A-068E-49D2-B65C-915214D06A04    0x6AE182F28E06D249B65C915214D06A04
90  6EDA33EB-8482-4BD8-9979-BB278C8D2C6F    0xEB33DA6E8284D84B9979BB278C8D2C6F
91  F4DC0E3B-B693-42C5-B5BD-07FC1A9AE8B9    0x3B0EDCF493B6C542B5BD07FC1A9AE8B9
92  24F12333-1B03-4EC4-A9A2-308A6F3CE996    0x3323F124031BC44EA9A2308A6F3CE996
93  5CCB94FC-F846-4EF3-ACF8-1DBAA0EB5405    0xFC94CB5C46F8F34EACF81DBAA0EB5405
94  9E8BC4B4-528F-42E3-B403-1A55F74420C3    0xB4C48B9E8F52E342B4031A55F74420C3
95  9F1BF452-31EC-486A-B572-4338319FC67E    0x52F41B9FEC316A48B5724338319FC67E
96  00B72F09-78AD-4BE5-9785-FA2939DE581A    0x092FB700AD78E54B9785FA2939DE581A
97  EC5A3AEF-3255-424D-8174-CBAEE0A2D243    0xEF3A5AEC55324D428174CBAEE0A2D243
98  48BC3E92-F3FA-47B8-881E-431D43118C99    0x923EBC48FAF3B847881E431D43118C99
99  C72A84F3-E961-4F6E-87AA-7A12D2EAD032    0xF3842AC761E96E4F87AA7A12D2EAD032
100 3F0E1666-BD80-4562-8FE3-CFA2009FDD08    0x66160E3F80BD62458FE3CFA2009FDD08

Então aqui está o meu cenário:

Estou trabalhando na localização para um projeto meu e normalmente faria isso no código C#, mas quero fazer isso no SQL um pouco mais, pois estou tentando melhorar um pouco meu SQL.

Ambiente: SQL Server 2014 Standard, C# (.NET 4.5.1)

Nota: a linguagem de programação em si deve ser irrelevante, estou incluindo apenas para completar.

Então eu meio que consegui o que eu queria, mas não na medida que eu queria. Já faz um tempo (pelo menos um ano) desde que eu fiz qualquer SQL JOIN, exceto os básicos, e isso é bastante complexo JOIN.

Aqui está um diagrama das tabelas relevantes do banco de dados. (Há muito mais, mas não é necessário para esta parte.)

Todos os relacionamentos descritos na imagem estão completos no banco de dados - as restrições PKe FKestão todas configuradas e operacionais. Nenhuma das colunas descritas é nullcapaz. Todas as tabelas têm o esquema dbo.

Agora, eu tenho uma consulta que quase faz o que eu quero: ou seja, dado ANY Id of SupportCategoriese ANY Id of Languages, ela retornará:

Se houver uma tradução correta para esse idioma para essa string (ou seja StringKeyId-> StringKeys.Idexiste, e em LanguageStringTranslations StringKeyId, LanguageId, e StringTranslationIdcombinação existe, então ele carrega StringTranslations.Textpara esse StringTranslationId.

Se a combinação LanguageStringTranslations StringKeyId, LanguageIde NÃO existir, ele carrega o valor. O é um dado .StringTranslationIdStringKeys.NameLanguages.Idinteger

Minha consulta, seja uma bagunça, é a seguinte:

SELECT CASE WHEN T.x IS NOT NULL THEN T.x ELSE (SELECT
    CASE WHEN dbo.StringTranslations.Text IS NULL THEN dbo.StringKeys.Name ELSE dbo.StringTranslations.Text END AS Result
FROM dbo.SupportCategories
    INNER JOIN dbo.StringKeys
        ON dbo.SupportCategories.StringKeyId = dbo.StringKeys.Id
    INNER JOIN dbo.LanguageStringTranslations
        ON dbo.StringKeys.Id = dbo.LanguageStringTranslations.StringKeyId
    INNER JOIN dbo.StringTranslations
        ON dbo.StringTranslations.Id = dbo.LanguageStringTranslations.StringTranslationId
WHERE dbo.LanguageStringTranslations.LanguageId = 38 AND dbo.SupportCategories.Id = 0) END AS Result FROM (SELECT (SELECT
    CASE WHEN dbo.StringTranslations.Text IS NULL THEN dbo.StringKeys.Name ELSE dbo.StringTranslations.Text END AS Result
FROM dbo.SupportCategories
    INNER JOIN dbo.StringKeys
        ON dbo.SupportCategories.StringKeyId = dbo.StringKeys.Id
    INNER JOIN dbo.LanguageStringTranslations
        ON dbo.StringKeys.Id = dbo.LanguageStringTranslations.StringKeyId
    INNER JOIN dbo.StringTranslations
        ON dbo.StringTranslations.Id = dbo.LanguageStringTranslations.StringTranslationId
WHERE dbo.LanguageStringTranslations.LanguageId = 5 AND dbo.SupportCategories.Id = 0) AS x) AS T

O problema é que ele não é capaz de me fornecer TODOS os SupportCategoriese seus respectivos StringTranslations.Textse existir, OU seus StringKeys.Namese não existirem. É perfeito em fornecer qualquer um deles, mas não em todos. Basicamente, é para impor que, se um idioma não tiver uma tradução para uma chave específica, o padrão é usar o StringKeys.Nameque é de StringKeys.DefaultLanguageIdtradução. (Idealmente, nem isso faria, mas, em vez disso, carregaria a tradução para StringKeys.DefaultLanguageId, o que eu mesmo posso fazer se apontar na direção certa para o restante da consulta.)

Eu gastei muito tempo nisso, e eu sei que se eu fosse apenas escrever em C# (como eu costumo fazer) já estaria feito. Eu quero fazer isso no SQL e estou tendo problemas para obter a saída que eu gosto.

A única ressalva é que quero limitar o número de consultas reais aplicadas. Todas as colunas são indexadas e, como eu gosto delas por enquanto, e sem testes de estresse reais, não posso indexá-las ainda mais.

Edit: Outra observação, estou tentando manter o banco de dados o mais normalizado possível, então não quero duplicar as coisas se puder evitá-lo.

Dados de exemplo

Fonte

dbo.SupportCategories (Inteira):

Id  StringKeyId
0   0
1   1
2   2

dbo.Languages ​​(185 registros, mostrando apenas dois como exemplo):

Id  Abbreviation    Family  Name    Native
38  en  Indo-European   English English
48  fr  Indo-European   French  français, langue française

dbo.LanguagesStringTranslations (Inteira):

StringKeyId LanguageId  StringTranslationId
0   38  0
1   38  1
2   38  2
3   38  3
4   38  4
5   38  5
6   38  6
7   38  7
1   48  8 -- added as example

dbo.StringKeys (Inteira):

Id  Name    DefaultLanguageId
0   Billing 38
1   API 38
2   Sales   38
3   Open    38
4   Waiting for Customer    38
5   Waiting for Support 38
6   Work in Progress    38
7   Completed   38

dbo.StringTranslations (Inteira):

Id  Text
0   Billing
1   API
2   Sales
3   Open
4   Waiting for Customer
5   Waiting for Support
6   Work in Progress
7   Completed
8   Les APIs -- added as example

Saída de corrente

Dada a consulta exata abaixo, ele gera:

Result
Billing

Saída Desejada

Idealmente, eu gostaria de poder omitir o específico SupportCategories.Ide obter todos eles, assim (independentemente se o idioma 38 Englishfoi usado, ou 48 French, ou QUALQUER outro idioma no momento):

Id  Result
0   Billing
1   API
2   Sales

Exemplo Adicional

Dado que eu deveria adicionar uma localização para French(ou seja, adicionar 1 48 8a LanguageStringTranslations), a saída mudaria para (nota: este é apenas um exemplo, obviamente eu adicionaria uma string localizada a StringTranslations) (atualizado com exemplo em francês):

Result
Les APIs

Saída Desejada Adicional

Dado o exemplo acima, a seguinte saída seria desejada (atualizada com o exemplo em francês):

Id  Result
0   Billing
1   Les APIs
2   Sales

(Sim, eu sei que tecnicamente isso está errado do ponto de vista da consistência, mas é o que seria desejado na situação.)

Editar:

Pequena atualização, alterei a estrutura da dbo.Languagestabela, eliminei a Id (int)coluna dela e a substituí por Abbreviation(que agora é renomeada para Id, e todas as chaves estrangeiras e relacionamentos relativos atualizados). Do ponto de vista técnico, esta é uma configuração mais apropriada na minha opinião devido ao fato de que a tabela está limitada aos códigos ISO 639-1, que são exclusivos para começar.

Tl; dr

Então: a pergunta, como eu poderia modificar essa consulta para retornar tudo eSupportCategories depois retornar StringTranslations.Textpara isso StringKeys.Id, Languages.Idcombinação ouStringKeys.Name se NÃO existisse?

Meu pensamento inicial é que eu poderia de alguma forma converter a consulta atual para outro tipo temporário como outra subconsulta e envolver essa consulta em outra SELECTinstrução e selecionar os dois campos que quero ( SupportCategories.Ide Result).

Se eu não encontrar nada, farei apenas o método padrão que normalmente uso, que é carregar tudo SupportCategoriesno meu projeto C# e, em seguida, executar a consulta que tenho acima manualmente em cada arquivo SupportCategories.Id.

Obrigado por todas e quaisquer sugestões/comentários/críticas.

Além disso, peço desculpas por ser absurdamente longo, só não quero nenhuma ambiguidade. Estou frequentemente no StackOverflow e vejo perguntas que carecem de substância, não queria cometer esse erro aqui.