Will frequent creation and deletion of TABLE (not indexes) cause fragmentation in database?

Question

Does frequent creation and deletion of physical table in database causes fragmentation? I am frequently creating and deleting database for loading data warehouse load. I am using these table to hold records temporarily for batch updates.

This question is asked primarily from SQL Server 2016 and Azure Data Warehouse (MPP) point of view. But i would like to extend this question for any databases (if true)

Answer 1

There are quite a lot of factors as to whether the staging table will be fragmented, and if so, how much. The most obvious that come to mind are:

• How much data is being added to the table
• How much other tables are adding or deleting rows at the same time
• Where exactly in the database file the table was created and if there are a lot of contiguous free extents

So basically, the answer is that it is quite likely that the table will be fragmented. They won't necessarily cause your other permanent tables to be fragmented much, but again, if they are growing at the same time and need new extents, then yes.

All that said, the more important questions are:

• Does the fragmentation cause a performance problem? Not likely. Fragmentation is not the problem it used to be when you had 3 hard drives in a RAID 5 array. On Azure your data is using 100s of disks, so the combined read and write power reduces the performance impact of fragmentation to almost nil. See Stop Worrying About SQL Server Fragmentation"
• Is there anything I can do about it? Nothing reasonable to stop it unless you want to create a different filegroup on another volume dedicated to the staging tables. You could also rebuild your other tables and indexes with a lower fill factor and that may stop them from becoming fragmented as much if data is being inserted into someplace other than then end of the table (which might indicate poor table design), but again, see point above. Perhaps the most reasonable option is to simply run your reindexing job after the ETL job is done.

Answer 2

When you create a table (and subsequently insert the first row), SQL Server allocates a uniform extent for the data and a page from a shared extent for the IAM page. This is repeated also for each index. As more rows are inserted, more extents are allocated as needed (and, of course also more IAM pages from shared extents). Drop that table and those extents and pages are unallocated (and of course also marked as "free" in the GAM/SGAM pages. Come creation next time, and we are back where we started from. No recollection from that this has happened earlier, hence no fragmentation.

That is, unless you keep growing and shrinking your database files, but I take it that we aren't those kind of questionable things. :-)