I came across this issue while troubleshooting a problem that I described in another post, but I thought this deserves a post of its own.
I'm trying to reclaim some unused space in a table. This is the current disk usage report for the table:
This is the current disk usage report for the database:

As an experiment, I did the following:
- Used the bcp utility to export the table contents:
EXECUTE sys.xp_cmdshell 'bcp MyDb.dbo.MyTable out c:\bcp\MyTable.bcp -n -S MyServer -T'
- Created a new database.
- Created the table with its schema in the new database:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[Column1] [int] NOT NULL,
[Column2] [int] NOT NULL,
[Column3] [int] NOT NULL,
[Column4] [bit] NOT NULL,
[Column5] [tinyint] NOT NULL,
[Column6] [datetime] NULL,
[Column7] [int] NOT NULL,
[Column8] [varchar](100) NULL,
[Column9] [varchar](256) NULL,
[Column10] [int] NULL,
[Column11] [varbinary](max) NULL,
[Column12] [varchar](max) NULL,
[Column13] [varchar](100) NULL,
[Column14] [varchar](6) NULL,
[Column15] [int] NOT NULL,
[Column16] [bit] NOT NULL,
[Column17] [datetime] NULL,
[Column18] [varchar](50) NULL,
[Column19] [varchar](50) NULL,
[Column20] [varchar](60) NULL,
[Column21] [varchar](20) NULL,
[Column22] [varchar](120) NULL,
[Column23] [varchar](4) NULL,
[Column24] [varchar](75) NULL,
[Column25] [char](1) NULL,
[Column26] [varchar](50) NULL,
[Column27] [varchar](128) NULL,
[Column28] [varchar](50) NULL,
[Column29] [int] NULL,
[Column30] [varchar](max) NULL,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
[Column1] ASC,
[Column2] ASC,
[Column3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column4] DEFAULT (0) FOR [Column4]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column5] DEFAULT (0) FOR [Column5]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column15] DEFAULT (0) FOR [Column15]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column16] DEFAULT (0) FOR [Column16]
GO
- Inserted the exported data into the new table:
BULK INSERT dbo.MyTable FROM 'c:\bcp\MyTable.bcp'
WITH (
DATAFILETYPE = 'native',
ORDER(Column1,Column2,Column3),
TABLOCK,
KEEPIDENTITY
)
Now here are the disk usage reports for the new database:
We can see that we've eliminated most of the unused space, but the space for the data has gone way up, from 4.5 GB to 8.2 GB, resulting in a negligible change in the overall space.
So, why is this happening? Is this kind of outcome expected?


