Dados em linha
Tipos de valores de pequenos para médios e grandes (varchar(max), nvarchar(max), varbinary(max) e xml) e tipos de dados LOB (large object – objetos grandes) (text, ntext e image) podem ser armazenados em uma linha de dados. Este comportamento é controlado usando duas opções no procedimento armazenado sp_tableoption do sistema: a opção tipos de valores grandes fora de linha para tipos de valores grandes e a opção texto em linha para tipos de objetos grandes. O melhor uso dessas opções é em tabelas cujos valores de dados de qualquer um desses tipos de dados sejam normalmente lidos ou gravados em uma unidade e a maioria das instruções que faz referência à tabela referencia este tipo de dados. Dependendo do uso ou características de carga de trabalho, pode ser que o armazenamento de dados em linha não seja útil.
.gif "Observação importante") Importante |
|---|
A opção texto em linha será removida em uma futura versão do SQL Server. Evite usar essa opção em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que atualmente utilizam texto em linha. Recomendamos armazenar dados de valor grande usando os tipos de dados varchar(max), nvarchar(max) ou varbinary(max). Para controlar o comportamento desses tipos de dados em linha e fora de linha, use a opção tipos de valores grandes fora de linha. |
A menos que a opção texto em linha esteja configurada como ON ou para um limite específico em linha, as cadeias text, ntext ou image terão cadeias binárias ou de caracteres grandes (de até 2 gigabytes), mas serão armazenadas fora da linha de dados. A linha de dados contém apenas um ponteiro de texto de 16 bytes para o nó raiz de uma árvore criada a partir de ponteiros internos que mapeiam as páginas nas quais os fragmentos da cadeia estão armazenados. Para obter mais informações sobre o armazenamento das cadeias text, ntext ou image, consulte Usando dados de texto e de imagem.
Você pode definir uma opção texto em linha para tabelas que contenham colunas de tipos de dados LOB. Você também pode especificar um limite da opção texto em linha, de 24 a 7.000 bytes.
De forma similar, a menos que a opção tipos de valores grandes fora de linha estiver definida como ON, as colunas varchar(max), nvarchar(max), varbinary(max) e xml serão armazenadas, se possível, dentro da linha de dados. Se este for o caso, o Mecanismo de Banco de Dados do SQL Server tentará encaixar o valor específico se puder ou de outra forma empurrará o valor fora de linha. Se tipos de valores grandes fora de linha estiver definido como ON, os valores serão armazenados fora da linha e somente um ponteiro de texto de 16 bytes será armazenado no registro.
.gif "Observação") Observação |
|---|
O máximo de armazenamento em linha para tipos de dados de valores grandes está definido como 8.000 bytes quando tipos de valores fora de linha estiver como OFF. Ao contrário da opção texto em linha, não é possível especificar o limite em linha para colunas da tabela. |
Quando uma tabela é configurada para armazenar tipos de valores grandes ou tipos de dados de objetos grandes diretamente na linha de dados, os valores reais da coluna estarão em linha se uma das seguintes condições existir:
O comprimento da cadeia de caracteres é menor que o limite especificado nas colunas text, ntext, e image.
Há espaço disponível suficiente na linha de dados para manter a cadeia de caracteres.
Quando um valor de coluna de tipo de valor grande ou de tipo de dados de objeto grande é armazenado na linha de dados, o Mecanismo de Banco de Dados não tem acesso a uma página separada ou a um conjunto de páginas para ler ou gravar a cadeia binária ou de caracteres. Isso torna a leitura e a gravação de cadeias em linha tão rápida quanto o tamanho limitado de leitura ou gravação das cadeias varchar, nvarchar ou varbinary. De forma similar, quando os valores são armazenados fora de linha, o Mecanismo de Banco de Dados incorre em uma página adicional de leitura ou gravação.
Para tipos de dados de objetos grandes, se a cadeia for maior que o limite da opção texto em linha ou que o espaço disponível na linha, o conjunto de ponteiros, que de outra forma seria armazenado no nó da raiz da árvore de ponteiros, será armazenado na linha. Os ponteiros serão armazenados na linha se qualquer uma das condições seguintes existir:
A quantia de espaço necessária para armazenar os ponteiros é menor que o limite especificado da opção texto em linha.
Há espaço disponível suficiente na linha de dados para manter os ponteiros.
Quando os ponteiros são movidos do nó de raiz para a própria linha, o Mecanismo de Banco de Dados não tem que usar o nó de raiz, o que pode eliminar um acesso de página na leitura ou gravação da cadeia de caracteres, melhorando o desempenho.
Quando os nós de raiz são usados, são armazenados como um dos fragmentos de cadeia em uma página de LOB e podem conter até cinco ponteiros internos. O Mecanismo de Banco de Dados precisa de 72 bytes de espaço em linha para armazenar cinco ponteiros para uma cadeia de caracteres em linha. Se não houver espaço suficiente na linha para manter os ponteiros quando a opção texto em linha estiver como ON ou se a opção tipos de valores grandes fora de linha estiver como OFF, o Mecanismo de Banco de Dados pode precisar alocar uma página de 8 K para mantê-los. Se o comprimento dos dados do valor exceder 40.200 bytes, haverá necessidade de mais que cinco ponteiros em linha; nesse ponto, somente 24 bytes serão armazenados na linha principal e a página de dados adicionais será alocada no espaço de armazenamento de LOB.
Quando grandes cadeias de caracteres são armazenadas em linha, são armazenadas de forma similar a cadeias de comprimento variável. O Mecanismo de Banco de Dados classifica as colunas em ordem de tamanho decrescente e empurra os valores fora de linha até que as colunas remanescentes encaixem na página de dados (8 K).
Habilitando e desabilitando a opção tipos de valores grandes fora de linha
É possível habilitar a opção tipos de valores grandes fora de linha para uma tabela usando sp_tableoption do seguinte modo:
sp_tableoption N'MyTable', 'large value types out of row', 'ON'
Se você especificar OFF, o limite em linha para as colunas varchar(max), nvarchar(max), varbinary(max) e xml será definido como 8.000 bytes. Somente um ponteiro de raiz de 16 bytes é armazenado em linha e o valor é armazenado no espaço de armazenamento de LOB. Recomendamos configurar essa opção como ON para tabelas onde a maioria das instruções não faz referência a colunas de tipos de valores grandes. O armazenamento dessas colunas fora de linha indica que mais linhas podem ser encaixadas por página, reduzindo portanto o número de operações de E/S requeridas para examinar a tabela.
Com o valor dessa opção definido como OFF, muitas cadeias podem terminar armazenadas na linha em si, potencialmente reduzindo o número de linhas de dados que cabem em cada página. Se a maioria das instruções que faz referências à tabela não acessar as colunas varchar(max), nvarchar(max), varbinary(max) e xml, a diminuição de linhas em uma página pode aumentar as páginas que devem ser lidas para processar as consultas. A redução das linhas por página pode aumentar o número de páginas que deveriam ser examinadas se o otimizador não encontrar um índice utilizável.
Também é possível usar sp_tableoption para desabilitar a opção fora de linha:
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
Quando o valor da opção tipos de valores grandes fora de linha é alterado, os valores existentes de varchar(max), nvarchar(max), varbinary(max) e xml não são imediatamente convertidos. O armazenamento das cadeias de caracteres é alterado de acordo com as atualizações subseqüentes. Quaisquer valores novos inseridos em uma tabela são armazenados de acordo com a opção de tabela em efeito.
Para examinar o valor da opção tipos de valores grandes fora de linha para uma tabela específica, consulte a coluna large_value_types_out_of_row da exibição de catálogo sys.tables. Esta coluna será de 0 se a tabela não tiver tipos de valores fora de linha habilitado e será 1 se os tipos de valores grandes forem armazenados fora de linha.
Habilitando e desabilitando a opção texto em linha
É possível habilitar a opção texto em linha para uma tabela usando sp_tableoption do seguinte modo:
sp_tableoption N'MyTable', 'text in row', 'ON'
Como opção, você pode especificar um limite máximo entre 24 e 7.000 bytes de comprimento de uma cadeia text, ntext e image que pode ser armazenada em uma linha de dados:
sp_tableoption N'MyTable', 'text in row', '1000'
Se você especificar ON em vez de um limite específico, o limite será padronizado em 256 bytes. Este valor padrão fornece a maioria dos benefícios de desempenho que podem ser ganhos usando a opção texto em linha. Embora geralmente você não deva definir o valor como menor que 72, também não se deve definir o valor muito alto, o que se aplica especialmente às tabelas nas quais a maioria das instruções não faz referência às colunas text, ntext e image ou nas quais existem várias colunas text, ntext e image.
Se você definir um limite muito grande para texto em linha e muitas cadeias de caracteres forem armazenadas na linha em si, o número de linhas de dados que cabem em cada página pode ser significativamente reduzido. Se a maioria das instruções que faz referências à tabela não acessar as colunas text, ntext ou image, a diminuição de linhas em uma página pode aumentar as páginas que devem ser lidas para processar as consultas. A redução das linhas por página pode aumentar o tamanho de índices e páginas a serem examinadas se o otimizador não encontrar um índice utilizável. O valor padrão de 256 para o limite de texto em linha é grande o suficiente para assegurar que cadeias pequenas e ponteiros de texto raiz sejam armazenados nas linhas, mas não tão grande a ponto de diminuir as linhas por página o suficiente para afetar o desempenho.
A opção texto em linha é definida automaticamente como 256 para variáveis com o tipo de dados de tabelas e para tabelas retornadas por funções definidas pelo usuário que retornam uma tabela. Não é possível alterar essa configuração.
Você também pode usar o parâmetro sp_tableoption para desabilitar a opção especificando um valor de opção como OFF ou 0:
sp_tableoption N'MyTable', 'text in row', 'OFF'
Para examinar o valor da opção texto em linha para uma tabela específica, consulte a coluna text_in_row_limit da exibição de catálogo sys.tables. Esta coluna será de 0 se a tabela não tiver texto em linha habilitado e será um valor maior que 0 se limite de texto em linha estiver definido.
Efeitos do uso da opção texto em linha
A opção texto em linha causa os seguintes efeitos:
Após habilitar a opção texto em linha, é possível usar as instruções TEXTPTR, READTEXT, UPDATETEXT ou WRITETEXT para ler ou modificar partes de qualquer valor de text, ntext ou image armazenado na tabela. Nas instruções SELECT, é possível ler a cadeia de caracteres inteira de text, ntext ou image ou usar a função SUBSTRING para ler partes da cadeia. Todas as instruções INSERT ou UPDATE que fizerem referências à tabela deverão especificar as cadeias de caracteres completas e não poderão modificar somente uma parte de uma cadeia de text, ntext ou image.
Ao habilitar a opção texto em linha pela primeira vez, as cadeias existentes de text, ntext ou image não serão imediatamente convertidas em cadeias em linha. As cadeias serão convertidas em cadeias em linhas somente se forem atualizadas subseqüentemente. Qualquer cadeia de text, ntext ou image inserida após a opção texto em linha será habilitada se for inserida como uma cadeia em linha.
Desabilitar a opção texto em linha pode ser uma operação registrada de longa execução. A tabela é bloqueada e todas as cadeias em linha de text, ntext e image são convertidas em cadeias normais de text, ntext e image. A quantidade de tempo que o comando deve executar e a quantidade de dados modificados depende de quantas cadeias text, ntext e image devem ser convertidas de cadeias em linha para cadeias regulares.
A opção texto em linha não afeta a operação do Native Client OLE DB Provider do SQL Server ou do driver Native Client ODBC do SQL Server mais do que o necessário para agilizar o acesso aos dados de text, ntext e image.
As funções de texto e imagem de DB-Library, como dbreadtext e dbwritetext, não podem ser usadas em uma tabela depois da habilitação da opção texto em linha.