Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-21 : 20:22:44
|
Larry A writes "One of the questions on your site was whether Varchar data types were padded. Your response was no. Your explanation was based on a test involving 10 rows, each containing a single varchar(8000) column populated with 1 character each. Because the resulting table was less than one 8K page, you concluded no padding occurred.
I seemed to remember that each varchar column requires 10 additional bytes needed to represent the column size and/or beginning/ending column locations. Also, a performance hit occured due to the need to read these extra bytes to resolve the actual data location.
So I ran my own test as follows. I created a test table called TestVarchar containing only one field without keys or indexes. The field was a varchar(10). I populated this table with 800 rows of '1234567890'. The following query indicates 3 pages averaging 1962.7 free bytes. This indicates the data required 18,291 total bytes (8060 X 3 less 1962.7 X 3) vs. the nominal 8000 (800 X 10). Stated another way, 12.8 extra bytes per row.
Running the same test with 8000 rows of 1 character yielded 13.9 extra bytes per row.
I guess that about says it all. Not really "padding", just additional overhead data to resolve varying column widths. Your answer was incomplete.
Regards,
Larry A.
DBCC SHOWCONTIG scanning 'TestVarchar' table...
Table: 'TestVarchar' (1423344135); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 3
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.5
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 1962.7
- Avg. Page Density (full).....................: 75.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator." |
|
|
|
|
|
Varchar vs Char. Does varchar have padding?