Início / dba / Perguntas / 162113
Accepted
Tintin
Asked: 2017-01-25 10:55:30 +0800 CST

Usar varchar(5000) seria ruim comparado a varchar(255)?

  • 772

Como varcharde qualquer maneira aloca espaço dinamicamente, minha pergunta é se o uso varchar(255)é mais eficiente ou economiza mais espaço em comparação ao uso do varchar(5000). Se sim, por quê?

sql-server

1 respostas

  1. Best Answer

    Sim, varchar(5000)pode ser pior do que varchar(255)se todos os valores couberem neste último. O motivo é que o SQL Server estimará o tamanho dos dados e, por sua vez, as concessões de memória com base no tamanho declarado (não real ) das colunas em uma tabela. Quando você tiver varchar(5000), ele assumirá que cada valor tem 2.500 caracteres e reservará memória com base nisso.

    Aqui está uma demonstração da minha recente apresentação GroupBy sobre maus hábitos que facilita a comprovação por si mesmo (requer SQL Server 2016 para algumas das sys.dm_exec_query_statscolunas de saída, mas ainda deve ser comprovado com SET STATISTICS TIME ONoutras ferramentas em versões anteriores); ele mostra memória maior e tempos de execução mais longos para a mesma consulta nos mesmos dados - a única diferença é o tamanho declarado das colunas:

    -- create three tables with different column sizes
CREATE TABLE dbo.t1(a nvarchar(32),   b nvarchar(32),   c nvarchar(32),   d nvarchar(32));
CREATE TABLE dbo.t2(a nvarchar(4000), b nvarchar(4000), c nvarchar(4000), d nvarchar(4000));
CREATE TABLE dbo.t3(a nvarchar(max),  b nvarchar(max),  c nvarchar(max),  d nvarchar(max));
GO -- that's important

-- Method of sample data pop : irrelevant and unimportant.
INSERT dbo.t1(a,b,c,d)
  SELECT TOP (5000) LEFT(name,1), RIGHT(name,1), ABS(column_id/10), ABS(column_id%10)
  FROM sys.all_columns ORDER BY object_id;
GO 100
INSERT dbo.t2(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
INSERT dbo.t3(a,b,c,d) SELECT a,b,c,d FROM dbo.t1;
GO

-- no "primed the cache in advance" tricks
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
GO

-- Redundancy in query doesn't matter! Just has to create need for sorts etc.
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t1 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t2 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO
SELECT DISTINCT a,b,c,d, DENSE_RANK() OVER (PARTITION BY b,c ORDER BY d DESC)
FROM dbo.t3 GROUP BY a,b,c,d ORDER BY c,a DESC;
GO

SELECT [table] = N'...' + SUBSTRING(t.[text], CHARINDEX(N'FROM ', t.[text]), 12) + N'...', 
s.last_dop, s.last_elapsed_time, s.last_grant_kb, s.max_ideal_grant_kb
FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.[text] LIKE N'%dbo.'+N't[1-3]%' ORDER BY t.[text];

    Então, sim, dimensione corretamente suas colunas , por favor.

    Além disso, executei novamente os testes com varchar(32), varchar(255), varchar(5000), varchar(8000) e varchar(max). Resultados semelhantes ( clique para ampliar ), embora as diferenças entre 32 e 255, e entre 5.000 e 8.000, foram insignificantes:

    insira a descrição da imagem aqui

    Aqui está outro teste com a TOP (5000)mudança para o teste mais totalmente reproduzível sobre o qual eu estava sendo incessantemente atormentado ( clique para ampliar ):

    insira a descrição da imagem aqui

    Portanto, mesmo com 5.000 linhas em vez de 10.000 linhas (e há mais de 5.000 linhas em sys.all_columns pelo menos desde o SQL Server 2008 R2), uma progressão relativamente linear é observada - mesmo com os mesmos dados, maior o tamanho definido da coluna, mais memória e tempo são necessários para satisfazer exatamente a mesma consulta (mesmo que ela tenha um DISTINCT).

    • 67

relate perguntas