-2

I will be having a table in SQL Server 2008 that will hold millions of rows and the initial design will be:

Code nvarchar(50) PK
Available bit
validUntil DateTime
ImportID int

The users can import 100,000 odd codes at a time which I will be using sqlbulkcopy to insert the data. They can also request batches of codes of up to 10,000 at a time for a specific ImportID and as long as the request date is less than the ValidUntil date and the code is available.

My question is, will it be better to hold all these codes in the one table and use indexes or to split the one table into two - AvailableCodes and UsedCodes - so whenever codes are requested, they are moved from the AvailableCodes table into the UsedCodes table instead of having the Available flag? That way the AvailableCodes table won't get as massive as a single table as over time there will be more used codes than available codes and I will not be that bothered about them accept for auditing purposes.

Also, if the tables are split will I still be able to use the sqlbulkcopy as the codes will still need to be unique across both tables?

knappster
  • 401
  • 9
  • 23
  • Obviously as I wouldn't be posting and as you can see below, someone has answered my "question" – knappster Mar 27 '12 at 19:18
  • My point is that your question is difficult to decipher - you need to work on your question writing skills if you want more and better answers to your questions. – Oded Mar 27 '12 at 19:24
  • Sorry, if you said it was difficult to decipher in the first place then I would've known what you meant. I have now revised the question which I hope makes it clearer. – knappster Mar 27 '12 at 20:08
  • You would still be able to bulk copy in the 2 table model. You would insert into the available table with an identity column. Then the used ID column would not be identity and populated from the available table. – Russell Hart Mar 27 '12 at 20:15
  • If I am moving rows from the Available table to the Used table when they have been requested then wouldn't it still be possible for the bulk copy to insert a code into the Available table which has already been requested and moved to the Used table, or have I got it wrong? The codes need to be unique between both tables. – knappster Mar 27 '12 at 20:58

1 Answers1

3

I would create it in one table and create well defined indexes.

Consider a filter index for the flag column. This is done with a where clause in t-sql and the filter page in ssms.

http://msdn.microsoft.com/en-us/library/cc280372.aspx

Russell Hart
  • 1,842
  • 13
  • 20