SQL Server - Design de tabela física - Ordem das colunas - Colunas nulas e não nulas

Estou postando isso aqui como uma resposta para que eu tenha mais espaço para postar recursos e links, além de explicar um pouco.

Para responder à pergunta específica sobre colunas NULLABLE vs NON-NULLABLE e sua cardinalidade relativa, Kimberly Tripp tem um artigo aqui: https://www.sqlskills.com/blogs/kimberly/column-order-doesnt-matter-generally-but- depende/

Infelizmente, não consigo encontrar a referência que suporta minha afirmação sobre ter colunas de largura variável no final da lista de ordem de coluna, mas o raciocínio era que atualizar uma coluna de largura variável no final de uma linha seria menos provável de causar fragmentação (e desligar -page spill) do que um no meio da ordem das colunas.

Mas, como eu disse anteriormente, é difícil acertar e, a menos que você saiba exatamente seus padrões de acesso a dados, provavelmente errará. Existem muitas outras frutas fáceis de ajustar, preocupando-se com a ordem das colunas. Alternar para comprimentos de coluna estática de tamanho adequado seria uma maneira mais fácil de obter melhores estimativas de memória e evitar a fragmentação.

Índices com fatores de preenchimento adequados, atualizações de estatísticas, procedimentos armazenados para acesso a dados (para que você possa controlar com mais rigor os planos de execução) e muito mais. Se você estiver na edição corporativa, terá mais opções, a compactação de tabelas permitirá que você coloque mais dados na memória e isso é muito mais fácil (e mais amplamente aplicável), o particionamento permitirá que você faça coisas muito legais no lado do gerenciamento (embora use acelerar suas leituras é um desafio (a eliminação de partição é outra coisa difícil de acertar)).

Observe que, quando digo difícil de acertar, geralmente quero dizer que é difícil fazer com que o SQL os use da melhor maneira, portanto, obter ROI pelo tempo gasto na otimização geralmente é muito melhor gasto em outros lugares.

Para responder à última seção da pergunta na seção EDITAR. Sim, pré-carregar a coluna com chaves primárias, chaves estrangeiras e outras colunas de junção provavelmente ajudaria. Mas se você tiver índices nesses, não importa, pois o índice será usado para determinar quais linhas recuperar e, em seguida, uma pesquisa de chave será executada. Eu só não acho que gastar tempo com arquitetura valha o impacto. Você provavelmente terá um índice nessas colunas em qualquer caso.

Caso 1: Não nulo Primeiro

 CREATE TABLE testspace 
                 ( 
                              id       INT IDENTITY PRIMARY KEY , 
                              code     CHAR(20) NOT NULL, 
                              NAME     VARCHAR(100) NOT NULL, 
                              address1 VARCHAR(200), 
                              address2 VARCHAR(200) 
                 )
CREATE NONCLUSTERED INDEX ix_code_testspce ON testspace 
                              ( 
                                                        code 
                              ) 
                              include 
                              ( 
                                                        NAME 
                              )

DECLARE @i INT=0

WHILE(@i<=500000) 
    BEGIN 
      IF(@i%10=0) 
      INSERT INTO testspace VALUES 
                  ( 
                              Replicate('code',5), 
                              Replicate('name',25), 
                              Replicate('add1',50), 
                              Replicate('add2',50) 
                  ) 
      ELSE 
      INSERT INTO testspace VALUES 
                  ( 
                              Replicate('code',5), 
                              Replicate('name',25), 
                              NULL, 
                              NULL 
                  ) 
      SET @i=@i+1 
    ENDsp_spaceused N'TestSpace' 
    --select * from sys.dm_db_database_page_allocations(db_id('DbName'),OBJECT_ID('TestSpace'),1,null,'DETAILED')

SELECT *
    FROM   sys.Dm_db_index_physical_stats(Db_id('DBName'),Object_id('TestSpace'),1,NULL,'DETAILED')

Caso 2: Coluna Nula Primeiro

        CREATE TABLE testspace1 
  ( 
     address1 VARCHAR(200), 
     address2 VARCHAR(200), 
     NAME     VARCHAR(100) NOT NULL, 
     id       INT IDENTITY PRIMARY KEY, 
     code     CHAR(20) NOT NULL 
  ) 

CREATE NONCLUSTERED INDEX ix_code_testspce1 
  ON testspace1(code) 
  include(NAME) 

DECLARE @i INT=0 

WHILE( @i <= 500000 ) 
  BEGIN 
      IF( @i%10 = 0 ) 
        INSERT INTO testspace1 
                    (address1, 
                     address2, 
                     NAME, 
                     code) 
        VALUES     (Replicate('add1', 50), 
                    Replicate('add2', 50), 
                    Replicate('name', 25), 
                    Replicate('code', 5)) 
      ELSE 
        INSERT INTO testspace1 
                    (address1, 
                     address2, 
                     NAME, 
                     code) 
        VALUES     (NULL, 
                    NULL, 
                    Replicate('name', 25), 
                    Replicate('code', 5)) 

      SET @i=@i + 1 
  END 

SELECT * 
FROM   sys.Dm_db_index_physical_stats(Db_id('Fixture20180417'), 
       Object_id('TestSpace1') 
       , -1, NULL, 'DETAILED') 

Clustered indexe Non Clustered Indexsão criados intencionalmente para verificar Page Count, fragmentation, Contagem de registros em cada página etc. de cada tipo de índice.

Descobri que Fragment_Count, Page_Count, record_countsão pouco mais no Caso 2.

Portanto , espera-se que o Caso 1 tenha um desempenho MELHOR .

Como eu disse, não tenho permissão para executar DBCC Page, DBCC Show_Statisticsetc.

Você pode fazer um experimento semelhante e verificar você mesmo e postar sua experiência aqui. Você pega seu exemplo de tabela real e importante.