Primary Key when storing a lot of data for a short period of time

Question

Hi, i'm having doubts on how to create a table for temporary storing error messages.

1. A lot of inserts and deletes
2. A GUID as foreign key, likely to be left outer joined
3. A simple nvarchar field for the ErrorMessage

Which primary key, if any, and indexes should I use for storing a lot of data for a short period of time?

Thanks

Answer 1

I would put an index on your GUID foreign ID field, because your going to be looking up the errors based on that. As for primary key. you haven't mentioned a requirement that forces a need for a primary key. And that will just add some extra overhead. However, you'll probably need to do a sort, and for that you'll need a numeric field, or a date field. I suggest simply adding a numeric field to the table, so you can sort based on the order error messages were created. But you probably don't need to make that field a primary field, as the data doesn't last long.

Answer 2

I would disagree with Jay - check out Kim Tripp's The Clustered Index Debate continues.

Among other things, she says that having a good primary/clustered key (on an INT IDENTITY column - NOT a GUID column) will actually speed up your inserts and deletes.

So even if you use your table only for a short period of time, it would be advisable to have a TableID INT IDENTITY(1,1) PRIMARY KEY column to get a good, fast primary key and clustered index, and as little other indices as possible (since those will slow down inserts for sure).

Depending on how often you'll be referencing your GUID column as a foreign key, you might also want to add an index there - since it'll speed up lookups for sure.

If you're bulk-loading lots of data (says: ten of thousands of rows at once), you could also think about dropping that index before the load and recreating it once the data is loaded (which is probably going to be faster than having it in place all the time) - but again: that depends on the amount of data you're loading, and how often.

Marc