Wanted: Automatic Columns

Louis Davidson plucks an old Connect item out for a new look:

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?

Read the whole thing.  Then check out the related Connect item Adam Machanic submitted.  I’d love to see that functionality, given how frequently I create these metadata columns.

Related Posts

Analysis Of A Failed Project

Eugene Meidinger looks back at a big project which fell apart: So the first issue was that the software was built in-house by another company in the same industry. Imagine, for example, if a large bakery had created an ERP system and another large bakery wanted to move to that system. Sounds great, right? Well, […]

Read More

What’s In SQL Server 2019 CTP 2.0?

Aaron Bertrand gives us the highlights: Certificate Management in Config Manager View and validate all of your certificates from a single interface, and manage and deploy certificate changes across all of the replicas in an Availability Group or all of the nodes in a Failover Cluster Instance. Built-in data classification A new ADD SENSITIVITY CLASSIFICATION statement helps you identify and […]

Read More


January 2017
« Dec Feb »