Best practices when storing logging information to table
To directly answer each of your questions:
Q: What should we do about unique key?
Q: How should our clustered index be?
A: In most cases it's important to have a primary key so you can guarantee uniqueness (including this case). Only in a staging table it could possibly be an exception to not use one. It may even serve useful for adhoc debugging later on and is cheap to use an INT or BIGINT auto-increment field as one. But you can (and should) still make your logTime column the clustered index on the table for performance reasons, since it's what will be used as a predicate in a lot of your queries, and the uniqifier step SQL Server does is super negligible from a performance perspective. If you do add a LogId as the primary key, you can add a nonclustered index to it. That way if you need to do any adhoc maintenance (e.g. deleting / updating specific records in the table, you can efficiently seek them by the primary key).
Q: Should we use lookup tables?
A: In this case I would recommend using a lookup / &quot;enums&quot; table. Not so much from a space saving or performance perspective, but mainly because if one of those values change you can very efficiently maintain it by only updating the lookup table as opposed to having to update every record referencing that value in the main table.
Q: Can we use WITH (NOLOCK)?
A: It is not possible for you to use the WITH (NO LOCK) query hint here and be guaranteed to always have 100% accurate data, even if you're selecting from a different set of rows than the ones being INSERTED or UPDATED. I've maintained queries in the past that do use it and very rarely have had an issue, but my personal choice (unless data accuracy is not a concern) is to avoid it. There are much better performance improvement alternatives for query tuning. By the way, I once thought the same way about the NOLOCK hint and you can see Microsoft's David Browne's answer to my question for a conclusive reason why the data accuracy isn't guaranteed. In short, the rows you're selecting against may be part of a changing data structure because of the other further away rows that are being INSERTED or UPDATED also being part of that same data structure, e.g. B-Tree for a particular index both rows are a part of.
Q: Any other best practices?
A: Maybe consider other possible ways logs might need to be queried, e.g. all logs by a specific user, specific process, or both (even irrespective of when they occurred)? If so, adding a couple extra nonclustered indexes on those fields could help you later on too. E.g. nonclustered index on processUser, processID and then a separate nonclustered index just on processID (the first index would be covering for predicates that only use processUser, or use processUser and processID which is why you don't need a third nonclustered index that's just on processUser here).