char(1) and char(2) take same space?

I create two tables:

create table table1

(

col1 char(1)

)

go

create table table2

(

col2 char(2)

)

go

I add some records to two tables after createing operation completed.

Then i use dbcc page command to oversee the structures of data page in two tables.

I found some interest things:

The rows in two tabes take up same space:9 bytes

You can see the "9" on top of the data, for example:Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

or calculate from the offset array

Any suggestions?

Did you enter same type of data in both tables?
|||

yes

And you can found that :the two tables can both contain as many as 699 rows per data page.

|||That surprising. Char(2) suppose to use 2 bytes and Char(1) suppose to use 1.
Can you tell me how many records you have in both tables?
|||

the sql script i used:

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table1 values('a')
set @.a=@.a+1
end

-

declare @.a int
set @.a=0
while @.a<4000
begin
insert into table2 values('aa')
set @.a=@.a+1
end

the rows in two tables both take up 9 bytes no matter how many rows you insertd.

|||

I assume you're only adding single character values to each table?

If thats the case, then you more than likely have the value SET ANSI_PADDING OFF when you created your tables. With this setting, any nullable char columns will be treated like varchar columns.

HTH!

|||

sorry, i don't understand what you say...

|||char(1) takes 1 byte, while char(2) takes up two bytes.

However, there is one other thing to consider: The minimum record size that SQL Server uses for data records is 9 bytes. Data records are records that are either records that are stored on leaf-pages on a B-Tree or are records stored in a heap.

The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.

For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.

To really see the difference between char(1) and char(2), create a record that is more than 9 bytes (say add a char(9) to it), and then create one table with the char(1) and another one with char(2). You'll see that the record size will be different.

Thanks,|||

Thats interesting stuff, Marcel.

However, can you clarify one thing. If each record stores a minimum of 9bytes per record, would you not expect the value from dbcc page to show 9 + the length of the data.

eg In this example, 10 (9bytes forwarding pointer and 1byte char(1) or 11 (9bytes forwarding pointer and 2bytes char(2))

Cheers

|||

The forwarding pointer is only stored if it is needed. In case a record is not forwarded, we don't store the forwarding pointer.

In a heap we only forward if a record gets updated, and the updated record does not fit on the page anymore. IN this case, we move the record to a new page, but leave a 9 byte forwarding pointer record on the original page.

DBCC PAGE shows the actual length of a record.

Suppose we have the following table:

CREATE TABLE t1 (col1 char(1)).

When a record is created, the actual size is 2 bytes header, 2 bytes for where null bitmap starts, 1 byte for null bit map, and 1 byte for the actual char(1) data. So the record would be 6 bytes. However, because a record needs to be at least 9 bytes, we make the record size 9 bytes.

Suppose now that we had the following table

CREATE TABLE t1 (col1 char(10)).

In this case the length of a record would be 2 bytes ehader, 2 byes for where nullbitmap starts, 1 byte for null bit map and 10 bytes for actual data. So the record would be 15 bytes.

Thanks,

|||

HI Marcel

Why sql server need forwarding pointer?

Only point the new Page which the original record stored?

We can use IAM to find all the pages of table? why need forwarding pointer?

thanks

|||

Forwarding pointers are used for heaps in case an row that was placed on a certain page gets updated and does not fit on the page anymore.

Let's go through an example.

Suppose there is a row on Page 1:100, and the row is placed in slot 0 on that page. In this case, the row ID (RID) is 1:100:0. This rowid is used to uniquely identify the row going forward.

Suppose not that the heap has a number of non-clustered indexes defined on it. The leaf pages of the non-clustered index contain the RID of the actual row. When the non-clustered index is traversed, and a leaf row is reached, SQL Server uses the RID to find the actual data for that row. So in the above example, SQL reads RID 1:100:0, and knows that it has to go to page 1:100, slot 0.

Let's now assume that the original row gets updated, and that because of update the row grows and does not fit on page 1:100 anymore (keep in mind that there are other rows on page 1:100 as well which take up spaces).

At this point, SQL Server could do two things:

1) It could move the row to a new page, and update all the RIDs in the non-clustered indexes for the original row. This is a very expensive operation, and thus SQL Server does not do this.

2) To keep the RID the same, SQL Server moves the row to a new page (for instance, 2:150:0, i.e. page 2:150, slot 0). On the original location (1:100:0), it keeps a very small, 9 byte forwarding pointer that points to slot 2:150:0.

Now when the data is retrieved, SQL Server reads the forwarding record, and uses the infromation in the forwarding record to find the actual record which is places on 2:150:0.

Because of this, SQL Server needs to guarantee that it can put at least a 9-byte forwarding record on any page that is used by an existing row, meaning that if a row is less than 9-byte, it will reserve 9 bytes anyways.

If the explanation is still unclear, it might help to have a look at the book 'Inside SQL Server 2005: the storage engine' by Kalen Delaney (http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_bbs_sr_1/002-2072619-2748829?ie=UTF8&s=books&qid=1187755949&sr=8-1).

The book has a better description than I can give here (I think there are about 10 pages or so about different record formats, etc).

Thanks,

|||

Marcel

thanks for your reply