菜鸟提问

pl_mm2 2008-09-17 10:08:11

-- 创建2个测试表

CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL) 

CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL) 

go

-- 填充数据

declare @i int

set @i = 0

while(@i < 20)

begin

    insert Table_2019(Data) values('')

    insert Table_2020(Data) values('')

    select @i = @i + 1

end

go

Table_2020 表数据占了 160kb ，Table_2019 表数据占了 80 kb???

...全文

306 27 打赏收藏转发到动态举报

写回复

用AI写文章

27 条回复

切换为时间正序

请发表友善的回复…

发表回复

fcuandy 2008-09-17

打赏
举报

偏移量是2字节每行。

我也是算不出来，为什么差了4字节，两个就不一样的方式存了。

pl_mm2 2008-09-17

打赏
举报

感谢大家参与！谢谢

Yang_ 2008-09-17

打赏
举报

[Quote=引用 16 楼 Garnett_KG 的回复:]
简单点的讲，

Table_2019的一个数据页上可以放2条记录
Table_2020的一个数据页上只能放1条记录.

所以Table_2020的数据页的需求数是Table_2019的两倍。
[/Quote]

这个显然是只能这么解释的。

问题是：
我们知道，数据页是8K=8*1024=8192字节，数据页都有96字节的页首，这样用于存放数据行和偏移量的空间是8192-96=8096字节，其中每行需要放一个偏移量，具体多少字节没有查到资料。
再看看我们这两个表每行的字节数，nchar(2019)的字节数是2019*2=4038；nchar(2020)的字节数是2020*2=4040
从结果看，一夜可以放两行nchar(2019)，但不可以放两行nchar(2020)
4038*2=8076
4040*2=8080
这里的临界数在8076和8080之间，既不是上面的8096页不是帮助里说的数据行最大是8060，这就是我的疑问

wgzaaa 2008-09-17

打赏
举报

那么：在 SQL Server 2000 中，一行内最多包含的数据量是 8060 字节不是都超过了吗？望解释一下

tcxx2008 2008-09-17

打赏
举报

我晕的,我不知道哦

zefuzhang2008 2008-09-17

打赏
举报

总结如下：



-- 创建2个测试表 

CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL) 

CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL) 

go 

-- 填充数据 

declare @i int 

set @i = 0 

while(@i < 20) 

begin 

    insert Table_2019(Data) values('') 

    insert Table_2020(Data) values('') 

    select @i = @i + 1 

end 

go 

exec sp_spaceused 'Table_2019' 

exec sp_spaceused 'Table_2020' 



--结果： 

Table_2019 20        144 KB 80 KB 16 KB 48 KB 

Table_2020 20        200 KB 160 KB 8 KB 32 KB 



--由此可看出数据空间差异



dbcc extentinfo(test,Table_2019) 

dbcc extentinfo(test,Table_2020) 



--结果： 

4 2691 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

4 2698 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

4 2695 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

4 2703 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

1 65186 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

1 65187 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

1 65188 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

1 65189 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000 

1 65200 2 8 212481573 0 1 72057594044940288 In-row data 0x4444000000000000 





4 2692 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

4 2693 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

4 2694 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

4 2699 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

4 2700 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

4 2701 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

1 65184 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

1 65185 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000 

4 2712 8 8 228481630 0 1 72057594045005824 In-row data 0x4242424242424242 

4 2720 4 8 228481630 0 1 72057594045005824 In-row data 0x4242424200000000 



--由此可得出分配的页面数，第一个表分配了10个页面，第二个表分配了20个

 

结论：第一个表每列的长度4045字节，加上页头96字节，和页尾的每行2字节的偏移量，一个页面可以容纳2行记录的 

即96+4045+4045+4 

而第二个表则只能每页一行记录 

至于为何有单一区的分配要看SGAM和GAM与FPS的协同工作机制

zefuzhang2008 2008-09-17

打赏
举报

补充下吧：
第一个表每列的长度4045字节，加上页头96字节，和页尾的每行2字节的偏移量，一个页面可以容纳2行记录的
即96+4045+4045+4
而第二个表则只能每页一行记录
至于为何有单一区的分配要看SGAM和GAM与FPS的协同工作机制

zjcxc 2008-09-17

打赏
举报

联机帮助位置:
估计堆的大小
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

zjcxc 2008-09-17

打赏
举报

计算方法如下(完全照搬联机帮助上的说明)

DECLARE

	@Num_Cols int, -- 总列数（固定长度和可变长度）

	@Fixed_Data_Size int, --= 所有固定长度列的总字节大小

	@Num_Variable_Cols int, -- = 可变长度列的数量

	@Max_Var_Size int --= 所有可变长度列的最大总字节大小

SELECT

	@Num_Cols = 1,

	@Fixed_Data_Size = 2019 * 2,

	@Num_Variable_Cols = 0,

	@Max_Var_Size = 0

	

-- 保留行中称为空位图的部分以管理列的为空性。计算大小：

DECLARE

	@Null_Bitmap int

SELECT

	@Null_Bitmap = 2 + ((@Num_Cols + 7) / 8)



--只应使用该表达式的整数部分。而去掉其余部分。





--计算可变长度数据的大小：

DECLARE

	@Variable_Data_Size int

--如果没有可变长度列，请将 Variable_Data_Size 设置为 0。

SELECT

	@Variable_Data_Size = 0



--计算总的行大小：

DECLARE

	@Row_Size int

SELECT

	@Row_Size = @Fixed_Data_Size + @Variable_Data_Size + @Null_Bitmap + 4



--公式中的值 4 是数据行的行标题开销。





--下一步，计算每页的行数（每页有 8096 可用字节）：



SELECT

	Rows_Per_Page = 8096 / (@Row_Size + 2)

zjcxc 2008-09-17

打赏
举报

第一个表一页 2 条记录
第二个表一页 1 条记录

zefuzhang2008 2008-09-17

打赏
举报

-- 创建2个测试表
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)
go
-- 填充数据
declare @i int
set @i = 0
while(@i < 20)
begin
insert Table_2019(Data) values('')
insert Table_2020(Data) values('')
select @i = @i + 1
end
go
exec sp_spaceused 'Table_2019'
exec sp_spaceused 'Table_2020'

结果：
Table_2019 20 144 KB 80 KB 16 KB 48 KB
Table_2020 20 200 KB 160 KB 8 KB 32 KB

由此可看出数据空间确实差异很大(80 KB,160 KB)

dbcc extentinfo(test,Table_2019)
dbcc extentinfo(test,Table_2020)

结果：
4 2691 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
4 2698 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
4 2695 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
4 2703 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
1 65186 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
1 65187 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
1 65188 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
1 65189 1 1 212481573 0 1 72057594044940288 In-row data 0x6400000000000000
1 65200 2 8 212481573 0 1 72057594044940288 In-row data 0x4444000000000000

4 2692 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
4 2693 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
4 2694 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
4 2699 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
4 2700 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
4 2701 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
1 65184 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
1 65185 1 1 228481630 0 1 72057594045005824 In-row data 0x6200000000000000
4 2712 8 8 228481630 0 1 72057594045005824 In-row data 0x4242424242424242
4 2720 4 8 228481630 0 1 72057594045005824 In-row data 0x4242424200000000

由此可得出分配的页面数，第一个表分配了10个页面，第二个表分配了20个

注：以第一个表为例，每列的长度并非2019*2，因为页面存储也需要开销，在这里要加上最少7字节的开销，即最小列长度4045字节

至于，为何分配这么多页，要看SGAM和GAM与FPS的协同工作机制，由于篇幅较长不再啰嗦了

Garnett_KG 2008-09-17

打赏
举报

简单点的讲，

Table_2019的一个数据页上可以放2条记录
Table_2020的一个数据页上只能放1条记录.

所以Table_2020的数据页的需求数是Table_2019的两倍。

水族杰纶 2008-09-17

打赏
举报

sp_spaceused table_2019
go
sp_spaceused table_2020

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
Table_2019 20 144 KB 80 KB 16 KB 48 KB

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
Table_2020 20 208 KB 160 KB 16 KB 32 KB

utpcb 2008-09-17

打赏
举报

这个是正确的！

SQL Server 中数据存储的基本单位是页（Page）。数据库中的数据文件（.mdf 或 .ndf）分配的磁盘空间可以从逻辑上划分成页（从 0 到 n 连续编号）。磁盘 I/O 操作在页级执行。也就是说，SQL Server 每次读取或写入数据的最少数据单位是数据页。

注意：日志文件不是用这种方式存储的，而是一系列日志记录。

数据库被分成逻辑页面（每个页面8KB），并且在每个文件中，所有页面都被连续地从0到x编号，其中x是由文件的大小决定的。我们可以通过指定一个数据库ID、一个文件ID、一个页码来引用任何一个数据页。当我们使用ALTER DATABASE命令来扩大一个文件时，新的空间会被加到文件的末尾。也就是说，我们所扩大文件的新空间第一个数据页的页码是x+1。当我们使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令来收缩一个数据库时，将会从数据库中页码最高的页面（文件末尾）开始移除页面，并向页码较低的页面移动。这保证了一个文件中的页码总是连续的。

在 SQL Server 中，页的大小为 8 KB。这意味着 SQL Server 数据库中每 MB 有 128 页。依次类推。根据数据库的文件大小，我们可以算出数据库有多少数据页。

SQL Server 2005 有以下几种页类型：

页类型内容
Data 当 text in row 设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。
Index 索引条目。
Text/Image 大型对象数据类型：

text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。

数据行超过 8 KB 时为可变长度数据类型列：

varchar、nvarchar、varbinary 和 sql_variant

Global Allocation Map、Shared Global Allocation Map 有关区是否分配的信息。
Page Free Space 有关页分配和页的可用空间的信息。
Index Allocation Map 有关每个分配单元中表或索引所使用的区的信息。
Bulk Changed Map 有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。
Differential Changed Map 有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。

数据页（Data 类型页）的结构示意图：

每页的开头是 96 字节的标头，用于存储有关页的系统信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元 ID。

在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。

有关数据页的更多知识，可以通过下面这篇文章获得更详细的了解：

估计在堆中存储数据所需的空间量
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

另外也可以看我收集的资料：怎样查看表的数据页的结构
http://blog.joycode.com/ghj/articles/113108.aspx

对大型行的支持

在 SQL Server 2005 中，行不能跨页，但是行的部分可以移出行所在的页，因此行实际可能非常大。
（比如：一行多列时，这一行的部分列在数据页A，部分列在数据页B）
页的单个行中的最大数据量和开销是 8,060 字节 (8 KB)。但是，这不包括用 Text/Image 页类型存储的数据。
在 SQL Server 2005 中，包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的约束。
当表中的所有固定列和可变列的行的总大小超过限制的 8,060 字节时，SQL Server 将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DATA 分配单元中的页。
每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时，将会执行此操作。
将列移动到 ROW_OVERFLOW_DATA 分配单元中的页后，将在 IN_ROW_DATA 分配单元中的原始页上维护 24 字节的指针。
如果后续操作减小了行的大小，SQL Server 会动态将列移回到原始数据页。

SQL Server 的数据页缓存

SQL Server 数据库的主要用途是存储和检索数据，因此密集型磁盘 I/O 是数据库引擎的一大特点。此外，完成磁盘 I/O 操作要消耗许多资源并且耗时较长，所以 SQL Server 侧重于提高 I/O 效率。缓冲区管理是实现高效 I/O 操作的关键环节。SQL Server 2005 的缓冲区管理组件由下列两种机制组成：用于访问及更新数据库页的缓冲区管理器和用于减少数据库文件 I/O 的缓冲区高速缓存（又称为“缓冲池”）。

缓冲区管理的工作原理
一个缓冲区就是一个 8KB 大小的内存页，其大小与一个数据页或索引页相当。因此，缓冲区高速缓存被划分为多个 8KB 页。缓冲区管理器负责将数据页或索引页从数据库磁盘文件读入缓冲区高速缓存中，并将修改后的页写回磁盘。页一直保留在缓冲区高速缓存中，直到已有一段时间未对其进行引用或者缓冲区管理器需要缓冲区读取更多数据。数据只有在被修改后才重新写入磁盘。在将缓冲区高速缓存中的数据写回磁盘之前，可对其进行多次修改

正宗老冉 2008-09-17

打赏
举报

[Quote=引用 11 楼 HEROWANG 的回复:]
table2020好像能算出来，2020*2*20/1024大约就是160K
但是table2019怎么算出来的？楼主的80K是怎么得到的？

楼主的结贴率是-68.10%，这个是怎么算出来的？不明白
[/Quote]

关注这个!

jacklinchen 2008-09-17

打赏
举报

汗，我一般没去注意这些，关注高手出现

让你望见影子的墙 2008-09-17

打赏
举报

table2020好像能算出来，2020*2*20/1024大约就是160K
但是table2019怎么算出来的？楼主的80K是怎么得到的？

楼主的结贴率是-68.10%，这个是怎么算出来的？不明白

Yang_ 2008-09-17

打赏
举报

你再这样测试（更接近实际点），看有没有你担心的问题

-- 创建2个测试表

CREATE TABLE [dbo].[Table_2019](id int,[Data] [nchar](2019) NOT NULL) 

CREATE TABLE [dbo].[Table_2020](id int,[Data] [nchar](2020) NOT NULL) 

go

-- 填充数据

declare @i int

set @i = 0

while(@i < 20)

begin

    insert Table_2019(id,Data) values(@i,'')

    insert Table_2020(id,Data) values(@i,'')

    select @i = @i + 1

end

go