Difference between CHAR and VARCHAR datatype in SQL Server

In SQL Server, both CHAR and VARCHAR are used to store character data, but they have key differences:

Feature	CHAR(n)	VARCHAR(n)
Definition	Fixed-length character data type.	Variable-length character data type.
Storage	Always takes up n bytes, even if the actual data is smaller.	Takes up only the space required for the data plus 2 bytes for storage overhead.
Performance	It is faster for fixed-size data because of consistent storage size.	More efficient for variable-length data but slightly slower for retrieval due to extra-length calculations.
Best Use Case	When all values are of a fixed length (e.g., country codes, gender, status codes).	When values vary significantly in length (e.g., names, descriptions).
Padding	Pads extra spaces to match the defined length (n).	Stores only the actual data without extra spaces.

In SQL Server, CHAR and VARCHAR are both used to store character string data, but they differ in how they handle storage and performance. Here are the key differences:

1. Storage and Length:

• CHAR:

  • Fixed-length character data type.

  • If you define a CHAR(10), it will always use 10 bytes of storage, regardless of the actual length of the string stored.

  • If the string is shorter than the defined length, it is padded with spaces to fill the remaining space.

• VARCHAR:

  • Variable-length character data type.

  • If you define a VARCHAR(10), it will only use as much storage as needed to store the actual string, up to a maximum of 10 characters.

  • No padding is added, so it saves space when storing strings shorter than the maximum length.

2. Performance:

• CHAR:

  • Can be faster for fixed-length strings because the database engine knows exactly how much space each entry will occupy.

  • However, it can waste space if the stored strings are shorter than the defined length.

• VARCHAR:

  • More space-efficient for variable-length strings, but may have a slight performance overhead due to the need to manage variable-length data.

  • Generally preferred when the length of the data varies significantly.

3. Usage Scenarios:

• CHAR:

  • Best suited for storing data that is consistently the same length, such as codes (e.g., ISO country codes, state abbreviations) or fixed-length identifiers.

• VARCHAR:

  • Ideal for storing data where the length can vary significantly, such as names, addresses, or descriptions.

4. Storage Size:

• CHAR(n): Always uses n bytes of storage.

• VARCHAR(n): Uses the actual length of the string plus 2 bytes (for length information).

Example:

-- CHAR example
CREATE TABLE ExampleChar (
    ID INT,
    Code CHAR(5)
);

INSERT INTO ExampleChar (ID, Code) VALUES (1, 'ABC');  -- 'ABC  ' (padded with spaces)

-- VARCHAR example
CREATE TABLE ExampleVarchar (
    ID INT,
    Description VARCHAR(100)
);

INSERT INTO ExampleVarchar (ID, Description) VALUES (1, 'This is a description');  -- No padding