View constraints sqlpro for mssql11/7/2023 ![]() ![]() ![]() In either case, I've found the performance to be acceptable as long as the data is properly normalized and the tables are kept narrow. Newer versions will only perform one seek, returning the most recent row with the TOP operator. No additional constraints/triggers/functions are required.įor older versions of SQL Server (I believe 2014 and earlier) the above query will result in two seeks against TestEntityVersion (although the data is usually one read once from disk). The primary key guarantees that one one row will be returned. To get a full picture of the entity as of a point in time, we would use the following query: SELECT ,CONSTRAINT PK_TestEntityVersion PRIMARY KEY (ColumnA, ColumnB, tt_start) ,CONSTRAINT FK_TestEntityVersion_VersionOf_TestEntity FOREIGN KEY (ColumnA, ColumnB) REFERENCES TestEntity (ColumnA, ColumnB) ,CONSTRAINT PK_TestEntity PRIMARY KEY (ColumnA, ColumnB) * Any immutable columns would go here */ ![]() Working from this pattern we would set things up like so: /* Need an entity to maintain the parent key for any time-independent relationships */ Unless we are defining the duration of a contract or true interval, it is not needed 2 and requires a good deal of transactional logic to ensure invalid rows are not inserted.įor your case, it looks like columns A and B form a composite primary key. It's a misconception that in order to query and maintain the integrity of time-dependent data that we must have a close/end date 1. To enforce this, we only need the point in time where the row was changed/will become effective. How could a similar constraint or trigger be then implemented? Or could the "temporal tables" feature be a replacement for what I am trying to do?įor a point in time, only one row can be valid. However, as far as I see, MSSQL Server doesn't implement DEFERRABLE INITIALLY DEFERRED. This would at the same time also increase the performance of multiple INSERTs. In that way, the constraints would be checked after all operations have finished. That is why Snodgrass writes that the constraints/assertions (or here the trigger) have to be DEFERRABLE INITIALLY DEFERRED. This doesn't work with this trigger, because the trigger would start already after the INSERT and fail, although the operation would perfectly work out if the trigger would start after the UPDATE. Furthermore, many operations like updates require more than one operation (e.g. Therefore, using multiple INSERTs leads to long waiting times. The trigger is pretty slow on larger tables. RAISERROR('Transaction violates sequenced constraint', 1, 2) IF (( EXISTS ( SELECT * FROM test_table AS b1ī1.tt_start < b2.tt_end AND b2.tt_start < b1.tt_end) ))) I am trying to implement transaction time tables on a MSSQL Server 2016 following the book by Richard Snodgrass ( Developing Time-Oriented Database Applications in SQL).Ī table with a sequenced primary key through a trigger could be implemented like this: CREATE TABLE test_table (ĬREATE TRIGGER Seq_Primary_Key_tt ON test_table FOR INSERT, UPDATE, DELETE AS ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |