<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Advantage of using char over varchar data type

Published on
11,156 Points
4,956 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
Comment
Author:s_niladri
0 Comments

Featured Post

Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Join & Write a Comment

Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month