SQL Server CHAR vs VARCHAR: What’s the Difference?

 

Introduction to String Data Types

In SQL Server, CHAR and VARCHAR are both character data types used to store string data, but they handle storage and padding differently. Choosing the right type affects storage efficiency, performance, and data integrity.

1. Fundamental Differences

CHAR (Fixed-Length)

• Always uses the same amount of storage space regardless of content

• Pads values with spaces to fill the defined length

• Best for data with consistent, predictable lengths

VARCHAR (Variable-Length)

• Uses only the storage space needed for the actual data plus overhead

• Does not pad values with spaces (except in some specific cases)

• Best for data with variable lengths

2. Storage Comparison

Example 1: Basic Storage Differences

sql

-- Create test tables
CREATE TABLE CharTable (
    ID INT IDENTITY PRIMARY KEY,
    FixedName CHAR(20)
);

CREATE TABLE VarCharTable (
    ID INT IDENTITY PRIMARY KEY,
    VariableName VARCHAR(20)
);

-- Insert the same data
INSERT INTO CharTable (FixedName) VALUES ('John');
INSERT INTO VarCharTable (VariableName) VALUES ('John');

-- Check storage usage
EXEC sp_spaceused 'CharTable';
EXEC sp_spaceused 'VarCharTable';

Storage Results:

• CHAR(20): Always uses 20 bytes + overhead (2 bytes for length)

• VARCHAR(20): Uses 4 bytes (for 'John') + overhead (2 bytes for length)

Example 2: Visualizing the Padding

sql

-- Insert data with trailing spaces
INSERT INTO CharTable (FixedName) VALUES ('John   '); -- With spaces
INSERT INTO VarCharTable (VariableName) VALUES ('John   ');

-- Retrieve data to see how it's stored
SELECT 
    '''' + FixedName + '''' AS CharValue,
    LEN(FixedName) AS CharLength,
    DATALENGTH(FixedName) AS CharDataLength
FROM CharTable;

SELECT 
    '''' + VariableName + '''' AS VarCharValue,
    LEN(VariableName) AS VarCharLength,
    DATALENGTH(VariableName) AS VarCharDataLength
FROM VarCharTable;

Results:

Type	Value	LEN()	DATALENGTH()
CHAR	'John '	4	20
VARCHAR	'John'	4	4

3. Performance Implications

Read Performance

sql

-- Create large sample tables
CREATE TABLE LargeCharTable (
    ID INT IDENTITY PRIMARY KEY,
    Code CHAR(10),
    Description VARCHAR(100)
);

CREATE TABLE LargeVarCharTable (
    ID INT IDENTITY PRIMARY KEY,
    Code VARCHAR(10),
    Description VARCHAR(100)
);

-- Insert 100,000 rows
DECLARE @i INT = 0;
WHILE @i < 100000
BEGIN
    INSERT INTO LargeCharTable (Code, Description) 
    VALUES ('ABC', 'Sample description ' + CAST(@i AS VARCHAR));
    
    INSERT INTO LargeVarCharTable (Code, Description) 
    VALUES ('ABC', 'Sample description ' + CAST(@i AS VARCHAR));
    
    SET @i = @i + 1;
END

-- Compare read performance
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

-- Query CHAR table
SELECT * FROM LargeCharTable WHERE Code = 'ABC';

-- Query VARCHAR table
SELECT * FROM LargeVarCharTable WHERE Code = 'ABC';

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Performance Observations:

• CHAR may have slightly better read performance for exact-width data

• VARCHAR uses less memory and buffer pool space

• Less storage means more rows per page, reducing I/O

Write Performance

sql

-- Test insert performance
DECLARE @StartTime DATETIME, @EndTime DATETIME;

-- CHAR inserts
SET @StartTime = GETDATE();
INSERT INTO LargeCharTable (Code, Description)
SELECT 'TEST', 'Description'
FROM master.dbo.spt_values WHERE number < 1000;
SET @EndTime = GETDATE();
SELECT DATEDIFF(MS, @StartTime, @EndTime) AS CharInsertTime;

-- VARCHAR inserts
SET @StartTime = GETDATE();
INSERT INTO LargeVarCharTable (Code, Description)
SELECT 'TEST', 'Description'
FROM master.dbo.spt_values WHERE number < 1000;
SET @EndTime = GETDATE();
SELECT DATEDIFF(MS, @StartTime, @EndTime) AS VarCharInsertTime;

4. Real-World Use Cases

When to Use CHAR

sql

-- 1. Fixed-length codes and identifiers
CREATE TABLE CountryCodes (
    CountryCode CHAR(2) PRIMARY KEY, -- ISO country codes (US, CA, GB)
    CountryName VARCHAR(50)
);

-- 2. Status flags with fixed length
CREATE TABLE Orders (
    OrderID INT IDENTITY PRIMARY KEY,
    OrderStatus CHAR(1), -- 'P'=Pending, 'C'=Complete, 'X'=Cancelled
    OrderDate DATETIME
);

-- 3. Formatted data with fixed patterns
CREATE TABLE PhoneNumbers (
    PhoneID INT IDENTITY PRIMARY KEY,
    FormattedPhone CHAR(12) -- (555)123-4567
);

-- 4. Legacy systems with fixed-width requirements
CREATE TABLE LegacyData (
    RecordType CHAR(1),
    AccountNumber CHAR(10),
    TransactionCode CHAR(5)
);

When to Use VARCHAR

sql

-- 1. Variable-length text data
CREATE TABLE Products (
    ProductID INT IDENTITY PRIMARY KEY,
    ProductName VARCHAR(100), -- Variable length names
    Description VARCHAR(500)  -- Variable length descriptions
);

-- 2. User-generated content
CREATE TABLE UserComments (
    CommentID INT IDENTITY PRIMARY KEY,
    CommentText VARCHAR(1000), -- Highly variable length
    UserID INT
);

-- 3. Address information
CREATE TABLE Addresses (
    AddressID INT IDENTITY PRIMARY KEY,
    StreetAddress VARCHAR(100), -- Highly variable
    City VARCHAR(50),
    State CHAR(2), -- Fixed length state codes
    ZipCode VARCHAR(10) -- Could be 5 or 9 digits
);

-- 4. JSON or XML data storage
CREATE TABLE AppSettings (
    SettingID INT IDENTITY PRIMARY KEY,
    SettingName VARCHAR(50),
    SettingValue VARCHAR(MAX) -- Variable length configuration
);

5. Advanced Considerations

VARCHAR(MAX) vs TEXT

sql

-- VARCHAR(MAX) is preferred over TEXT in modern SQL Server
CREATE TABLE DocumentStore (
    DocID INT IDENTITY PRIMARY KEY,
    -- Old way (deprecated)
    -- DocContent TEXT,
    
    -- Modern way
    DocContent VARCHAR(MAX),
    Metadata VARCHAR(200)
);

-- VARCHAR(MAX) can be used with string functions
UPDATE DocumentStore 
SET DocContent = REPLACE(DocContent, 'old', 'new')
WHERE DocID = 1;

Collation and Sorting Differences

sql

-- CHAR and VARCHAR behave the same regarding collation
CREATE TABLE CollationTest (
    CharColumn CHAR(10) COLLATE Latin1_General_CI_AS,
    VarCharColumn VARCHAR(10) COLLATE Latin1_General_CI_AS
);

-- Sorting behavior is identical
INSERT INTO CollationTest VALUES ('Apple', 'Apple'), ('apple', 'apple');
SELECT * FROM CollationTest ORDER BY CharColumn;
SELECT * FROM CollationTest ORDER BY VarCharColumn;

Storage Overhead Analysis

sql

-- Analyze actual storage usage
SELECT 
    name AS ColumnName,
    type_name(system_type_id) AS DataType,
    max_length AS MaxLength,
    CASE 
        WHEN type_name(system_type_id) = 'varchar' THEN '2 bytes + actual data'
        WHEN type_name(system_type_id) = 'char' THEN 'fixed length + 2 bytes'
        ELSE 'varies'
    END AS StorageInfo
FROM sys.columns 
WHERE object_id = OBJECT_ID('CharTable')
   OR object_id = OBJECT_ID('VarCharTable');

6. Best Practices and Recommendations

1. Use CHAR for fixed-length data:

   • Codes, flags, and identifiers with predictable lengths

   • When storage consistency is more important than space savings

2. Use VARCHAR for variable-length data:

   • Most string data in modern applications

   • When storage efficiency is important

   • For user-generated content

3. Avoid over-sizing:

   sql

   -- Bad: Over-sized CHAR
   CREATE TABLE BadDesign (
       Status CHAR(100) -- Only needs 1 character
   );
   
   -- Good: Properly sized
   CREATE TABLE GoodDesign (
       Status CHAR(1) -- Appropriate size
   );

4. Consider future growth:

   sql

   -- Allow room for growth but don't exaggerate
   CREATE TABLE ProductCatalog (
       -- Reasonable sizing
       SKU VARCHAR(20), -- Enough for complex SKUs
       ProductName VARCHAR(100), -- Most product names fit
       Description VARCHAR(500) -- Detailed descriptions
   );

5. Use appropriate indexing:

   sql

   -- Indexes work equally well on both types
   CREATE INDEX IX_Product_SKU ON ProductCatalog(SKU);
   CREATE INDEX IX_Product_Name ON ProductCatalog(ProductName);

7. Common Pitfalls and Solutions

Pitfall 1: Implicit Padding in Comparisons

sql

-- CHAR vs VARCHAR comparisons
DECLARE @CharValue CHAR(5) = 'Test', @VarCharValue VARCHAR(5) = 'Test';

-- These behave differently due to padding
SELECT CASE WHEN @CharValue = 'Test' THEN 'Match' ELSE 'No Match' END; -- Match
SELECT CASE WHEN @CharValue = 'Test ' THEN 'Match' ELSE 'No Match' END; -- Match

SELECT CASE WHEN @VarCharValue = 'Test' THEN 'Match' ELSE 'No Match' END; -- Match
SELECT CASE WHEN @VarCharValue = 'Test ' THEN 'Match' ELSE 'No Match' END; -- No Match

-- Solution: Use RTRIM or consistent data types
SELECT CASE WHEN RTRIM(@CharValue) = 'Test' THEN 'Match' ELSE 'No Match' END;

Pitfall 2: Storage Waste with CHAR

sql

-- Wasted space example
CREATE TABLE InefficientTable (
    UserID INT,
    StatusFlag CHAR(50) -- Only needs 1 character, wastes 49 bytes per row
);

-- Efficient alternative
CREATE TABLE EfficientTable (
    UserID INT,
    StatusFlag CHAR(1) -- Properly sized
);

Conclusion

Choose CHAR when:

• Data length is consistently fixed

• You need predictable storage patterns

• Working with legacy systems or fixed-width formats

• Storage efficiency is less concerned than read performance

Choose VARCHAR when:

• Data length varies significantly

• Storage efficiency is important

• Dealing with user-generated content

• Most modern application scenarios

General recommendations:

1. Default to VARCHAR for most string data

2. Use CHAR only for truly fixed-length data

3. Size columns appropriately for current and future needs

4. Consider storage implications for large tables

5. Test performance with realistic data volumes

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone