Advantage of using char over varchar data type

Being asked about the advantage of using char over varchar data type very often, I have gone through a lot of documents regarding its use, and everywhere it is preferred to use varchar over char.

As we know char is fixed length, it always takes full space as defined in database. In case you declare a column with char(5) which then stores a single character, it will always occupy 5 bytes storage space; but in case of varchar storage space will be 1 byte (approximately).

Nevertheless, char data type can be beneficial in some situation. Few days back I had done a Proof of Concept on this which I am going to describe below. See how char datatype is useful in following scenario:

Two tables are created with the following structure:

Create table TestChar    (Id Int Identity Key(1,1) Primary  key,  Name     char(100))
                      Create table TestVarchar (Id Int Identity Key(1,1) Primary  key,  Name  varchar(100))

Select all Open in new window

Let's assume we have inserted 1,00,000 rows into both tables, populating the Name column with NULL.
So, after inserting rows into the above tables I have executed   dbcc Showcontig   statement.

dbcc showcontig(TestChar)
                      
                      DBCC SHOWCONTIG scanning 'TestChar' table...
                      Table: 'TestChar' (165575628); index ID: 1, database ID: 10
                      TABLE level scan performed.
                      - Pages Scanned................................: 1409
                      - Extents Scanned..............................: 178
                      - Extent Switches..............................: 177
                      - Avg. Pages per Extent........................: 7.9
                      - Scan Density [Best Count:Actual Count].......: 99.44% [177:178]
                      - Logical Scan Fragmentation ..................: 0.00%
                      - Extent Scan Fragmentation ...................: 0.00%
                      - Avg. Bytes Free per Page.....................: 76.2
                      - Avg. Page Density (full).....................: 99.06%
                      DBCC execution completed. If DBCC printed error messages, contact your
                      system administrator.
                      
                      dbcc showcontig(TestVarchar)
                      
                      DBCC SHOWCONTIG scanning 'TestVarchar' table...
                      Table: 'TestVarchar' (197575742); index ID: 1, database ID: 10
                      TABLE level scan performed.
                      - Pages Scanned................................: 161
                      - Extents Scanned..............................: 21
                      - Extent Switches..............................: 20
                      - Avg. Pages per Extent........................: 7.7
                      - Scan Density [Best Count:Actual Count].......: 100.00% [21:21]
                      - Logical Scan Fragmentation ..................: 0.00%
                      - Extent Scan Fragmentation ...................: 0.00%
                      - Avg. Bytes Free per Page.....................: 21.5
                      - Avg. Page Density (full).....................: 99.73%
                      DBCC execution completed. If DBCC printed error messages, contact your
                      system administrator.

Select all Open in new window

Then I have updated the two tables with the following statement:

Update Testchar
                          set name ='this is an interesting note that I came across recently.Most of our database design'
                      
                      Update Testvarchar
                          set name ='this is an interesting note that I came across recently.Most of our database design'

Select all Open in new window

Executing   dbcc Showcontig   again, the output for table Testchar remains the same as shown above, but the output varies greatly for table Testvarchar:

dbcc showcontig(TestVarchar)
                      
                      DBCC SHOWCONTIG scanning 'testVarchar' table...
                      Table: 'testVarchar' (197575742); index ID: 1, database ID: 10
                      TABLE level scan performed.
                      - Pages Scanned................................: 1596
                      - Extents Scanned..............................: 201
                      - Extent Switches..............................: 484
                      - Avg. Pages per Extent........................: 7.9
                      - Scan Density [Best Count:Actual Count].......: 41.24% [200:485]
                      - Logical Scan Fragmentation ..................: 11.03%
                      - Extent Scan Fragmentation ...................: 0.00%
                      - Avg. Bytes Free per Page.....................: 1767.7
                      - Avg. Page Density (full).....................: 78.16%

Select all Open in new window

The internal storage structure of Testchar did not change after update, because at the time of inserting data it stored 100 spaces for the Name column. The number of pages was fixed with inserting. When we update this table no extra space is required to store values as it was already allocated.

But the scenario is different  for TestVarchar . As Name column is varchar in this table, at the time of inserting data in this table it took only 161 pages, as we only put NULL value for Name column.

But when we updated this table with a value of 84 characters length, lots of page splits occurred to accommodate the new values. That is why this table is fragmented a lot after executing the Update statement. The output of   dbcc Showcontig   for this table changed dramatically.

                                              Before Update      After Update
Pages Scanned                      161                  1596
Scan Density                           100%              41.24%
Avg. Bytes Free per Page      21.5               1767.7
Avg. Page Density (full)          99.73%         78.16%
Logical Scan Fragmentation   0.00%         11.03%

In this case if we use char instead of varchar, we can save page splits and table fragmentation which can save some time.

Conclusion:
If a column's data does not vary widely in length, we should consider using a fixed-length char field instead of a varchar. While it may take up a little more space to store the data, processing fixed-length columns is faster in SQL Server than processing variable-length columns (in most cases).

In the above scenario, if we can come to a conclusion that most of the time the length of Name values will be more than 80, then char will be better choice than varchar as it will save some extra space.

Another advantage of using char datatype in the above scenario is faster search than varchar. Because for varchar the SQL Server has to verify the length of each record and search through the file in order to locate it, but in case of char it is fixed. So SQL Server needs not to calculate the length of record each time it process, and this can also save some time.