Início / dba / Perguntas / 135950
Accepted
Simon Jones
Asked: 2016-04-21 07:38:52 +0800 CST

Onde o SQL Server armazena fisicamente o VALOR DE IDENTIDADE de uma tabela?

  • 772

Eu estou esperando que alguém possa me apontar na direção certa sobre este. Aqui está o meu trabalho até agora.

SELECT * FROM sys.identity_columnsé uma visão do sistema que dá "last_value", mas a definição para essa visão usa uma função interna IdentityProperty(colName, 'LastValue')- então é um beco sem saída (não puxando de uma tabela do sistema lá).

Em todos os lugares (eu procurei) na internet sugere o uso DBCC IDENT_...de comandos para descobrir o valor, mas isso ainda me deixa no escuro sobre onde ele está realmente armazenado.

Então, cheguei a pesquisar as páginas individuais com DBCC PAGE(TestDB,1,1325,3)meu banco de dados de teste e usar o RESEEDcomando para propagar novamente entre os valores 10 e 12.

Ao fazer isso, notei os valores hexadecimais no IAM: Header, IAM: Single Page Allocationse IAM: Extent Alloc Status Slot 1tudo mudou. (E percebi que eles mudam periodicamente de qualquer maneira junto com o valor bUse1 , que também muda incrementalmente por si só).

Então, outro beco sem saída e estou sem ideias. Onde mais posso pesquisar?

Estou executando o SQL Server 2014. Tenho uma sede insaciável de conhecimento interno e ainda não encontrei nada tão indescritível quanto isso. Chamou minha atenção porque, em teoria, (um valor absoluto) está armazenado em algum lugar e deve (provavelmente) ser localizável. Em minha busca para descobrir locais de dados / metadados armazenados internamente, esse valor em particular me parece particularmente elusivo. Estou supondo / esperando que alguém venha e me diga, você pode conseguir, DBCC PAGEmas eu estava procurando no lugar errado.

sql-server sql-server-2014

1 respostas

  1. Best Answer

    Se você puder acessar o DAC ( Dedicated Administrator Console ), poderá inspecionar o valor da coluna de identidade, para INTcolunas, observando a idtvalcoluna em sys.syscolpars.

    Agradeço a Martin Smith por me direcionar a essa mesa por meio desta resposta muito útil de Roi Gavish sobre uma questão relacionada aqui.

    Tomemos, por exemplo, a seguinte tabela temporária:

    USE tempdb;

CREATE TABLE #d
(
    ID INT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #d;

DBCC CHECKIDENT ('#d',RESEED, 2147483635);

INSERT INTO #d DEFAULT VALUES;

    Vamos ver o que a tabela contém:

    SELECT *
FROM #d;

    +------------+
| ID         |
+------------+
| 2147483635 |
+------------+

    O valor da identidade pode ser inspecionado por este código:

    DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#d____%'

DECLARE @LittleEndian NVARCHAR(10);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 10);
SELECT @LittleEndian;
DECLARE @BigEndian NVARCHAR(10) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 4
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((4 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(INT, 
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

    +----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 2147483635           |
+----------------------+

    Para BIGINTcolunas de identidade, precisamos expandir o tamanho de algumas variáveis ​​usadas no código, como:

    CREATE TABLE #dBig
(
    ID BIGINT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #dBig;

DBCC CHECKIDENT ('#dBig',RESEED, 9223372036854775704);

INSERT INTO #dBig DEFAULT VALUES;

SELECT *
FROM #dBig;


DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#dBig____%'

DECLARE @LittleEndian NVARCHAR(18);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 18);
DECLARE @BigEndian NVARCHAR(18) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 8
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((8 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(BIGINT, 
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

    Resultados para BIGINT:

    +----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 9223372036854775704  |
+----------------------+
    • 8

relate perguntas