title: SQL Server Efficiently dropping a group of rows with millions and millions of rows
body: <p>I recently asked this question:
  <a href="https://stackoverflow.com/questions/2519183/ms-sql-share-identity-seed-amongst-tables">MS SQL share identity seed amongst tables</a>
  (Many people wondered why) </p>
  
  <p>I have the following layout of a table:</p>
  
  <p><strong>Table: Stars</strong><br>
  starId bigint<br>
  categoryId bigint<br>
  starname varchar(200)  </p>
  
  <p>But my problem is that I have millions and millions of rows.  So when I want to delete stars from the table Stars it is too intense on SQL Server.</p>
  
  <p>I cannot use built in partitioning for 2005+ because I do not have an enterprise license.</p>
  
  <p><strong>When I do delete though, I always delete a whole category Id at a time.</strong></p>
  
  <p>I thought of doing a design like this:</p>
  
  <p><strong>Table: Star_1</strong><br>
  starId bigint<br>
  CategoryId bigint constaint rock=1<br>
  starname varchar(200)   </p>
  
  <p><strong>Table: Star_2</strong><br>
  starId bigint<br>
  CategoryId bigint constaint rock=2<br>
  starname varchar(200)  </p>
  
  <p>In this way I can delete a whole category and hence millions of rows in O(1) by doing a simple drop table.</p>
  
  <p>My question is, is it a problem to have hundreds of thousands of tables in your SQL Server?  The drop in O(1) is extremely desirable to me.   Maybe there's a completely different solution I'm not thinking of?</p>
  
  <p><strong>Edit:</strong> </p>
  
  <p>Is a star ever modified once it is inserted? No.</p>
  
  <p>Do you ever have to query across star categories? I never have to query across star categories. </p>
  
  <p>If you are looking for data on a particular star, would you know which table to query? Yes </p>
  
  <p>When entering data, how will the application decide which table to put the data into? The insertion of star data is done all at once at the start when the categoryId is created.</p>
  
  <p>How many categories will there be? You can assume there will be infinite star categories. Let's say up to 100 star categories per day and up to 30 star categories not needed per day.</p>
  
  <p>Truly do you need to delete the whole category or only the star that the data changed for? Yes the whole star category.</p>
  
  <p>Have you tried deleting in batches? Yes we do that today, but it is not good enough.
  od enough.</p>
  
  <p>Another technique is mark the record for deletion? There is no need to mark a star as deleted because we know the whole star category is eligible to be deleted.</p>
  
  <p>What proportion of them never get used? Typically we keep each star category data for a couple weeks but sometimes need to keep more.</p>
  
  <p>When you decide one is useful is that good for ever or might it still need to be deleted later?</p>
  
  <p>Not forever, but until a manual request to delete the category is issued.
  If so what % of the time does that happen? Not that often.</p>
  
  <p>What kind of disc arrangement are you using? Single filegroup storage and no partitioning currently. </p>
  
  <p>Can you use sql enterprise ?  No.  There are many people that run this software and they only have sql standard.  It is outside of their budget to get ms sql enterprise. </p>