Devo usar uma coluna varchar (max) embutida ou armazená-la em uma tabela separada?

Eu quero criar uma tabela no MS SQL Server 2005 para registrar detalhes de determinadas operações do sistema. Como você pode ver no design da tabela abaixo, todas as colunas além de Details são não anuláveis.

 CREATE TABLE [Log] ( [LogID] [int] IDENTITY(1,1) NOT NULL, [ActionID] [int] NOT NULL, [SystemID] [int] NOT NULL, [UserID] [int] NOT NULL, [LoggedOn] [datetime] NOT NULL, [Details] [varchar](max) NULL ) 

Porque a coluna ” Details ” nem sempre contém dados. É mais eficiente armazenar essa coluna em uma tabela separada e fornecer um link para ela?

 CREATE TABLE [Log] ( [LogID] [int] IDENTITY(1,1) NOT NULL, [ActionID] [int] NOT NULL, [SystemID] [int] NOT NULL, [UserID] [int] NOT NULL, [LoggedOn] [datetime] NOT NULL, [DetailID] [int] NULL ) CREATE TABLE [Detail] ( [DetailID] [int] IDENTITY(1,1) NOT NULL, [Details] [varchar](max) NOT NULL ) 

Para um tipo de dados menor, eu realmente não consideraria isso, mas para um varchar(max) , isso ajuda a manter o tamanho da tabela menor? Ou estou apenas tentando descobrir o database e não conseguir nada?

Mantenha-o em linha. Nos bastidores, o SQL Server já armazena as colunas MAX em uma ‘unidade de alocação’ separada desde o SQL 2005. Consulte Tabela e Organização do Índice . Isso, na verdade, é exatamente o mesmo que manter a coluna MAX em sua própria tabela, mas sem qualquer desvantagem de fazê-lo explicitamente.

Ter uma tabela explícita seria, na verdade, mais lenta (devido à restrição de chave estrangeira) e consumir mais espaço (devido à duplicação DetaiID). Sem mencionar que requer mais código, e erros são introduzidos por … código escrito.

texto alternativo http://i.msdn.microsoft.com/ms189051.3be61595-d405-4b30-9794-755842d7db7e(en-us,SQL.100).gif

Atualizar

Para verificar a localização real dos dados, um teste simples pode mostrá-lo:

 use tempdb; go create table a ( id int identity(1,1) not null primary key, v_a varchar(8000), nv_a nvarchar(4000), m_a varchar(max), nm_a nvarchar(max), t text, nt ntext); go insert into a (v_a, nv_a, m_a, nm_a, t, nt) values ('v_a', N'nv_a', 'm_a', N'nm_a', 't', N'nt'); go select %%physloc%%,* from a go 

A %%physloc%% mostrará a localização física real da linha, no meu caso foi a página 200:

 dbcc traceon(3604) dbcc page(2,1, 200, 3) Slot 0 Column 2 Offset 0x19 Length 3 Length (physical) 3 v_a = v_a Slot 0 Column 3 Offset 0x1c Length 8 Length (physical) 8 nv_a = nv_a m_a = [BLOB Inline Data] Slot 0 Column 4 Offset 0x24 Length 3 Length (physical) 3 m_a = 0x6d5f61 nm_a = [BLOB Inline Data] Slot 0 Column 5 Offset 0x27 Length 8 Length (physical) 8 nm_a = 0x6e006d005f006100 t = [Textpointer] Slot 0 Column 6 Offset 0x2f Length 16 Length (physical) 16 TextTimeStamp = 131137536 RowId = (1:182:0) nt = [Textpointer] Slot 0 Column 7 Offset 0x3f Length 16 Length (physical) 16 TextTimeStamp = 131203072 RowId = (1:182:1) 

Todos os valores da coluna, mas o TEXTO e o NTEXT foram armazenados em linha, incluindo os tipos MAX.
Depois de alterar as opções da tabela e inserir uma nova linha (sp_tableoption não afeta as linhas existentes), os tipos MAX foram despejados em seu próprio armazenamento:

 sp_tableoption 'a' , 'large value types out of row', '1'; insert into a (v_a, nv_a, m_a, nm_a, t, nt) values ('2v_a', N'2nv_a', '2m_a', N'2nm_a', '2t', N'2nt'); dbcc page(2,1, 200, 3); 

Observe como as colunas m_a e nm_a são agora um Textpointer na unidade de alocação de LOB:

 Slot 1 Column 2 Offset 0x19 Length 4 Length (physical) 4 v_a = 2v_a Slot 1 Column 3 Offset 0x1d Length 10 Length (physical) 10 nv_a = 2nv_a m_a = [Textpointer] Slot 1 Column 4 Offset 0x27 Length 16 Length (physical) 16 TextTimeStamp = 131268608 RowId = (1:182:2) nm_a = [Textpointer] Slot 1 Column 5 Offset 0x37 Length 16 Length (physical) 16 TextTimeStamp = 131334144 RowId = (1:182:3) t = [Textpointer] Slot 1 Column 6 Offset 0x47 Length 16 Length (physical) 16 TextTimeStamp = 131399680 RowId = (1:182:4) nt = [Textpointer] Slot 1 Column 7 Offset 0x57 Length 16 Length (physical) 16 TextTimeStamp = 131465216 RowId = (1:182:5) 

Para fins de conclusão, também podemos forçar a exclusão de um dos campos não máximos:

 update a set v_a = replicate('X', 8000); dbcc page(2,1, 200, 3); 

Observe como a coluna v_a é armazenada no armazenamento Row-Overflow:

 Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4 v_a = [BLOB Inline Root] Slot 0 Column 2 Offset 0x19 Length 24 Length (physical) 24 Level = 0 Unused = 99 UpdateSeq = 1 TimeStamp = 1098383360 Link 0 Size = 8000 RowId = (1:176:0) 

Assim, como outros já comentaram, os tipos MAX são armazenados in-line por padrão, se forem adequados. Para muitos projetos DW, isso seria inaceitável, pois os carregamentos típicos de DW devem varrer ou, pelo menos, varrer a varredura, portanto, sp_tableoption ..., 'large value types out of row', '1' devem ser usados. Observe que isso não afeta as linhas existentes, no meu teste nem na reconstrução do índice , portanto, a opção deve ser ativada antecipadamente.

Para a maioria das cargas do tipo OLTP, o fato de os tipos MAX serem armazenados em linha, se possível, é uma vantagem, já que o padrão de access OLTP deve ser procurado e a largura da linha causa pouco impacto.

No entanto, quanto à questão original: tabela separada não é necessária. Ativar a opção de large value types out of row obtém o mesmo resultado a um custo livre para desenvolvimento / teste.

Paradoxalmente, se seus dados normalmente são menores que 8000 caracteres, eu os armazenaria em uma tabela separada, enquanto se os dados fossem maiores que 8000 caracteres, eu os manteria na mesma tabela.

Isso ocorre porque o SQL Server mantém os dados na página se ela permite que a linha fique em uma única página, mas quando os dados ficam maiores, eles são movidos como o tipo de dados TEXT e deixam apenas um ponteiro na página. linha. Então, para um monte de linhas de 3000 caracteres, você está ajustando menos linhas por página, o que é realmente ineficiente, mas para um monte de 12.000 linhas de caracteres, os dados estão fora da linha, por isso é realmente mais eficiente.

Dito isto, normalmente você tem uma ampla gama de comprimentos e, portanto, eu iria movê-lo para sua própria mesa. Isso lhe dá flexibilidade para mover esta tabela para um grupo de arquivos diferente, etc.

Observe que você também pode especificá-lo para forçar os dados para fora da linha usando sp_tableoption . O varchar (max) é basicamente semelhante ao tipo de dados TEXT, com o padrão de dados na linha (para varchar (max)), em vez de padronizar para dados fora da linha (para TEXT).

Você deve estruturar seus dados em qualquer que seja a estrutura mais lógica e permitir que o SQL Server realize suas otimizações de como armazenar fisicamente os dados.

Se você descobrir, por meio da análise de desempenho, que sua estrutura é um problema de desempenho, considere a possibilidade de realizar alterações em sua estrutura ou em configurações de armazenamento.

Mantenha-o em linha. O ponto inteiro do varchar é que ele ocupa 0 bytes se estiver vazio, 4 bytes para ‘Hello’ e assim por diante.

Eu iria normalizá-lo criando a tabela Detail. Eu suponho que algumas das inputs no Log terão o mesmo detalhe? Então, se você normalizar, você estará armazenando apenas um ID FK INTEGER em vez do texto para cada ocorrência, se você armazenou o texto na tabela Detalhe. Se você tem razões para desnormalizar, faça isso, mas pela sua pergunta eu não vejo esse sendo o caso.

Ter uma coluna anulável custa 2 bytes para cada 16 deles. Se esta for a única (ou 17ª, ou 33ª, etc) coluna anulável na tabela, custará 2 bytes por linha, caso contrário, nada.

    Intereting Posts