What should I use as a primary key/index for a table with no unique columns that are mostly written to? I have a table on a SQL server database that stores logging information about various user activity. Date/time, user id, and a few columns that specify where and what was done. None of this data is guaranteed to be unique (date/time will likely be unique, but it's far from guaranteed). This table will be written to often and searched rarely. I'd like to have it optimized for inserting, but at the same time be able to search the large quantities of data in as short a period of time as possible.

I'm still very new to sql server, if I was to have every column indexed, would that be a big performance hit with constantly adding new entries?

Edit: The problem is, it can be important to search for a lot of stuff. Some of the things this logs is who looks at important client personal information. So, ideally, you should be able to search for both which users accessed this specific information (select user where thing_done=looked at that data) as well as what a suspect user has been up to (select alldata where user=suspect). The speed of this is heavily dependent on those indexes though, right?
Q: If were to to have every column indexed, would that be a big
  performance hit?


A: Uh - yes :)  It would :)

Don't do it :)


  Q: I'd like to be able to search the large quantities of data in as
  short a period of time as possible


A: And that's precisely what you want to build your indexes on.

What are you likely to be "searching"?

I'm guessing date/time (a certain range of minutes/hours/days/etc) and some "type".  For example, users might be "eating", "sleeping" or "studying".

You definitely want at least one index, and you definitely want to "strategically determine" what should be your "clustered index".  Clustered indexes are a Sql Server thing.

You can learn more here:


https://stackoverflow.com/questions/9085390/best-practice-for-storing-millions-of-rows-with-tsql-sql-server-2008
http://msdn.microsoft.com/en-us/library/aa933131%28v=sql.80%29.aspx