CODEWITHSUNDEEP
sql-server
JC008
JC008

Reputation: 53

Append only to table in SQL Server to record immutable events and improve overall performance?

I need to record immutable events in a SQL Server table. How can the following be achieved?

  1. Mark a table as append only
  2. Prevent edits on the table for everyone (similar to #1)
  3. Allow deletes on the table for specific users
  4. Not lock the table for appends
  5. Calculate a hash for a varchar 255 to be used as a secondary index
  6. Improve read, write and indexing performance

Are there performance benefits or any potential side effects to attempting to do this?

Note that the question is asked from a non sql guru perspective so some of the items might overlap.

Upvotes: 3

Views: 3739

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32170

  1. Mark a table as append only
  2. Prevent edits on the table for everyone (similar to #1)
  3. Allow deletes on the table for specific users

If you don't want to use application level security or if it's not appropriate because you'll be connecting to the DB directly rather than through a service, use SQL Server's security to do accomplish this.

Create a database role in the database for each type of user. Create an Append role, grant the role INSERT (and SELECT if it's suitable) permissions to the table. Create a Delete role, grant the role DELETE (and SELECT and INSERT if it's suitable) permissions to the table. Then, add the Logins to the server and the associated Users in the database, and assign the database roles to the created Users. The Logins should only be members of the public built-in role. Now the users are blocked by security.

There is no method to make a table actually append-only. Users with the db_owner role will always be able to update or delete from the table. You can create an INSTEAD OF UPDATE trigger, but a user in db_owner can always disable the trigger. You can't stop sysadmin logins or db_owner users from being able to UPDATE the table if they're malicious. They can just take the permissions you denied and disable the security measures you put in place.

  1. Not lock the table for appends

Ensure all indexes on the table are created with ALLOW_ROW_LOCKS = ON and/or ALLOW_PAGE_LOCKS = ON. That should eliminate almost all table locks since the query engine can use row level locking or page level locking instead.

Beyond that, you cannot wholly eliminate locking on an INSERT. Locks are how the database ensures atomicity and concurrency. That said, I can't think of a situation where multiple INSERT statements would cause a deadlock on their own, but it can happen once you combine DELETE statements.

  1. Calculate a hash for a varchar 255 to be used as a secondary index

You want to maintain your own index? Why does this matter? If it's a checksum generated by an external application, then I would expect the field to be just another data field in the table with an index in the database.

If you want the database to index your data in multiple ways, the correct way to do that is to create multiple indexes in the database based on the queries you will need to run.

If you want to duplicate the effect of an index by making a column and using a function in the RDBMS to populate it with values so you can search for it, then I suppose you can use CHECKSUM() or HASHBYTES(), but this strikes me as a questionable design that's likely to have performance issues.

Are you just saying you want to create a surrogate key of some kind?

  1. Improve read, write and indexing performance

There are literally hundreds of technical manuals written on this topic. There are consultants and experts who get paid very large salaries just to answer this question all day every day. It is too broad. It depends on your server (memory, disk, CPU), your network, your application, the amount of data you generate, the amount of data you store, how time-sensitive the data needs to be, the number of concurrent users, how you will insert the data, how you will query the data, etc.

It's like asking a bridge engineer, "How can I be sure the bridge I want to build won't fall down?"

"The best way is to become a bridge engineer."

This site can help with specific instances of performance issues.

Upvotes: 8

Related Questions