locked
Ordem das Colunas na chave primária, como organizar ? RRS feed

  • Pergunta

  • Pessoal,

                Tenho criado muitos modelos de dados SQL Server, sempre observando normas básicas de modelagem de dados.  Uma das preocupações é organizar a PK, da coluna mais restritiva para a menos restritiva, garantindo uma organização lógica das páginas e facilitando a busca. Ao menos foi assim que sempre organizei as colunas em qualquer banco relacional.

                 Busquei documentação MS para explicação de como esse tipo de organização, seria ou não mais performático para o SQL Server, sem resultado.

                  Pregunto aos senhores, existe alguma diferença para o SGBD, quando organizo as colunas na PK da coluna mais restritiva para a menos restritiva ?

    * Coluna mais restritiva é aquela com mais incidências de registros únicos e que pouco se repetem.

    *Coluna menos restritiva é aquela com grande numero de registros duplicados / repetidos. 

    Desde já agradeço o apoio

    Nilton Morais

      


    Nilton Pereira de Morais Administrador de Dados


    segunda-feira, 26 de maio de 2014 18:21

Respostas

  • Nilton,

    Realmente existe sim algumas históricas e até mesmo contos de fadas que dizem que tudo pode influenciar e variar o comportamento do SQL Server no momento da Execução e Armazenamento dos dados.

    Há algum tempo eu encontrei um artigo no SQLServerCentral que falava justamente do impacto na criação de indices de acordo com as colunas.

    Com base, neste cenário, eu escrevi um artigo apresentando este tipo de comportamento, veja o código de exemplo que utilizei:

    Create Database Exemplos
    
    --Criando as Tabelas fisicamente--
    CREATE TABLE dbo.Tabela1 
    (Coluna1 int NOT NULL,
     Coluna2 int NULL,
     Coluna3 int NULL,
     Coluna4 varchar(50) NULL)
    GO
    
    CREATE TABLE dbo.Tabela2 
    (Coluna1 int NOT NULL,
     Coluna2 int NULL,
     Coluna3 int NULL,
     Coluna4 varchar(50) NULL)
    GO
    
    -- Adicionando Primary Key --
    ALTER TABLE dbo.Tabela1 
     ADD CONSTRAINT PK_Tabela1 PRIMARY KEY CLUSTERED (Coluna1) 
    GO
    
    ALTER TABLE dbo.Tabela2 
     ADD CONSTRAINT PK_Tabela2 PRIMARY KEY CLUSTERED (Coluna1)
    GO
    
    -- Populando as tabelas --
    DECLARE @valor INT
    
    SET @valor=1
    
    WHILE @valor < 1000
    BEGIN  
       INSERT INTO dbo.Tabela1(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
       INSERT INTO dbo.Tabela2(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
       SET @valor=@valor+1
    END
    GO
    
    -- Criando Índice na Tabela1 com Multiplas Colunaunas --
    CREATE NONCLUSTERED INDEX IND_Tabela1_Coluna2_Coluna3 
    ON dbo.Tabela1 (Coluna3,Coluna2)
     WITH (STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, 
                ALLOW_ROW_LOCKS = ON, 
                ALLOW_PAGE_LOCKS = ON) 
      ON [PRIMARY]
    GO
    
    --Ativando a exibição de informações sobre Estatísticas com retorno em Tela--
    SET STATISTICS IO ON
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 WHERE Coluna3=99
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 WHERE Coluna2=99
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                    ON dbo.Tabela1.Coluna2 = dbo.Tabela2.Coluna1
     WHERE dbo.Tabela1.Coluna3=255       
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                    ON dbo.Tabela1.Coluna3 = dbo.Tabela2.Coluna1
     WHERE dbo.Tabela1.Coluna2=255       
    GO
    
    --Desativando a exibição de informações sobre Estatísticas com retorno em Tela--
    SET STATISTICS IO OFF

    Talvez possa servir como base para alguma análise.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 29 de maio de 2014 13:16
    Moderador

Todas as Respostas

  • Bom dia Nilton,

    Também não encontrei material oficial, porém sempre uso a ordem baseado, como você mencionou, na densidade/seletividade. Todas as vezes que crio um índice, lembro que ele tem um funcionamento muito parecido com a lista telefonica, onde que, por os sobrenomes e nomes estarem ordenados (fisicamente), facilita muito minha consulta. Exitem muitos blogs provando o conceito de ordem das colunas, porém nada oficial.

    http://www.mssqltips.com/sqlservertip/2718/sql-server-index-column-order--does-it-matter/

    At.
    Rafael

    • Marcado como Resposta Nilton Morais quarta-feira, 28 de maio de 2014 13:01
    • Não Marcado como Resposta Nilton Morais quarta-feira, 28 de maio de 2014 17:42
    terça-feira, 27 de maio de 2014 11:37
  • Obrigado Rafael !

    Nilton Pereira de Morais Administrador de Dados

    terça-feira, 27 de maio de 2014 14:08
  • Nilton,

    Realmente existe sim algumas históricas e até mesmo contos de fadas que dizem que tudo pode influenciar e variar o comportamento do SQL Server no momento da Execução e Armazenamento dos dados.

    Há algum tempo eu encontrei um artigo no SQLServerCentral que falava justamente do impacto na criação de indices de acordo com as colunas.

    Com base, neste cenário, eu escrevi um artigo apresentando este tipo de comportamento, veja o código de exemplo que utilizei:

    Create Database Exemplos
    
    --Criando as Tabelas fisicamente--
    CREATE TABLE dbo.Tabela1 
    (Coluna1 int NOT NULL,
     Coluna2 int NULL,
     Coluna3 int NULL,
     Coluna4 varchar(50) NULL)
    GO
    
    CREATE TABLE dbo.Tabela2 
    (Coluna1 int NOT NULL,
     Coluna2 int NULL,
     Coluna3 int NULL,
     Coluna4 varchar(50) NULL)
    GO
    
    -- Adicionando Primary Key --
    ALTER TABLE dbo.Tabela1 
     ADD CONSTRAINT PK_Tabela1 PRIMARY KEY CLUSTERED (Coluna1) 
    GO
    
    ALTER TABLE dbo.Tabela2 
     ADD CONSTRAINT PK_Tabela2 PRIMARY KEY CLUSTERED (Coluna1)
    GO
    
    -- Populando as tabelas --
    DECLARE @valor INT
    
    SET @valor=1
    
    WHILE @valor < 1000
    BEGIN  
       INSERT INTO dbo.Tabela1(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
       INSERT INTO dbo.Tabela2(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
       SET @valor=@valor+1
    END
    GO
    
    -- Criando Índice na Tabela1 com Multiplas Colunaunas --
    CREATE NONCLUSTERED INDEX IND_Tabela1_Coluna2_Coluna3 
    ON dbo.Tabela1 (Coluna3,Coluna2)
     WITH (STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, 
                ALLOW_ROW_LOCKS = ON, 
                ALLOW_PAGE_LOCKS = ON) 
      ON [PRIMARY]
    GO
    
    --Ativando a exibição de informações sobre Estatísticas com retorno em Tela--
    SET STATISTICS IO ON
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 WHERE Coluna3=99
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 WHERE Coluna2=99
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                    ON dbo.Tabela1.Coluna2 = dbo.Tabela2.Coluna1
     WHERE dbo.Tabela1.Coluna3=255       
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                    ON dbo.Tabela1.Coluna3 = dbo.Tabela2.Coluna1
     WHERE dbo.Tabela1.Coluna2=255       
    GO
    
    --Desativando a exibição de informações sobre Estatísticas com retorno em Tela--
    SET STATISTICS IO OFF

    Talvez possa servir como base para alguma análise.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quinta-feira, 29 de maio de 2014 13:16
    Moderador
  • Bom dia Nilton,

    Também não encontrei material oficial, porém sempre uso a ordem baseado, como você mencionou, na densidade/seletividade. Todas as vezes que crio um índice, lembro que ele tem um funcionamento muito parecido com a lista telefonica, onde que, por os sobrenomes e nomes estarem ordenados (fisicamente), facilita muito minha consulta. Exitem muitos blogs provando o conceito de ordem das colunas, porém nada oficial.

    http://www.mssqltips.com/sqlservertip/2718/sql-server-index-column-order--does-it-matter/

    At.
    Rafael

    Rafael,

    Realmente não é nada official, mas esta mudança de comportamento existe sim, inclusive este link que você indicou eu utilizei como estudo em alguns casos.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    • Sugerido como Resposta Durval Ramos sexta-feira, 20 de junho de 2014 14:38
    quinta-feira, 29 de maio de 2014 13:18
    Moderador
  • Nilton,

    Realmente existe sim algumas históricas e até mesmo contos de fadas que dizem que tudo pode influenciar e variar o comportamento do SQL Server no momento da Execução e Armazenamento dos dados.

    Há algum tempo eu encontrei um artigo no SQLServerCentral que falava justamente do impacto na criação de indices de acordo com as colunas.

    Com base, neste cenário, eu escrevi um artigo apresentando este tipo de comportamento, veja o código de exemplo que utilizei:

    Create Database Exemplos
    
    --Criando as Tabelas fisicamente--
    CREATE TABLE dbo.Tabela1 
    (Coluna1 int NOT NULL,
     Coluna2 int NULL,
     Coluna3 int NULL,
     Coluna4 varchar(50) NULL)
    GO
    
    CREATE TABLE dbo.Tabela2 
    (Coluna1 int NOT NULL,
     Coluna2 int NULL,
     Coluna3 int NULL,
     Coluna4 varchar(50) NULL)
    GO
    
    -- Adicionando Primary Key --
    ALTER TABLE dbo.Tabela1 
     ADD CONSTRAINT PK_Tabela1 PRIMARY KEY CLUSTERED (Coluna1) 
    GO
    
    ALTER TABLE dbo.Tabela2 
     ADD CONSTRAINT PK_Tabela2 PRIMARY KEY CLUSTERED (Coluna1)
    GO
    
    -- Populando as tabelas --
    DECLARE @valor INT
    
    SET @valor=1
    
    WHILE @valor < 1000
    BEGIN  
       INSERT INTO dbo.Tabela1(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
       INSERT INTO dbo.Tabela2(Coluna1, Coluna2, Coluna3, Coluna4) VALUES(@valor,@valor,@valor,'Valores')
       SET @valor=@valor+1
    END
    GO
    
    -- Criando Índice na Tabela1 com Multiplas Colunaunas --
    CREATE NONCLUSTERED INDEX IND_Tabela1_Coluna2_Coluna3 
    ON dbo.Tabela1 (Coluna3,Coluna2)
     WITH (STATISTICS_NORECOMPUTE = OFF, 
                IGNORE_DUP_KEY = OFF, 
                ALLOW_ROW_LOCKS = ON, 
                ALLOW_PAGE_LOCKS = ON) 
      ON [PRIMARY]
    GO
    
    --Ativando a exibição de informações sobre Estatísticas com retorno em Tela--
    SET STATISTICS IO ON
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 WHERE Coluna3=99
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 WHERE Coluna2=99
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                    ON dbo.Tabela1.Coluna2 = dbo.Tabela2.Coluna1
     WHERE dbo.Tabela1.Coluna3=255       
    GO
    
    --Removendo todos os buffers limpos do Pool de buffers do SQL Server--
    DBCC DROPCLEANBUFFERS
    GO
    
    --Consultando dados--
    SELECT * FROM dbo.Tabela1 INNER JOIN dbo.Tabela2 
                                                    ON dbo.Tabela1.Coluna3 = dbo.Tabela2.Coluna1
     WHERE dbo.Tabela1.Coluna2=255       
    GO
    
    --Desativando a exibição de informações sobre Estatísticas com retorno em Tela--
    SET STATISTICS IO OFF

    Talvez possa servir como base para alguma análise.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    Pedro, Será que você pode mandar o link deste artigo ou mesmo o link do sqlservercentral.

    obrigado pela ajuda


    Nilton Pereira de Morais Administrador de Dados

    quinta-feira, 29 de maio de 2014 13:21
  • Este post foi encerrado por ser considerado um post antigo.

    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 5 de junho de 2018 18:38
    Moderador