CODEWITHSUNDEEP
sqldatabasesql-server-2008database-design
Jeferson Tenorio
Jeferson Tenorio

Reputation: 2190

Table size strange in SQL Server

I have 2 tables in SQL Server

TbUrl

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

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

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

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

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

Upvotes: 1

Views: 186

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

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.)

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.

Upvotes: 3

MatBailie
MatBailie

Reputation: 86808

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

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

Upvotes: 0

Related Questions