<

Advantage of using char over varchar data type

Published on
11,484 Points
5,284 Views
2 Endorsements
Last Modified:
Approved
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))

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.

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'

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%

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.
2
Author:s_niladri
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free