SQL Server – Comparing Temporary vs. Physical Tables

Depending on how you are using your temp tables, you could run into a data truncation issue.

This example is a bit contrived, but it illustrates my point. Example:

1. Your user table column is varchar(50).
2. Your temp table column is varchar(255).
3. You have a record with 45 characters in that column in your user table.
4. In your procedure, you concatenate ' - for the win' to the end of that column, prior to merging that temp table into your user table.

The temp table would gladly accept the new varchar value with a length of 59. However, your user table could not. Depending on how you handle this in your procedure, this could result in truncation or an error.

Unless you document and account for these issues, your procedure could perform in an unexpected manner.

Personally, I do not think there is an answer to this question that is correct 100% of the time. It really depends on how you are using those temp tables.

Hope this helps

If you are looking to get the size information for all tables in the database you can use this query:

SELECT O.type_desc, 
       IndexSize.obj_name,
       I.name AS index_name,
       IndexSize.reserved_MB,
       IndexSize.used_MB,
       IndexSize.row_count,
       IndexSize.object_id,
       IndexSize.index_id
  FROM (
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(DDPS.object_id))+'.'+QUOTENAME(OBJECT_NAME(DDPS.object_id)) obj_name,
           SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
           SUM(DDPS.used_page_count)/128.0 AS used_MB,
           SUM(row_count) AS row_count,
           DDPS.object_id,
           DDPS.index_id
      FROM sys.dm_db_partition_stats AS DDPS
     GROUP BY DDPS.object_id,DDPS.index_id
   )IndexSize
   JOIN sys.objects AS O
     ON IndexSize.object_id = O.object_id
   JOIN sys.indexes AS I
     ON IndexSize.object_id = I.object_id
    AND IndexSize.index_id = I.index_id;

It returns the reserved megabytes and the used megabytes as well as the row count for every index in the database. The first column tells you the object type. This includes system_tables as well as indexed views. If you want a subset, filter on that column.

If you do not need the per-index detail you can use this query instead:

SELECT O.type_desc,
       ObjectSize.obj_name,
       ObjectSize.reserved_MB,
       ObjectSize.used_MB,
       ObjectSize.row_count
  FROM( 
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(IndexSize.object_id))+'.'+QUOTENAME(OBJECT_NAME(IndexSize.object_id)) obj_name,
           SUM(IndexSize.reserved_MB) AS reserved_MB, 
           SUM(IndexSize.used_MB) AS used_MB, 
           MAX(IndexSize.row_count) AS row_count, 
           IndexSize.object_id
      FROM(
        SELECT SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
               SUM(DDPS.used_page_count)/128.0 AS used_MB,
               SUM(row_count) AS row_count,
               DDPS.object_id
          FROM sys.dm_db_partition_stats AS DDPS
         GROUP BY DDPS.object_id,DDPS.index_id
      )IndexSize
     GROUP BY IndexSize.object_id
  )ObjectSize
  JOIN sys.objects AS O
    ON ObjectSize.object_id = O.object_id

It gives the same information, but on an object basis. Again, filter on the object type if you are for example not interested in system_tables.

If you are just interested in normal tables, you can get the information in SSMS too by using the "Object Explorer Details" tab:

You can open it by selecting the table folder in the database in Object Explorer and then pressing F7.

You might have to add the size columns. For that just right-click on the column list and select the columns you want to see: