varchar (255) czy varchar (256)?

Czy powinienem używać varchar(255)lub varchar(256)przy projektowaniu stołów? Słyszałem, że jeden bajt jest używany dla długości kolumny lub do przechowywania metadanych.

Czy w tym momencie ma to już znaczenie?

Widziałem kilka postów w Internecie, jednak dotyczą one Oracle i MySQL.

Mamy Microsoft SQL Server 2016 Enterprise Edition, w jaki sposób ma on zastosowanie do tego środowiska?

Teraz powiedzmy na przykład: co jeśli kazałem moim klientom zachować na przykład opis tekstowy do 255 znaków zamiast 256, czy jest jakaś różnica? Co przeczytałem „Przy maksymalnej długości 255 znaków DBMS może wybrać pojedynczy bajt do wskazania długości danych w polu. Gdyby limit wynosił 256 lub więcej, potrzebne byłyby dwa bajty”. Czy to prawda?

Rozmiar każdej kolumny odpowiednio. NIE używaj „standardowego” rozmiaru dla każdej kolumny. Jeśli potrzebujesz tylko 30 znaków, po co tworzyć kolumnę, która może obsłużyć 255? Tak się cieszę, że nie zalecasz używania varchar(max)swoich kolumn ciągów.

Jest to szczególnie rozsądna rada, jeśli kiedykolwiek będziesz potrzebować zaindeksować kolumnę lub jeśli używasz kolumny jako klucza podstawowego i ma ona odniesienia do klucza obcego. SQL Server używa wielkości każdej kolumny w swoim optymalizatorze zapytań, aby zrozumieć szacunkowe wymagania pamięci dla przetwarzania zapytań. Posiadanie zbyt dużych kolumn może mieć negatywny wpływ na wydajność.

Indeksy w kolumnach, które są zbyt duże, mogą powodować generowanie błędów:

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);

Próba utworzenia powyższego indeksu powoduje wyświetlenie tego ostrzeżenia:

Ostrzeżenie! Maksymalna długość klucza wynosi 900 bajtów. Indeks „IX_WideIndex_01” ma maksymalną długość 1110 bajtów. W przypadku niektórych kombinacji dużych wartości operacja wstawiania / aktualizacji zakończy się niepowodzeniem.

900 bajtów to maksymalny rozmiar klucza dla indeksów klastrowych (i indeksów nieklastrowanych w SQL Server 2012 i starszych). 1700 bajtów to maksymalny rozmiar klucza dla indeksów nieklastrowanych w nowszych wersjach SQL Server. Jeśli projektujesz kolumny o ogólnej szerokości, takie jak (255), możesz spotkać się z tym ostrzeżeniem znacznie częściej niż oczekiwano.

Jeśli interesują Cię wewnętrzne elementy pamięci, możesz skorzystać z następującego drobnego testu, aby lepiej zrozumieć, w jaki sposób SQL Server przechowuje nieskompresowane dane z magazynu wierszy.

Najpierw utworzymy tabelę, w której możemy przechowywać kolumny o różnych rozmiarach:

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
);

Teraz wstawimy pojedynczy wiersz:

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

W tym zapytaniu wykorzystano nieudokumentowane i nieobsługiwane funkcje sys.fn_RowDumpCrackeroraz sys.fn_PhyslocCrackerpokazano kilka interesujących szczegółów dotyczących tabeli:

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

Dane wyjściowe będą wyglądać podobnie do tego:

╔═════════════════════╦════════════╦═════════╦════ ══════╦══════════════════════════╦══════════╦═════ ════════╦═════════════╦═════════╦═════════╦═══════ ══╗
Id identyfikator_partycji ║ nazwa_kolumny ║ IsInrow ║ IsSparse ║ IsRecordPrefixCompressed ║ IsSymbol ║ PrefixBytes ║ InRowLength ║ id_pliku ║ id_strony ║ identyfikator_grysu ║
╠═════════════════════╬════════════╬═════════╬════ ══════╬══════════════════════════╬══════════╬═════ ════════╬═════════════╬═════════╬═════════╬═══════ ══╣
29 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 ║
╚═════════════════════╩════════════╩═════════╩════ ══════╩══════════════════════════╩══════════╩═════ ════════╩═════════════╩═════════╩═════════╩═══════ ══╝

Jak widać, InRowLengthdla każdej wartości jest wyświetlana wraz z fizyczną lokalizacją każdego wiersza - „id_pliku”, „id_strony” i „id_boksu”.

Jeśli weźmiemy wartości file_idi page_idz powyższych wyników zapytania i uruchomimy DBCC PAGEje, możemy zobaczyć rzeczywistą fizyczną zawartość strony:

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

Wyniki z mojej maszyny to:

STRONA: (1: 1912)


BUFOR:


BUF @ 0x00000000FF5B2E80

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

NAGŁÓWEK:


Strona @ 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
Metadane: AllocUnitId = 2233785421652951040                              
Metadane: PartitionId = 1945555045333008384 Metadane: IndexId = 0
Metadane: 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 DB Frag ID = 1                      

Status przydziału

GAM (1: 2) = PRZYDZIELONY SGAM (1: 3) = NIE PRZYZNANY PFS (1: 1) = 0x41 PRZYZNANY 50_PCT_FULL
DIFF (1: 6) = NOT CHANGED ML (1: 7) = NOT MIN_LOGGED           

Pozycja 0 Przesunięcie 0x60 Długość 556

Typ rekordu = PRIMARY_RECORD Atrybuty rekordu = NULL_BITMAP VARIABLE_COLUMNS
Rozmiar rekordu = 556                   
Zrzut pamięci @ 0x000000005145A060

0000000000000000: 30000400 03000003 002d002c 012c0231 31313131 0 ........-.,.. 11111
0000000000000014: 31313131 31313131 31313131 31313131 31313131 11111111111111111111
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
00000000000000 F0: 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 33333333333333333333
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 33333333333333333333
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

Szczelina 0 Kolumna 1 Przesunięcie 0xf Długość 30 Długość (fizyczna) 30

varchar30 = 111111111111111111111111111111                               

Slot 0 Kolumna 2 Przesunięcie 0x2d Długość 255 Długość (fizyczna) 255

varchar255 = 2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
222222222222222222222222222222222222222222                               

Slot 0 Kolumna 3 Przesunięcie 0x12c Długość 256 Długość (fizyczna) 256

varchar256 = 3333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
33333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333333
3333333333333333333333333333333333333333333                              

Inni zauważyli już, że liczba bajtów wymagana do przechowywania długości jest stała. Chciałem skoncentrować się na tej części w twoim pytaniu:

Czy w tym momencie ma to już znaczenie?

Twoje pytanie zostało otagowane wersją Enterprise, co ogólnie oznacza, że ​​będziesz mieć sporo danych. Często różnice w jednym bajcie na wiersz naprawdę nie mają większego znaczenia w praktyce. Na przykład poniższa tabela z w pełni wypełnioną VARCHAR(255)kolumną zajmuje 143176 KB miejsca na dysku:

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';

Wyniki:

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

Utwórzmy drugą tabelę z w pełni wypełnioną VARCHAR(256)kolumną. To zajmie przynajmniej jeden bajt na wiersz, prawda?

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';

Wyniki:

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

Tak się składa, że ​​obie tabele zajmują tyle samo miejsca. Ta sama liczba wierszy mieści się na każdej stronie o wielkości 8 tys. To wspaniałe, że chcesz spędzić czas na optymalizacji aplikacji, ale podejrzewam, że lepiej skupić się na różnych obszarach.

Deklarowany rozmiar varchara nie ma wpływu na wydajność. Dane mogą być faktycznie przechowywane jako magazyn wierszy z kompresją strony lub kompresją wiersza. Jako klastrowany magazyn kolumn lub jako tabela zoptymalizowana pod kątem pamięci. Każdy z nich będzie miał różne kompromisy wydajnościowe, ale nigdy nie ma znaczenia, czy zadeklarujesz varchar (255) czy varchar (256).