THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

T-SQL Tuesday #86 My all-time favorite personal SQL Server Enhancement Request: Automatically maintained columns

T-SQL TuesdayI have heard the name T-SQL Tuesday for (like,) ever; but I have always been so into my own bundle of insanity that never took the time to figure out what it is. This changes today because I read a blog on Adam Machanic's (@adammachanic) blog that outlined the rules (here) and I have been keen to get back to blogging regularly.

When I saw what the topic was this month, hosted by Brent Ozar, I was even more excited. SQL Server Bugs & Enhancement Requests. Over the years, I have posted a lot of enhancement requests on the connect site, most of them in and around the SQL Server 2005 release. I was super enthusiastic about my requests, and made quite a few. I knew that most would fail, but one has stuck with me for years that I wanted to get into the product.

Automatically maintained columns. https://connect.microsoft.com/SQLServer/feedback/details/203570/add-automatically-maintained-columns-perhaps-just-update-date-update-user

The concept is very similar to a DEFAULT constraint, with two differences:

1. Will work on an UPDATE operation, without specifying DEFAULT

2. Could be configured to disallow the user from entering a value. My proposed syntax was pretty simple:

AUTO [WITH OVERRIDE] (scalar expression)

Now I realize that 10 years ago, I didn't take terribly long to consider that WITH was a terrible thing to add to the syntax, and AUTO is a keyword already, so I am going to rename it: AUTO_DEFAULT (scalar expression, [option]). Since I have thought a bit more about this in the years since writing it, I realized there were a few more options that would be nice. I was terrible in college doing syntax parsing, but the syntax itself is not important. Temporal in SQL Server 2016 has syntax that is similar to this for the new temporal columns which I got really excited about the first time I saw it: SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL. Maybe in vNext?

Possible options could include :

  • ALLOW_OVERRIDE -- let user input override scalar expression.
  • INSERT_ONLY -- only works for the insert operation. This makes it a kind of super default, and protects the column from future changes.

It could be implemented as a constraint, or like dynamic data masking, just be a part of the table structure and syntax. I will leave that ambiguous as it isn't important.

The major value of the feature would be to eliminate trigger use for the very typical need to automatically set a value for a column. It would also allow (as Rob Farley noted back in 2009) the OUTPUT clause to work when this is the only need you have for a trigger, and SCOPE_IDENTITY would not be affected like it is with an instead of trigger. (Allowing SCOPE_IDENTITY to work with an instead of trigger is also high on my list of fixes).

As an example, consider a table such as the following:

CREATE TABLE dbo.Test
(
     TestId int NOT NULL CONSTRAINT PKTest PRIMARY KEY, --assume a SEQUENCE object name Test_SEQUENCE existed, the value should be always the next value
     Value int NOT NULL,
     RowCreateTime datetime2(0) NOT NULL, --should always be set on INSERT, and not modifiable on UPDATE
     RowModifiedTime datetime2(0) NOT NULL, --should always be the server time when the row was modified
     RowModifiedUserName nvarchar(128) NOT NULL --should be the original login of the user who modified the row, or whatever the user wants it to be
)

The proposed syntax to fill the need would be:

CREATE TABLE dbo.Test
(
     TestId int NOT NULL CONSTRAINT PKTest PRIMARY KEY AUTO_DEFAULT (NEXT VALUE FOR dbo.TestSEQUENCE, INSERT_ONLY), 
     Value int NOT NULL,
     RowModifiedTime datetime2(0) NOT NULL AUTO_DEFAULT(SYSDATETIME()), 
     RowModifiedUserName nvarchar(128) NOT NULL AUTO_DEFAULT(ORIGINAL_LOGIN(), ALLOW_OVERRIDE)  

)

Now, if the user executed:

INSERT INTO dbo.Test(TestId, Value, RowModifiedTime)
VALUES (-1000, 1, '1900-01-01')

I would either like an error to occur, saying TestId and RowModifiedTime should not be modified in a DML statement, or just override their desires. RowModifiedUserName is acceptable to modify, since it allows override:

TestId     Value RowModifiedTime     RowModifiedUserName
---------- ----- ------------------- -------------------
         1     1 2017-01-10 16:09:03 domain\louis

Now, if I execute the following:

UPDATE dbo.Test
SET    Value = 2,
       RowModifiedUserName = 'Fred'
WHERE  TestId = 1;

The data should change to:

TestId     Value RowModifiedTime     RowModifiedUserName
---------- ----- ------------------- -------------------
         1     2 2017-01-10 16:15:23 Fred

Now, if I execute the following:

UPDATE dbo.Test
SET    Value = 3,
WHERE  TestId = 1;

The data should change to:

TestId     Value RowModifiedTime     RowModifiedUserName
---------- ----- ------------------- -------------------
         1     3 2017-01-10 16:19:25 domain\louis

It is definitely possible to write triggers to make this happen, but it is certainly a tedious task that definitely feels like a DDL configuration that would save thousands of lines of trigger code from needing to be written when you need to have a value that cannot be overwritten by the user (or in my case, where you don't trust developers, even yourself, to always get the values change for all operations without it automatically occurring.)

 
Published Tuesday, January 10, 2017 6:05 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

I re-submitted basically the same thing recently...

https://connect.microsoft.com/SQLServer/Feedback/Details/3105516

Can't let this one die!

January 10, 2017 7:27 PM
 

drsql said:

Agreed! This would fix so many problems with triggers or silly people using the clock from multiple clients.

January 17, 2017 8:50 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement