Meu conhecimento de SQL é limitado, portanto, os termos que usarei provavelmente não são os corretos.
Eu tenho uma tabela que armazenará resultados de testes para vários locais.
Os testes serão registrados em diferentes bancos de dados em diferentes locais (sem conexão de rede) e o local "mestre" irá "importar" regularmente os resultados do teste de outros locais.
Pretendo ter uma chave primária composta agrupada nas colunas LocationId (int) e Date (datetime), nessa ordem. O raciocínio é que ele deve manter todos os resultados de um local juntos e quase nunca farei uma consulta por intervalo de datas, mas sim por intervalo de datas e local.
O tamanho da linha será de 80 a 100 bytes e o número de resultados de teste não deve exceder alguns milhões. Uma "importação" típica inserirá de 50 a 100 mil resultados de outro local.
O que acontecerá durante as importações? O SQL "moverá" as linhas existentes para manter o agrupamento ou permitirá que a tabela se torne "fragmentada"? Isso poderia causar um grande impacto no desempenho se a importação for feita uma linha por vez? Devo simplesmente não me preocupar com a ordem das linhas e apenas adicionar uma coluna de identidade como chave primária e um índice na coluna Data para ajudar em minhas consultas?
Caramba, você tem um monte de perguntas aqui. Vamos quebrar isso.
P: O SQL "moverá" as linhas existentes para manter o agrupamento ou permitirá que a tabela se torne "fragmentada"?
Pense em um banco de dados como uma coleção de páginas - literalmente pedaços de papel dispostos em sua mesa. Pense no dicionário por enquanto. Se você quiser adicionar mais palavras ao dicionário, poderá adicioná-las no lugar se as páginas tiverem espaço vazio.
Quando você começa com um dicionário vazio, isso é relativamente fácil. Mas pense em um dicionário maduro com milhares de páginas de papel, todas cheias.
Quando você quiser adicionar mais palavras a esse dicionário maduro, é provável que não haja espaço na página. O SQL Server "rasgará" uma página - levará uma nova página para outro lugar e moverá algumas das palavras para essa nova página. A nova página estaria no final do dicionário. A boa notícia é que imediatamente após essa ação, agora há uma página meio vazia no final do seu dicionário e também no meio, ambas com espaço para adicionar palavras.
Se acontecer de você adicioná-los nessa ordem, é isso. (É por isso que a maneira como você carrega os dados se torna cada vez mais importante.)
Isso poderia causar um grande impacto no desempenho se a importação for feita uma linha por vez?
Esqueça o índice por um segundo - adicionar dados uma linha por vez é simplesmente ineficiente, independentemente da estrutura de indexação. O SQL Server é um sistema baseado em conjunto - sempre que você puder trabalhar em conjuntos, provavelmente deveria.
O que acontece quando eu consulto os dados?
Você não perguntou isso, mas eu estou pedindo por você, hahaha.
Pense nas consequências de nossas inserções. Agora temos um dicionário que está quase todo ordenado, mas quando você chegar a alguns pontos do dicionário, terá que pular para trás para ler algumas outras páginas. Se todas essas páginas estiverem armazenadas em cache em sua memória (RAM, buffer pool etc.), a sobrecarga não será tão grande. A maior parte do acesso à memória é aleatório de qualquer maneira - não é como se o SQL Server armazenasse seu dicionário na memória em ordem.
Por outro lado, se você precisar buscar os dados de discos rígidos magnéticos convencionais (ferrugem giratória), poderá obter um pequeno benefício de desempenho se os dados forem armazenados em ordem. O objetivo real do projeto aqui, no entanto, é obter os dados da RAM em vez de obtê-los das unidades. A diferença entre dados desfragmentados no disco versus dados fragmentados no disco não chega nem perto de ser tão significativa quanto a diferença entre obtê-los do disco e obtê-los da RAM .
Devo simplesmente não me preocupar com a ordem das linhas e apenas adicionar uma coluna de identidade como chave primária e um índice na coluna Data para ajudar em minhas consultas?
Bingo: esta é a diferença entre o design de banco de dados físico e o design de banco de dados lógico. Os programadores precisam se preocupar muito com o design físico do banco de dados inicialmente, mas contanto que seu banco de dados esteja abaixo de, digamos, 100 GB de tamanho, você pode corrigir o design lógico na postagem, por assim dizer. Coloque um campo de identidade lá para começar, agrupe-o e, depois de estar ativo por alguns meses, revise o design do índice para maximizar o desempenho.
Agora, tendo dito isso, uma vez que você tenha experiência com esse tipo de tomada de decisão, estará mais bem equipado para estimar índices desde o início. Mesmo assim, eu nem costumo pensar muito no design do índice inicialmente. Os usuários nunca parecem consultar os dados da maneira que eu esperava.