I recently asked this question: http://stackoverflow.com/questions/2519183/ms-sql-share-identity-seed-amongst-tables (Many people wondered why)

I have the following layout of a table:

Table: Stars
starId bigint
categoryId bigint
starname varchar(200)

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.

I cannot use built in partitioning for 2005+ because I do not have an enterprise license.

When I do delete though, I always delete a whole category Id at a time.

I thought of doing a design like this:

Table: Star_1
starId bigint
CategoryId bigint constaint rock=1
starname varchar(200)

Table: Star_2
starId bigint
CategoryId bigint constaint rock=2
starname varchar(200)

In this way I can delete a whole category and hence millions of rows in O(1) by doing a simple drop table.

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?