表大小在SQL Server中很奇怪

I have 2 tables in SQL Server

我在SQL Server中有两个表

TbUrl

TbUrl

INDEX SPACE 12,531 MB
索引空间12531 MB
ROW COUNT 247505
行数247505
DATA SPACE 1.965,891 MB
1.965数据空间,891 MB

Table structure:

表结构:

CREATE TABLE [TbUrl](
    [IdUrl] [Int] IDENTITY(1,1) NOT NULL,
    [IdSupply] [Int] NOT NULL,
    [Uri] [varchar](512) NOT NULL,
    [UrlCod] [varchar](256) NOT NULL,
    [Status] [Int] NOT NULL,
    [InsertionDate] [datetime] NOT NULL,
    [UpdatedDate] [datetime] NULL,
    [UpdatedIp] [varchar](15) NULL

TbUrlDetail

TbUrlDetail

INDEX SPACE 29,406 MB
索引空间29406 MB
ROW COUNT 234209
行数234209
DATA SPACE 386,047 MB
数据空间386047 MB

Structure:

结构:

CREATE TABLE .[TbUrlDetail](
    [IdUrlDetail] [Int] IDENTITY(1,1) NOT NULL,
    [IdUri] [Int] NOT NULL,
    [Title] [varchar](512) NOT NULL,
    [Sku] [varchar](32) NOT NULL,
    [MetaKeywords] [varchar](512) NOT NULL,
    [MetaDescription] [varchar](512) NOT NULL,
    [Price] [money] NOT NULL,
    [Description] [text] NOT NULL,
    [Stock] [Bit] NOT NULL,
    [StarNumber] [Int] NOT NULL,
    [ReviewNumber] [Int] NOT NULL,
    [Category] [varchar](256) NOT NULL,
    [UrlShort] [varchar](32) NULL,
    [ReleaseDate] [datetime] NOT NULL,
    [InsertionDate] [datetime] NOT NULL

The size of TbUrl is very large compared with TbUrlDetail

与TbUrlDetail相比，TbUrl的尺寸非常大

The layout (design) of table TbUrl is less compared with TbUrlDetail but the data space it's else.

与TbUrlDetail相比，表TbUrl的布局(设计)要少一些，但数据空间要少一些。

I´ve done SHRINK ON DATABASE but the space of TbUrl doesn't reduce.

我´已经完成收缩数据库但TbUrl的空间并不减少。

What might be happening? How do I decrease the space of this table?

可能会发生什么?如何减少这张桌子的空间?

2 个解决方案

#1

Is there a clustered index on the table? (If not you could be suffering from a lot of forward pointers - ref.) Have you made drastic changes to the data or the data types or added / dropped columns? (If you have then a lot of the space previously occupied may not be able to be re-used. One ref where changing a fixed-length col to variable does not reclaim space.)

表上有聚集索引吗?(如果没有，您可能会遭受许多正向指针- ref.)您是否对数据或数据类型或添加/删除列进行了重大更改?(如果你有那么许多以前被占用的空间可能不能被再使用。将一个固定长度的col改为variable并不会回收空间)。

In both cases you should be able to recover the wasted space by rebuilding the table (which will also rebuild all of the clustered indexes):

在这两种情况下，您都应该能够通过重新构建表(这也将重新构建所有的聚集索引)来恢复浪费的空间:

ALTER TABLE dbo.TblUrl REBUILD;

If you are on Enterprise Edition you can do this online:

如果你在企业版上，你可以在网上做这个:

ALTER TABLE dbo.TblUrl REBUILD WITH (ONLINE = ON);

Shrinking the entire database is not the magic answer here. And if there is no clustered index on this table, I strongly suggest you consider one before performing the rebuild.

缩小整个数据库并不是这里的神奇答案。如果该表上没有聚集索引，我强烈建议您在执行重新构建之前考虑它。

#2

With VARCHAR() fields, the amount of space actually taken does vary according to the amount of text put in those fields.

对于VARCHAR()字段，实际占用的空间的数量会根据输入到这些字段中的文本的数量而变化。

Could you perhaps have (on average) much shorter entries in one table than in the other?

一个表中的条目(平均而言)是否比另一个表中的条目要短得多?

Try

试一试

SELECT
  SUM(CAST(LENGTH(uri) + LENGTH(urlcod) AS BIGINT)) AS character_count
FROM
  TbUrl

SELECT
  SUM(CAST(LENGTH(title) + LENGTH(metakeywords) + LENGTH(metadescription) + LENGTH(Category) AS BIGINT)) AS character_count
FROM
  TbUrlDetail

#1