Este eu peguei emprestado pra palestra a partir de um post que o Luti colocou no site da Sr Nimbus, mas como minha didática é melhor que a dele, vou colocar o exemplo com meus comentários aqui. ;-)
Colunas TEXT e VARCHAR(MAX) armazenam objetos grandes, ou LOB's (Large Objects). Sintaticamente são iguais: uma coluna TEXT ou VARCHAR(MAX) armazena um valor string de até 2GB por registro. No entanto há uma diferença que pode impactar severamente na quantidade de IO realizada por uma consulta em uma tabela que contenha colunas destes tipos, e também na performance do Data Cache, um dos principais mecanismos de otimização do SQL Server, que é a área de memória que serve de buffer para as páginas lidas de disco. Tudo isto por uma "pequena" diferença na forma como o armazenamento dos dados LOB é feita, dependendo do tipo usado para declarar a coluna.
O script a seguir demonstra isto. Copie ele para uma nova query no Management Studio (não esqueça de mudar para um banco de testes), vá executando os pedaços de código entre os comentários e veja os resultados. Qualquer dúvida posta uma pergunta!
/* =============================================================================================================================== Comparação entre modos de armazenamento de dados para os tipos de dados "large data type values" (varchar(max), nvarchar(max) e varbinary(max) e "large object values" (text, ntext, image) Por default os "large data types" armazenam dados "in row", o que pode ocasinar muito IO desnecessário e mau uso do data cache. Este comportamento pode ser modificado através da opção de tabela "large value types out of row" http://msdn.microsoft.com/en-us/library/ms189087(v=sql.105).aspx =============================================================================================================================== */ -- Tabela com campo LOB armazenado em coluna VARCHAR(MAX) DROP TABLE dbo.TabelaTEXT CREATE TABLE dbo.TabelaTEXT ( ID INT IDENTITY NOT NULL CONSTRAINT PK_TabelaTEXT PRIMARY KEY , Nome VARCHAR(100) NOT NULL DEFAULT ('Sr. Nimbus') , DataRegistro DATETIME2 NOT NULL DEFAULT(SYSDATETIME()) , Texto TEXT NOT NULL DEFAULT (REPLICATE('A', 2000)) ) GO -- Mesma estrutura, exceto campo LOB que é armazenado em coluna TEXT DROP TABLE dbo.TabelaVARMAX CREATE TABLE dbo.TabelaVARMAX ( ID INT IDENTITY NOT NULL CONSTRAINT PK_TabelaVARMAX PRIMARY KEY , Nome VARCHAR(100) NOT NULL DEFAULT ('Sr. Nimbus') , DataRegistro DATETIME2 NOT NULL DEFAULT(SYSDATETIME()) , Texto VARCHAR(MAX) NOT NULL DEFAULT (REPLICATE('A', 2000)) ) GO -- Popula as 2 tabelas INSERT INTO dbo.TabelaTEXT DEFAULT VALUES INSERT INTO dbo.TabelaVARMAX DEFAULT VALUES GO 10000 select count(*) from TabelaTEXT select count(*) from TabelaVARMAX -- Vamos verificar a quantidade de IO realizada pelas consultas abaixo SET STATISTICS IO ON SELECT ID, Nome FROM dbo.TabelaTEXT SELECT ID, Nome FROM dbo.TabelaVARMAX -- TabelaTEXT > TabelaVARMAX -- Em TabelaTEXT a área de dados contém campos LOB -- SELECT não requisitou estes campos! SET STATISTICS IO OFF -- Tamanho e localização dos dados nas tabelas SELECT OBJECT_NAME(object_id) AS ObjectName , AU.type , AU.type_desc , AU.container_id , AU.filegroup_id , AU.total_pages , AU.used_pages , AU.data_pages FROM SYS.system_internals_allocation_units AS AU INNER JOIN SYS.Partitions AS P ON AU.Container_id = P.Partition_id WHERE Object_ID IN (object_id('TabelaTEXT'), object_id('TabelaVARMAX')) ORDER BY object_id, type -- Mudando o comportamento de VARCHAR(MAX): opção "large value types out of row" EXEC sp_tableoption 'TabelaVARMAX', 'large value types out of row', 1 GO -- Para dados existentes, armazenamento só muda quando linha é modificada UPDATE dbo.TabelaVARMAX SET Texto = Texto -- Rebuild do índice para compactar tabela ALTER INDEX PK_TabelaVARMAX ON dbo.TabelaVARMAX REBUILD -- Vamos verificar a quantidade de IO realizada pelas consultas abaixo SET STATISTICS IO ON SELECT ID, Nome FROM dbo.TabelaTEXT SELECT ID, Nome FROM dbo.TabelaVARMAX -- TabelaTEXT == TabelaVARMAX -- Ambas agora usam armazenamento de LOB's out of row SET STATISTICS IO OFF |