Início / dba / Perguntas / 183275
Accepted
user129291
Asked: 2017-08-12 09:05:21 +0800 CST

varchar(255) ou varchar(256)?

  • 772

Devo usar varchar(255)ou varchar(256)ao projetar tabelas? Ouvi dizer que um byte é usado para o comprimento da coluna ou para armazenar metadados.

Isso importa mais neste momento?

Eu vi alguns posts na internet, porém eles se aplicam a Oracle e MySQL.

Temos o Microsoft SQL Server 2016 Enterprise Edition, como se aplica a este ambiente?

Agora diga, por exemplo, e se eu dissesse aos meus clientes para manter, por exemplo, uma descrição de texto com 255 caracteres em vez de 256, há alguma diferença? O que li "Com comprimento máximo de 255 caracteres, o SGBD pode optar por usar um único byte para indicar o comprimento dos dados no campo. Se o limite fosse 256 ou maior, seriam necessários dois bytes." Isso é verdade?

sql-server database-design

3 respostas

  1. Best Answer

    Dimensione cada coluna adequadamente. NÃO use um tamanho "padrão" para cada coluna. Se você precisa apenas de 30 caracteres, por que criar uma coluna que possa lidar com 255? Estou tão feliz que você não está defendendo o uso varchar(max)de suas colunas de string.

    Este é um conselho especialmente prudente se você precisar indexar uma coluna ou se estiver usando uma coluna como chave primária e ela tiver referências de chave estrangeira. O SQL Server usa o tamanho de cada coluna em seu otimizador de consulta para entender os requisitos estimados de memória para processamento de consulta. Ter colunas superdimensionadas pode prejudicar o desempenho.

    Índices em colunas muito grandes podem resultar na geração de erros:

    CREATE TABLE dbo.WideIndex
(
    col1 varchar(255) NOT NULL
    , col2 varchar(255) NOT NULL
    , col3 varchar(600) NOT NULL    
);

CREATE INDEX IX_WideIndex_01
ON dbo.WideIndex (col1, col2, col3);

    A tentativa de criar o índice acima resulta neste aviso:

    Aviso! O comprimento máximo da chave é de 900 bytes. O índice 'IX_WideIndex_01' tem comprimento máximo de 1110 bytes. Para alguma combinação de valores grandes, a operação de inserção/atualização falhará.

    900 bytes é o tamanho máximo da chave para índices clusterizados (e índices não clusterizados no SQL Server 2012 e anteriores). 1700 bytes é o tamanho máximo da chave para índices não clusterizados em versões mais recentes do SQL Server. Se você projetar colunas com uma largura genérica, como (255), poderá encontrar esse aviso com muito mais frequência do que o esperado.

    Caso esteja interessado em armazenamento interno, você pode usar o pequeno teste a seguir para entender melhor como o SQL Server armazena dados de armazenamento de linha não compactados.

    Primeiro, vamos criar uma tabela onde podemos armazenar colunas de vários tamanhos:

    IF OBJECT_ID(N'dbo.varchartest', N'U') IS NOT NULL
DROP TABLE dbo.varchartest;
GO

CREATE TABLE dbo.varchartest
(
    varchar30 varchar(30) NOT NULL
    , varchar255 varchar(255) NOT NULL
    , varchar256 varchar(256) NOT NULL
);

    Agora vamos inserir uma única linha:

    INSERT INTO dbo.varchartest (varchar30, varchar255, varchar256)
VALUES (REPLICATE('1', 30), REPLICATE('2', 255), REPLICATE('3', 256));

    Esta consulta usa as funções não documentadas e não suportadas sys.fn_RowDumpCrackere sys.fn_PhyslocCrackermostra alguns detalhes interessantes sobre a tabela:

    SELECT rdc.*
    , plc.*
FROM dbo.varchartest vct
CROSS APPLY  sys.fn_RowDumpCracker(%%rowdump%%) rdc
CROSS APPLY sys.fn_physlocCracker(%%physloc%%) plc

    A saída será semelhante a esta:

    ╔═════════════════════╦════════════╦═════════╦════ ══════╦══════════════════════════╦══════════╦═════ ════════╦═════════════╦═════════╦═════════╦═══════ ══╗
║ partition_id ║ colName ║ IsInrow ║ IsSparse ║ IsRecordPrefixCompressed ║ IsSymbol ║ PrefixBytes ║ InRowLength ║ file_id ║ page_id ║ slot_id ║
╠═════════════════════╬════════════╬═════════╬════ ══════╬══════════════════════════╬══════════╬═════ ════════╬═════════════╬═════════╬═════════╬═══════ ══╣
║ 1729382263096344576 ║ varchar30 ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 30 ║ 1 ║ 1912 ║ 0 ║
║ 1729382263096344576 ║ varchar255 ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 255 ║ 1 ║ 1912 ║ 0 ║
║ 1729382263096344576 ║ varchar256 ║ 1 ║ 0 ║ 0 ║ 0 ║ 0 ║ 256 ║ 1 ║ 1912 ║ 0 ║
╚═════════════════════╩════════════╩═════════╩════ ══════╩══════════════════════════╩══════════╩═════ ════════╩═════════════╩═════════╩═════════╩═══════ ══╝

    Como você pode ver, o InRowLengthpara cada valor é mostrado, juntamente com o local de armazenamento físico de cada linha - o "file_id", "page_id" e "slot_id".

    Se pegarmos os valores file_ide page_iddos resultados da consulta acima e executarmos DBCC PAGEcom eles, podemos ver o conteúdo real da página física:

    DBCC TRACEON (3604); --send display to the client
DBCC PAGE (tempdb, 1, 1912, 3); --database, file_id, page_id, 3 to show page contents
DBCC TRACEOFF (3604);--reset display back to the error log

    Os resultados da minha máquina são:

    PÁGINA: (1:1912)


AMORTECEDOR:


BUF @0x00000000FF5B2E80

bpage = 0x0000000024130000 bhash = 0x0000000000000000 bpageno = (1:1912)
bdbid = 2 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 32497 bstat = 0x10b
blog = 0x212121cc bnext = 0x0000000000000000          

CABEÇALHO DA PÁGINA:


Página @0x0000000024130000

m_pageId = (1:1912) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 98834 m_indexId (AllocUnitId.idInd) = 7936
Metadados: AllocUnitId = 2233785421652951040                              
Metadados: PartitionId = 1945555045333008384 Metadados: IndexId = 0
Metadados: ObjectId = 34099162 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 4 m_slotCnt = 1 m_freeCnt = 7538
m_freeData = 652 m_reservedCnt = 0 m_lsn = (35:210971:362)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 ID de fragmentação de banco de dados = 1                      

Status de alocação

GAM (1:2) = SGAM ALOCADO (1:3) = PFS NÃO ALOCADO (1:1) = 0x41 ALOCADO 50_PCT_FULL
DIFF (1:6) = NÃO ALTERADO ML (1:7) = NÃO MIN_LOGGED           

Slot 0 Offset 0x60 Comprimento 556

Tipo de registro = PRIMARY_RECORD Atributos de registro = NULL_BITMAP VARIABLE_COLUMNS
Tamanho do registro = 556                   
Despejo de memória @0x000000005145A060

0000000000000000: 30000400 03000003 002d002c 012c0231 31313131 0........-.,.,.11111
0000000000000014: 31313131 31313131 31313131 31313131 31313131 1111111111111111111
0000000000000028: 31313131 31323232 32323232 32323232 32323232 11111222222222222222
000000000000003C: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
0000000000000050: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
0000000000000064: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
0000000000000078: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
000000000000008C: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
00000000000000A0: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
00000000000000B4: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
00000000000000C8: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
00000000000000DC: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
00000000000000F0: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
0000000000000104: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
0000000000000118: 32323232 32323232 32323232 32323232 32323232 22222222222222222222
000000000000012C: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
0000000000000140: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
0000000000000154: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
0000000000000168: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
000000000000017C: 33333333 33333333 33333333 33333333 33333333 3333333333333333333
0000000000000190: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
00000000000001A4: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
00000000000001B8: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
00000000000001CC: 33333333 33333333 33333333 33333333 33333333 3333333333333333333
00000000000001E0: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
00000000000001F4: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
0000000000000208: 33333333 33333333 33333333 33333333 33333333 33333333333333333333
000000000000021C: 33333333 33333333 33333333 33333333 3333333333333333

Slot 0 Coluna 1 Offset 0xf Comprimento 30 Comprimento (físico) 30

varchar30 = 11111111111111111111111111111                               

Slot 0 Coluna 2 Offset 0x2d Comprimento 255 Comprimento (físico) 255

varchar255 = 2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
222222222222222222222222222222222222222222                               

Slot 0 Coluna 3 Offset 0x12c Comprimento 256 Comprimento (físico) 256

varchar256 = 33333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
333333333333333333333333333333333333333333
    • 42

  2. Outros já apontaram que o número de bytes necessários para armazenar o comprimento é fixo. Eu queria focar nesta parte em sua pergunta:

    Isso importa mais neste momento?

    Você tem sua pergunta marcada com edição corporativa, o que geralmente significa que você terá uma quantidade razoável de dados. Muitas vezes, as diferenças de um byte por linha realmente não importam muito na prática. Por exemplo, a tabela a seguir com uma VARCHAR(255)coluna totalmente preenchida ocupa 143176 KB de espaço em disco:

    DROP TABLE IF EXISTS dbo.V255_FULL;

CREATE TABLE dbo.V255_FULL (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    V255 VARCHAR(255)
);

INSERT INTO dbo.V255_FULL WITH (TABLOCK)
SELECT TOP (500000) 0, 0, REPLICATE('A', 255)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

EXEC sp_spaceused 'V255_FULL';

    Resultados:

    ╔═══════════╦══════════════════════╦═══════════╦═══════════╦════════════╦════════╗
║   name    ║         rows         ║ reserved  ║   data    ║ index_size ║ unused ║
╠═══════════╬══════════════════════╬═══════════╬═══════════╬════════════╬════════╣
║ V255_FULL ║ 500000               ║ 143176 KB ║ 142888 KB ║ 8 KB       ║ 280 KB ║
╚═══════════╩══════════════════════╩═══════════╩═══════════╩════════════╩════════╝

    Vamos criar uma segunda tabela com uma VARCHAR(256)coluna totalmente preenchida. Isso vai levar pelo menos mais um byte por linha, certo?

    DROP TABLE IF EXISTS dbo.V256_FULL;

CREATE TABLE dbo.V256_FULL (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    V256 VARCHAR(256)
);

INSERT INTO dbo.V256_FULL WITH (TABLOCK)
SELECT TOP (500000) 0, 0, REPLICATE('A', 256)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

EXEC sp_spaceused 'V256_FULL';

    Resultados:

    ╔═══════════╦══════════════════════╦═══════════╦═══════════╦════════════╦════════╗
║   name    ║         rows         ║ reserved  ║   data    ║ index_size ║ unused ║
╠═══════════╬══════════════════════╬═══════════╬═══════════╬════════════╬════════╣
║ V256_FULL ║ 500000               ║ 143176 KB ║ 142888 KB ║ 8 KB       ║ 280 KB ║
╚═══════════╩══════════════════════╩═══════════╩═══════════╩════════════╩════════╝

    Acontece que ambas as tabelas ocupam a mesma quantidade de espaço. O mesmo número de linhas cabe em cada página de 8k. É ótimo que você queira gastar tempo otimizando seu aplicativo, mas suspeito que é melhor se concentrar em diferentes áreas.

    • 24

  3. O tamanho declarado do varchar raramente tem impacto no desempenho 1 . Os dados podem ser realmente armazenados como um rowstore com compactação de página ou compactação de linha. Como um Columnstore em cluster ou como uma tabela com otimização de memória. Cada um deles terá diferentes compensações de desempenho, mas nunca importa se você declara um varchar(255) ou varchar(256).

    1 - em circunstâncias específicas, há implicações de desempenho em torno do tamanho das colunas que variam de caracteres; Brent Ozar tem um ótimo artigo sobre isso aqui

    • 8

relate perguntas