I need to drop a table or two. Will it affect performance?
I have a database where an automated system creates tables for a data transfer process and then never uses them again. I assume that it should also remove these tables but it would appear that it has stopped doing the tidy up step. As a result I have a few tables that are totally empty and I have the go ahead from the vendor to drop them. Does anyone have any experience of dropping a lot of tables from a database and whether it will bend the server out of shape at all? I think I am going to do them in batches and I will try it in test first, I am just really wondering if anyone else has ever had to drop 1662 tables from a database before?!?!
If you aren't querying them they will not be in cache, so that should not be affected. You may see internal fragmentation of the data file(s) where these tables resided, but that would be expected. You may consider cleaning that up, but it shouldn't be a problem to begin with. I have dropped about 50 tables in a go, plus indexes that belonged to them. The drop, as long as constraints etc. are not there, should be pretty much instantaneous. As the tables are empty, the effects should be minimal though. Go for it on test, then prod - it'll be fine.
I've create a script which creates 1662 create table statements (all with one identity column and nothing more) and one script which creates drop table statements for them all. declare @i int set @i=0 declare @s nvarchar(max) set @s='' while @i<1662 BEGIN -- set @s=@s + N'CREATE TABLE t' + CAST(@i as nvarchar(10)) + ' (id int identity(1,1)) -- GO -- ' set @s=@s + 'DROP TABLE t' + CAST(@i as varchar(10)) + ' GO ' set @i=@i+1 if LEN(@s)>2000 begin print @s set @s='' end END print @s Running on my crappy laptop, they both run in less then five seconds. I guess there's some more overhead to it if you have wider tables, but this at least shows that the magnitude is not that high.