Reasonably design the Column Length

 

1 USE testdb
2 GO
3 /************************************** **
4. Heap Storage _ 01
5 Phoenix. Feng 2010-11-18
6 *************************************** */
7 IF OBJECT_ID ('idf _ Heap_01 ') IS NOT NULL
8 drop table IDF_Heap_01
9 GO
10 create table IDF_Heap_01 (
11 id INT,
12 name NCHAR (2017)
13)
14 insert into IDF_Heap_01 VALUES (1, 'phoenix ')
15 insert into IDF_Heap_01 VALUES (2, 'kevin ')
16 insert into IDF_Heap_01 VALUES (3, 'yy ')
17 insert into IDF_Heap_01 VALUES (4, 'terry ')

 

1 /************************************** **
2 Heap Storage _ 02
3 Phoenix. Feng 2010-03-16
4 *************************************** */
5 IF OBJECT_ID ('idf _ Heap_02 ') IS NOT NULL
6 drop table IDF_Heap_02
7 GO
8 create table IDF_Heap_02 (
9 id INT,
10 name NCHAR (2018)
11)
12 insert into IDF_Heap_02 VALUES (1, 'phoenix ')
13 insert into IDF_Heap_02 VALUES (2, 'kevin ')
14 insert into IDF_Heap_02 VALUES (3, 'yy ')
15 insert into IDF_Heap_02 VALUES (4, 'terry ')
16 GO

I created two tables here. They seem to have no difference, but the string length of the name column in the first table is 2017, followed by 2018:

However, by checking the IAM allocation, the results are surprising:

1 SELECT total_pages, used_pages, data_pages, 2 first_page, root_page, first_iam_page,
3 dbo. f_get_page (first_page) first_page,
4 dbo. f_get_page (root_page) root_page,
5 dbo. f_get_page (first_iam_page) first_iam_page
6 FROM sys. system_internals_allocation_units
7 WHERE container_id IN (
8 SELECT partition_id FROM sys. partitions
9 WHERE object_id in (
10 SELECT object_id FROM sys. objects WHERE name = 'idf _ Heap_01'
11)
12)
13 GO
14 SELECT total_pages, used_pages, data_pages,
15 first_page, root_page, first_iam_page,
16 dbo. f_get_page (first_page) first_page,
17 dbo. f_get_page (root_page) root_page,
18 dbo. f_get_page (first_iam_page) first_iam_page
19 FROM sys. system_internals_allocation_units
20 WHERE container_id IN (
21 SELECT partition_id FROM sys. partitions
22 WHERE object_id in (
23 SELECT object_id FROM sys. objects WHERE name = 'idf _ Heap_02'
24)
25)
26 GO

The query result is displayed here:

In the second table, the name is only one longer than the first table, but the data page used by the name is twice that of the former, that is, one data page.

In fact, the reason is very simple. In table 2, the size of each row of data is just more than half the size of each page stored in SQL server. For details, refer to MSDN.