title: Differences in Table size
body: <p>I have to check big tables on our SQL Server and shrink these tables if possible. See select below.
  One table is about 18 GB. This big table contains a column of type Image which stores binary data. </p>
  
  <pre><code>    SELECT 
      s.Name AS SchemaName,
      t.NAME AS TableName,
      p.rows AS Zeilenanzahl,
      SUM(a.total_pages) * 8 AS [Total Storage], 
      SUM(a.used_pages) * 8 AS [Used Storage], 
      (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS [Free Storage]
  FROM 
      sys.tables t
  INNER JOIN 
      sys.schemas s ON s.schema_id = t.schema_id
  INNER JOIN      
      sys.indexes i ON t.OBJECT_ID = i.object_id
  INNER JOIN 
      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  INNER JOIN 
      sys.allocation_units a ON p.partition_id = a.container_id
  WHERE   
       t.is_ms_shipped = 0
      AND i.OBJECT_ID &gt; 255 
  GROUP BY 
      t.Name, s.Name, p.Rows
  ORDER BY 
      [Total Storage] desc, s.Name, t.Name
  </code></pre>
  
  <p>If I check the image field I get about 700 MB.</p>
  
  <pre><code>  select sum( datalength(Property))
      from dbo.TIF_UserDWProperty
  </code></pre>
  
  <p>I have checked the actual Size with this:</p>
  
  <pre><code>select * into NewTable
    from dbo.TIF_UserDWProperty
  </code></pre>
  
  <p>The newly created table has the correct size of about 700 MB.</p>
  
  <p>Where does this difference came from? 
  How can I shrink this table? 
  I have tried DBCC SHRINKDATABASE with no success.</p>
  
  <p>Thank in advance.</p>