>>I created a procedure to copy a SQL database to another database in the same instance. It works pretty simple by first creating the database schema with all tables, indexes etc. in a new database and then inserting the rows from the original database tables using INSERT INTO NewDatabase.dbo.Tablename (field1, field2...) SELECT field1, field2... FROM OriginalDatabase.dbo.Tablename.
>>
>>That works pretty well, however when looking the database size the new database is much smaller in disk size than the original database. I counted all records in all tables and they all are the same, so the data is all the same. Only the size difference concerned me, at first it looks like a loss of information. Maybe somebody has some insight what SQL Server stores besides of the actual data and for what purpose, and should I be concerned about the size difference of the database copy?
>
>Do you have the same settings for log files in both databases? Also, I assume in original data you deleted some rows, right? That space is not immediately reclaimed, so there is no wonder your new database being smaller in size.
>
>What is the difference in size (in %) between these 2 databases?
>
>I think you should not worry.
>
>In addition, if you inserted your rows in the order of the clustered index, you also may have less page splits when with your original data inserts which could have been inserted in some random order.


Good points, I will check for deleted records. Is there a way in SQL Server to optimize tables (like a pack in VFP?) The settings should be the same because the databases were created with the same code. If it's due to deleted records a "Pack" should bring the two databases closer in size, just to get reaffirmation. The percentage is quite high, around 40% less.