Início / dba / Perguntas / 86065
Accepted
Pavel Nefyodov
Asked: 2014-12-13 11:42:41 +0800 CST

Tabelas idênticas, colunas idênticas, registros idênticos, mas por que tamanhos de registro diferentes?

  • 772

Tabelas idênticas, colunas idênticas, registros idênticos, mas por que tamanhos de registro diferentes?

USE [test]
GO

CREATE TABLE [dbo].[mybit](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
    [col9] [bit] NULL
) 
GO

INSERT INTO [dbo].[mybit]
VALUES (1,1,1,1,1,1,1,1,NULL)
GO 2

DBCC IND(test, mybit, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

Ambos os registros têm 10 bytes de tamanho.

insira a descrição da imagem aqui

CREATE TABLE [dbo].[mybit2](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
) 
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1)
GO

ALTER TABLE [dbo].[mybit2] ADD [col9] [bit] NULL
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1, NULL)
GO

DBCC IND(test, mybit2, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

Um registro tem 9 bytes de tamanho e outro tem 10.

insira a descrição da imagem aqui

sql-server sql-server-2012

4 respostas

  1. Best Answer

    Thomas Cleberg está correto.

    A primeira linha que você insere bit2aparece na página DBCC como

    10000500 ff080000 63

    Isso se decompõe da seguinte forma

    +-------+-----------------+--------+-------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                Comments               |
+-------+-----------------+--------+-------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                  |
| 1     | 00              | 0x00   | TagB                                                  |
| 2-3   | 0500            | 0x0005 | Column Count offset 0x0005 = 5                        |
| 4     | ff              | 0xff   | The bit values. All are 1. 11111111 in binary = 0xff  |
| 5-6   | 0800            | 0x0008 | The column count. 0x0008 = 8. You have 8 columns.     |
| 7     | 00              | 0x00   | The Null bitmap. None are NULL.                       |    
| 8     | 63              | 0x63   | Spare                                                 |
+-------+-----------------+--------+-------------------------------------------------------+

    (Se você está se perguntando por que existe um byte "sobressalente". Isso é explicado aqui . É para permitir que a linha seja substituída por um ponteiro de encaminhamento, caso seja necessário. O valor dele é arbitrário. Para mim foi 0x63. Para você parece que foi 0x00)

    Depois de alterar a tabela e adicionar uma nova coluna com um NULLvalor (ou um valor padrão constante de tempo de execução no SQL Server 2012+ Enterprise Edition), essa é uma alteração apenas de metadados. O SQL Server não atualiza as linhas existentes. Ele pode deduzir da contagem de colunas armazenada na linha que o valor para col9deve ser NULLpara essa linha. A alteração será gravada na próxima vez que a linha for atualizada.

    As linhas inseridas recentemente serão escritas por completo. Dois bytes extras são necessários porque o único byte na seção de dados fixos está cheio de valores para as colunas de bits 1-8, portanto, um byte adicional é necessário para as colunas de bits 9 a 16. Além disso, o bitmap nulo também cresce em um byte. No entanto, não há necessidade de reter esse byte "sobressalente" para manter o tamanho da linha em um nível mínimo, portanto, o efeito líquido é um aumento de um byte.

    +-------+-----------------+--------+------------------------------------------------------------------------------------+
| Bytes | Hex (LSB order) |  Hex   |                                      Comments                                      |
+-------+-----------------+--------+------------------------------------------------------------------------------------+
| 0     | 10              | 0x10   | TagA                                                                               |
| 1     | 00              | 0x00   | TagB                                                                               |
| 2-3   | 0600            | 0x0006 | Column Count offset 0x0006 =6                                                      |
| 4-5   | ff00            | 0xff00 | The bit values. An extra byte has now been added to accomodate the 9th bit column. |
| 6-7   | 0900            | 0x0009 | The column count. You now have 9 columns.                                          |
| 8-9   | 0001            | 0x0100 | The Null bitmap. The 9th column is NULL. 100000000 in binary is 0x0100             |
+-------+-----------------+--------+------------------------------------------------------------------------------------+
    • 4

  2. Tabelas idênticas, colunas idênticas, registros idênticos

    Não é inteiramente verdade. A princípio, eles eram diferentes e a linha 1 foi adicionada [mybit2]quando eles eram diferentes. Sua segunda imagem, a DBCC PAGEsaída para [mybit2], indica claramente esse fato.

    Por que tamanhos de registro diferentes?

    Simplificando: você alterou a definição da tabela e não a reconstruiu . Algumas operações podem ser realizadas instantaneamente (também conhecidas como "online"), pois não há necessidade urgente de refletir imediatamente essas alterações nas linhas existentes:

    • Adicionando um campo NULL
    • Adicionando um campo NOT NULL, não blob/UDT com um padrão que é uma constante de tempo de execução (a partir do SQL Server 2012)
    • Soltando colunas

    • Recuperar espaço após adicionar a opção SPARSE (às vezes) a um campo NULLable (a partir do SQL Server 2008)

      [A página do MSDN para ALTER TABLE tem notas sobre: ​​adicionar colunas NOT NULL com um DEFAULT, descartar colunas e adicionar a SPARSEopção. ]

    Nesses casos, você precisa reconstruir o Clustered Index (ou a tabela se for um heap), que é quando o SQL Server irá passar por cada registro e fazer os ajustes necessários. Faça o seguinte para vê-lo:

    1. largar a [mybit2]mesa
    2. execute seu script novamente para criar [mybit2]com 8 campos, adicione uma linha e, em seguida, ALTER para adicionar o 9º campo e adicione uma linha
    3. exec: DBCC PAGEapenas para garantir que o registro 1 tenha 9 bytes e o registro 2 tenha 10 bytes
    4. agora executivo:ALTER TABLE dbo.mybit2 REBUILD;
    5. exec: DBCC IND(0, mybit2, -1); -- 0 = current DBjá que a reconstrução provavelmente moveu o conteúdo para outra página
    6. exec: DBCC PAGEnovamente (com o novo PagePID!) e você verá que o "tamanho do registro" para ambos os registros agora é de 10 bytes

    Ainda mais divertido:

    1. executivo:ALTER TABLE dbo.mybit2 DROP COLUMN col5;

    2. executivo:

       DBCC TRACEON(3604) WITH NO_INFOMSGS;
 DBCC PAGE(0, 1, {PagePID}, 3) WITH TABLERESULTS; -- 0 = current DB
 DBCC TRACEOFF(3604) WITH NO_INFOMSGS;

      Ambos os registros ainda têm um "Tamanho do registro" de 10 bytes, mas o que era col5agora é exibido como DROPPED.

    3. agora executivo:ALTER TABLE dbo.mybit2 REBUILD;
    4. exec: DBCC IND(0, mybit2, -1); -- 0 = current DBjá que a reconstrução provavelmente moveu o conteúdo para outra página

    5. executivo:

       DBCC TRACEON(3604) WITH NO_INFOMSGS;
 DBCC PAGE(0, 1, {newPagePID}, 3) WITH TABLERESULTS; -- 0 = current DB
 DBCC TRACEOFF(3604) WITH NO_INFOMSGS;

      Ambos os registros agora têm um "Tamanho do registro" de 9 bytes e não há mais espaços reservados para col5isso, como DROPPEDna Fieldcoluna

    • 3

  3. Sua segunda tabela tem 8 colunas, a primeira tem 9. Como o tipo de dados é "Bit", isso explica exatamente a diferença.

    • 2

  4. Talvez eu esteja perdendo alguma coisa, mas você percebe que começa com 8 colunas, faz uma inserção, depois altera a tabela e adiciona uma 9ª coluna e depois faz outra inserção. Claro que o tamanho vai ser diferente quando a primeira inserção foi em 8 colunas e a segunda em 9 colunas.

    • 0

relate perguntas