Advertisements
Home > SQL 2005, SQL Internals > VARCHAR or CHAR?

VARCHAR or CHAR?

Question: Which column type to use?
Answer: It depends ….

Explanation: SQL Server stores fixed width and variable length columns different. When storing a VARCHAR, there can be 2-byte overhead for each column plus an additional 2-byte overhead for the row. SQL Server does not record the length of the data saved in a VARCHAR field. That is when saving someone’s first name or last name in a VARCHAR(50) field it doesn’t actually keep track of if it was 10 characters or 20 characters; what it records is the ending offset for the column. So if the field value is NULL it points to the end offset points to start of the next columns value. But when we store CHAR field it takes up the space whether we have any value in the field or not. That is with CHAR even NULL fields take up space. But VARCHAR depending on where they are in the field list may or may not take up space.

I created the following table with three VARCHAR columns:

CREATE TABLE FixVarWidthOnly
(Col1 INT NULL,
Col2 VARCHAR(10) NULL,
Col3 VARCHAR(10) NULL,
Col4 VARCHAR(10) NULL )
GO

And inserted following value into it:

INSERT INTO FixVarWidthOnly VALUES (1, 'AAAAAAAAAA', 'BBBBBBBBBB', 'CCCCCCCCCC')

Now if I look at the page I get the following information:

00000000: 30000800 01000000 0400f003 001d0027 †0…………..’
00000010: 00310041 41414141 41414141 41424242 †.1.AAAAAAAAAABBB
00000020: 42424242 42424243 43434343 43434343 †BBBBBBBCCCCCCCCC
00000030: 43†††††††††††††††††††††††††††††††††††C

This page dump shows number of columns, my data, and tells me which columns are null. Let’s look at the bolded section …

01000000 0400 F0 0300 1D00 2700 3100

This starts with the integer value, number of columns, NULL Bitmap, Array size of variable length columns ending offset; followed by the three offset values indicating where the column value finished. So to store all three columns it took (2 Bytes+(# VARCHAR Columns*2) ) = 8 Bytes of overhead to store this information.

Question: So we had value in each of the variable length columns; what if they were null then do we still have the 2-byte over head for the column?
Answer: It Depends! If the column that was null was at the end of the list of three columns then we don’t have the two byte over head.

Explanation: So with same table above I inserted following rows and looked at the information stored in the page:

INSERT INTO FixVarWidthOnly VALUES (2 , 'AAAAAAAAAA', 'BBBBBBBBBB', NULL )
INSERT INTO FixVarWidthOnly VALUES (3 , 'AAAAAAAAAA', NULL, NULL )
INSERT INTO FixVarWidthOnly VALUES (4 , NULL, NULL, NULL )
INSERT INTO FixVarWidthOnly VALUES (5 , NULL, 'BBBBBBBBBB', 'CCCCCCCCCC' )
INSERT INTO FixVarWidthOnly VALUES (6 , 'AAAAAAAAAA', NULL, 'CCCCCCCCCC' )
INSERT INTO FixVarWidthOnly VALUES (7 , NULL, NULL, 'CCCCCCCCCC' )

Row 1 (Original Row):
00000000: 30000800 01000000 0400f003 001d0027 †0…………..’
00000010: 00310041 41414141 41414141 41424242 †.1.AAAAAAAAAABBB
00000020: 42424242 42424243 43434343 43434343 †BBBBBBBCCCCCCCCC
00000030: 43†††††††††††††††††††††††††††††††††††C

Row 2 (Last value NULL):
00000000: 30000800 02000000 0400f802 001b0025 †0…………..%
00000010: 00414141 41414141 41414142 42424242 †.AAAAAAAAAABBBBB
00000020: 42424242 42††††††††††††††††††††††††††BBBBB

Row 3 (Last two values NULL):
00000000: 30000800 03000000 0400fc01 00190041 †0…………..A
00000010: 41414141 41414141 41†††††††††††††††††AAAAAAAAA

Row 4 (All VARCHAR columns NULL):
00000000: 10000800 04000000 0400fe†††††††††††††………..

Row 5 (First column NULL):
00000000: 30000800 05000000 0400f203 0013001d †0……………
00000010: 00270042 42424242 42424242 42434343 †.’.BBBBBBBBBBCCC
00000020: 43434343 434343††††††††††††††††††††††CCCCCCC

Row 6 (Middle column NULL):
00000000: 30000800 06000000 0400f403 001d001d †0……………
00000010: 00270041 41414141 41414141 41434343 †.’.AAAAAAAAAACCC
00000020: 43434343 434343††††††††††††††††††††††CCCCCCC

Row 7 (First two VARCHAR columns NULL):
00000000: 30000800 07000000 0400f603 00130013 †0……………
00000010: 001d0043 43434343 43434343 43††††††††…CCCCCCCCCC

I highlighted all the key values before VARCHAR data information starts, below is summary:

Row Number Column Count NULL Bitmap Array Size 1st Offset 2nd Offset 3rd Offset Overhead
01000000 0400 F0 0300 1D00 2700 3100 8
02000000 0400 F8 0200 1B00 2500 6
03000000 0400 FC 0100 1900 4
04000000 0400 FE 0
05000000 0400 F2 0300 1300 1D00 2700 8
06000000 0400 F4 0300 1D00 1D00 2700 8
07000000 0400 F6 0300 1300 1300 1D00 8

As you see in above example if the NULL VARCHAR columns are at not at the end in the table it takes the maximum number of bytes over head as if there was actual data in the column.

Question: So how is CHAR field stored in the SQL Server?
Answer: They are stored like integer field, because they are fixed width; they take fixed space whether the value is null or not.

This time I create a table with FIXED width CHAR type:

CREATE TABLE FixWidthOnly
(Col1 INT NULL ,
Col2 CHAR(10) NULL,
Col3 CHAR(10) NULL,
Col4 CHAR(10) NULL )
GO

And I create similar set of rows in the new table:

INSERT INTO FixWidthOnly VALUES (1 , 'AAAAAAAAAA', 'BBBBBBBBBB', 'CCCCCCCCCC' )
INSERT INTO FixWidthOnly VALUES (2 , 'AAAAAAAAAA', 'BBBBBBBBBB', NULL )
INSERT INTO FixWidthOnly VALUES (3 , 'AAAAAAAAAA', NULL, NULL )
INSERT INTO FixWidthOnly VALUES (4 , NULL, NULL, NULL )
INSERT INTO FixWidthOnly VALUES (5 , NULL, 'BBBBBBBBBB', 'CCCCCCCCCC' )
INSERT INTO FixWidthOnly VALUES (6 , 'AAAAAAAAAA', NULL, 'CCCCCCCCCC' )
INSERT INTO FixWidthOnly VALUES (7 , NULL, NULL, 'CCCCCCCCCC' )

I take the output of the page:

Row 1 (Nothing Null):
00000000: 10002600 01000000 41414141 41414141 †..&…..AAAAAAAA
00000010: 41414242 42424242 42424242 43434343 †AABBBBBBBBBBCCCC
00000020: 43434343 43430400 f0†††††††††††††††††CCCCCC…

Row 2 (Last value NULL):
00000000: 10002600 02000000 41414141 41414141 †..&…..AAAAAAAA
00000010: 41414242 42424242 42424242 43434343 †AABBBBBBBBBBCCCC
00000020: 43434343 43430400 f8†††††††††††††††††CCCCCC…

Row 3 (Last two values NULL):
00000000: 10002600 03000000 41414141 41414141 †..&…..AAAAAAAA
00000010: 41414242 42424242 42424242 43434343 †AABBBBBBBBBBCCCC
00000020: 43434343 43430400 fc†††††††††††††††††CCCCCC…

Row 4 (All CHAR columns NULL):
00000000: 10002600 04000000 41414141 41414141 †..&…..AAAAAAAA
00000010: 41414242 42424242 42424242 43434343 †AABBBBBBBBBBCCCC
00000020: 43434343 43430400 fe†††††††††††††††††CCCCCC…

Row 5 (First column NULL):
00000000: 10002600 05000000 41414141 41414141 †..&…..AAAAAAAA
00000010: 41414242 42424242 42424242 43434343 †AABBBBBBBBBBCCCC
00000020: 43434343 43430400 f2†††††††††††††††††CCCCCC…

Row 6 (Middle column NULL):
00000000: 10002600 06000000 41414141 41414141 †..&…..AAAAAAAA
00000010: 41414242 42424242 42424242 43434343 †AABBBBBBBBBBCCCC
00000020: 43434343 43430400 f4†††††††††††††††††CCCCCC…

Row 7 (First two CHAR columns NULL):
00000000: 10002600 07000000 41414141 41414141 †..&…..AAAAAAAA
00000010: 41414242 42424242 42424242 43434343 †AABBBBBBBBBBCCCC
00000020: 43434343 43430400 f6†††††††††††††††††CCCCCC…

If you notice they all look exactly the same; so how does SQL know which field is null it’s by the NULL Bitmap value. The value that is bolded for each of the 7 rows above; for example a value F6 translated to Binary is 11110110; indicating first and forth row are not null. But the actual page is taking the full 30 bytes each time for CHAR whether it is empty or not.

Final notes:

  • Fix-width character fields use space whether you have anything saved field or not; so if there is a percentage of rows that might be null consider using VARCHAR. For example if 50% of the rows are going to be null then the varchar(2) is equivalent to char(2); you can see the minimum field length required before varchar(2) becomes as good as char by using FS = (2/%Null) – 2. (** Note this is based on just column size not row size **).
  • Variable length character fields take at minimum two bytes of overhead to store.
  • Variable fields should be ordered in the table by least nullable to most nullable; i.e. fields that have higher null value should come later in the table.

References:

Inside Microsoft SQL Server 2005: The Storage Engine, Kalen Delaney.
How to use DBCC Page, Microsoft SQL Server Storage Engine Internals Blog, Link.
How Does SQL Server Engine find Variable Length Columns, Paul S. Randel, Link.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: