Reputation: 2190
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
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
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