varchar(255) or varchar(256)?

Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata.

Does it matter anymore at this point?

I saw some posts on the internet, however they apply to Oracle and MySQL.

We have Microsoft SQL Server 2016 Enterprise Edition, how does it apply to this environment?

Now say for example, what if I told my clients to keep for example, a text description to 255 characters instead of 256, is there any difference? What I read " With a maximum length of 255 characters, the DBMS can choose to use a single byte to indicate the length of the data in the field. If the limit were 256 or greater, two bytes would be needed." Is this true?

Size each and every column appropriately. Do NOT use a "standard" size for each column. If you only need 30 characters, why create a column that can handle 255? I'm so glad you're not advocating using varchar(max) for your string columns.

This is especially prudent advice if you ever need to index a column, or if you are using a column as a primary key and it has foreign key references. SQL Server uses the size of each column in it's query optimizer to understand estimated memory requirements for query processing. Having oversized columns can be detrimental to performance.

Indexes on columns that are oversize can result in errors being generated:

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

The attempt to create the index above results in this warning:


Warning! The maximum key length is 900 bytes. The index 'IX_WideIndex_01' has maximum length of 1110 bytes. For some combination of large values, the insert/update operation will fail.

900 bytes is the maximum key size for clustered indexes (and non-clustered indexes on SQL Server 2012 and older). 1700 bytes is the maximum key size for non-clustered indexes on newer versions of SQL Server. If you design columns with a generic width, such as (255), you may run into this warning far more often than expected.

In case you are interested in storage internals, you can use the following tiny test to better understand how SQL Server stores uncompressed row-store data.

First, we'll create a table where we can store columns of various sizes:

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

Now we'll insert a single row:

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

This query uses the undocumented, and unsupported, functions sys.fn_RowDumpCracker and sys.fn_PhyslocCracker to show some interesting details about the table:

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

Output will look similar to this:

╔═════════════════════╦════════════╦═════════╦══════════╦══════════════════════════╦══════════╦═════════════╦═════════════╦═════════╦═════════╦═════════╗
║ 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 ║
╚═════════════════════╩════════════╩═════════╩══════════╩══════════════════════════╩══════════╩═════════════╩═════════════╩═════════╩═════════╩═════════╝

As you can see, the InRowLength for each value is shown, along with the physical storage location of each row - the "file_id", "page_id", and "slot_id".

If we take the file_id and page_id values from the query results above and run DBCC PAGE with them, we can see the actual physical page contents:

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

The results from my machine are:

PAGE: (1:1912)

BUFFER:

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

PAGE HEADER:

Page @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
Metadata: AllocUnitId = 2233785421652951040
Metadata: PartitionId = 1945555045333008384 Metadata: IndexId = 0
Metadata: ObjectId = 34099162 m_prevPage = (0:0)

StackExchange Database Administrators Q#183275, answer score: 45