Log table without primary key

Question

I have a SQL Server log table that includes a column "Id" This is an identity column, but not a primary key, it is not even indexed.

This would have just been set up from some tutorial for adding DB logging, and I'm not sure if the PK was just missed or intentionally left out.

Given its logging all concern is to write performance, not read.

I would have thought if its an identity column it would have to be checking the data anyway to do an insert at correct numbers, should this be set up as a pk for that reason?

Is there any benefit to leaving it with no pk?

Answer 1

Give it a PK. This helps in multiple aspects of data management, not just logical.

For example, suppose at some point later you decided to ship this data somewhere and whatever gatekeeper requires and checks that your table has a PK (does not know it is IDENTITY), you would have to scramble and that is not fun.

Also, in the replication world heaps are not fun, whether using MS tools or some others.

Answer 2

First of all, PK is not Clustered Index, although in SSMS it seems so.

In short, clustered index should be built.

To insert rows in the heap also need read IO, which must search IAM(Index Allocation Map) to locate a free page to store the new rows. With clustered index, locate store page is more faster than searching IAM. The cost of maintaining a clustered index is worth it to gain write performance, and in your case, you don't even need to defragment the index.

It only takes up very little more space than the heap. But there are a lot of benefits, such as faster reads, logs will also need to be searched at some point in the future.

Answer 3

I would have thought if its an identity column it would have to be checking the data anyway to do an insert at correct numbers, should this be set up as a pk for that reason?

No, that is not how identity (or sequence) work. SQL Server has a "counter" materialized that it reads at startup/when it is needed first and then it has a number of values in memory that can be consumed. I.e., identity doesn't gurarantee uniqueness, if you for instance re-seed it, or if SQL Server looses track (which sometimes happened many versions ago).

In addition to the other reasons to have this as a PK/unique index is that you might want to have that index a clustered index (which it is by default for a PK). This way you don't have a heap. Here I'm particularly addressing when you might want to delete old data. And please tell me you do have a plan for this! When you mass-delete data from a heap, you might get an execution plan that results in the storage not being deallocated. SQL Server will happily scan all this dead storage if/when you query the table and it will keep growing endlessly. Whenever you see a table which is "bigger then it should be", you always want to check if it is a heap.

Answer 4

The additional cost of INSERT here is trivial, unless you have high enough insert volumes to hit hot page latch contention, which only starts to kick in around ~10,000 rows/sec, and since it's just logging, I'm not sure you would care.

And the real reason you want a clustered index is to run queries like

select top 1000 * from MyLogTable order by ID desc

so you can see recent log entries without a full table scan.

I have a techinque here: https://stackoverflow.com/questions/51459805/generate-a-unique-time-based-id-on-a-table-in-sql-server/51460731#51460731

for using a "smart" datetime2 as a unique clustered index key, which would allow you to do efficient date range searching with a single index.

Answer 5

The main benefits I can think of of using a table as a heap in SQL Server, for example in your case a table with an identity column and no primary key and assume another indexes:

1. Faster Insert Operations: Since heaps do not enforce any order on the data, SQL Server can insert rows more quickly compared to tables with clustered indexes

2. Reduce Storage Overhead: Heaps can use less storage space because they do not maintain the additional structures required by clustered indexes

3. Flexibility with Nonclustered Indexes: If the table is frequently accessed through nonclustered indexes, heaps can be efficient. The row identifier used in heaps is smaller compared to a clustered index key, this can reduce the storage overhead