为何SQL Server中Varchar(100)比Char(100)占用更多存储空间？

Great question—this counterintuitive result happens because of how SQL Server stores fixed vs. variable-length data at the page level, not just the theoretical per-row size. Let’s break it down step by step:

1. Theoretical vs. Actual Storage Calculations #

First, let’s confirm the math to set the baseline:

• char(100): Each row takes exactly 100 bytes (no extra length overhead). For 100,000 rows, that’s 100 * 100000 = 10,000,000 bytes (~9.5MB) in raw data.
• varchar(100): Each row uses actual character length + 1 byte (since varchar(n) with n ≤255 uses 1 byte to store the length). Your data averages (1+100)/2 = 50.5 characters per row, so average row size is 50.5 +1 =51.5 bytes. Raw data would be 51.5 *100000=5,150,000 bytes (~4.9MB)—way smaller on paper.

So why does the actual storage end up larger for varchar?

2. Page Utilization & Fragmentation #

SQL Server stores data in 8KB pages (8192 bytes, minus ~96 bytes for page headers/metadata, leaving ~8096 bytes usable per page).

Fixed-length (char) rows #

Since every row is exactly 100 bytes, SQL Server can pack pages perfectly:

• 8096 //100 =80 rows per page (80*100=8000 bytes, leaving only 96 bytes unused).
• Total pages needed: 100000 /80=1250 pages → 1250*8KB=10,000KB (~9.76MB), almost exactly matching the theoretical value. No wasted space from fragmentation here.

Variable-length (varchar) rows #

With rows of varying lengths (1-100 characters), page packing becomes inefficient:

• Even though the average row size is 51.5 bytes, mixing short and long rows creates gaps. For example: if a page has a 101-byte row (100 chars +1 length byte) followed by several 2-byte rows (1 char +1 length byte), the remaining space might not fit another full row of any length.
• This leads to internal page fragmentation—unused space within pages. In practice, page utilization for variable-length data in this scenario often drops to 70-80% or lower.
• More pages mean more overhead (page headers, row offset arrays), which adds to the total storage footprint.

3. Additional Factors That Might Amplify This #

• Clustered Index Impact: If your table has a clustered index, variable-length index keys can cause more index page fragmentation compared to fixed-length keys, adding extra storage for the index itself.
• Page Allocation Behavior: SQL Server may reserve slightly more space for variable-length rows during insertion to accommodate potential future updates (even if you don’t plan to update rows), which can increase the total pages allocated.

How to Verify This #

You can check the actual page count and utilization using these commands:

-- Check table space usage
EXEC sp_spaceused 'YourTableName';

-- Check page-level details (requires VIEW SERVER STATE permission)
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.index_id,
    p.partition_number,
    p.page_count,
    p.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), OBJECT_ID('YourTableName'), NULL, NULL, 'DETAILED'
) p;

You’ll likely see that the varchar table has more pages and lower average page space usage than the char table.

内容的提问来源于stack exchange，提问作者Jeppe KS