More each day I find myself newly empowered by your videos selectively drawing on your valuable content. Just this morning I heard myself saying “screw you sql server” and smiled with you in mind. Days just wouldn’t be the same without your videos. Someone please hire this guy.
Well Damn, that's an interesting nugget of information. We use a LastUpdate Column all the time with a default constraint, but of course the hole in that is an UPDATE without an explicit update to said column. And I'm embarrassed to admit we've been using temporal tables for probably 5 years now and I never ran across this temporal column usage without a temporal table.
One of these days, I'm going to need to figure out how to read the SQL Server documentation from cover to cover. There must be some sort of reading order I can use. You NEVER see features like these talked about anywhere else. I've read many SQL books and this hasn't been mentioned even once!
Hey Erik - EXCELLENT video. I've been doing home-grown CDC incorrectly for a LONG time :) I have a couple of questions. I kinda do what you are with some subtle differences: * Why are you using [datetime2] vs [datetime] (higher precision)? * I've always used update triggers and you've accomplished the same without them. What triggers the [LastModified] column update without a trigger. * You didn't really talk about the need for [JunkDate] and its reference by the PERIOD FOR clause. Not sure what that does (I guess I could read the documentation but it's easier to ask you :) ). If I can accomplish my CDC without triggers, that would be AWESOME!!! Thanks again for this video. I'm sure I'm going to start refactoring my CDC stuff thanks to you
1. No particular reason other than some system functions use it, and the additional granularity is nice sometimes. Probably overkill here though. 2. Temporal versioning handles this internally 3. I do talk about this in the video, give it another watch. Temporal rows need both.
Pretty cool way to keep a value updated without a trigger, but since the column is locked for adhoc updates, as soon as I implement it, someone will ask that they need to update it to a different value than what is generated.
Well, you shouldn't allow that anyway if it's being used for auditing. And of course, if you need it to be newer, just do a simple update to roll the date forward.
@@ErikDarlingData we delivered a government project - using notification services back when it was released - I worked with Christian Bolton (MS then) but stayed away from it (NS) on pain of death. The DB’s were on my 3 Node Cluster!! I miss Clusters - AG’s have overheads - some have been addressed in 22 but yet to play with that but still prefer clusters 😀
More each day I find myself newly empowered by your videos selectively drawing on your valuable content. Just this morning I heard myself saying “screw you sql server” and smiled with you in mind. Days just wouldn’t be the same without your videos. Someone please hire this guy.
Aw, thank you!
agree 100%
@@mumk 😃
Well Damn, that's an interesting nugget of information. We use a LastUpdate Column all the time with a default constraint, but of course the hole in that is an UPDATE without an explicit update to said column. And I'm embarrassed to admit we've been using temporal tables for probably 5 years now and I never ran across this temporal column usage without a temporal table.
HAHAHA, well there you go. I hope you can use this in your journeys~~
One of these days, I'm going to need to figure out how to read the SQL Server documentation from cover to cover. There must be some sort of reading order I can use. You NEVER see features like these talked about anywhere else. I've read many SQL books and this hasn't been mentioned even once!
I think it's in T-SQL Querying (Itzik et al) but yeah, this isn't a feature that gets a lot of attention.
@@ErikDarlingData I don't think it's in that book, unless there's a second edition that I've not heard of.
Pretty neat. Didn’t realize you could hide a column or utilize partial temporal table features.
Yeah, not something that gets a lot of attention.
Hey Erik - EXCELLENT video. I've been doing home-grown CDC incorrectly for a LONG time :) I have a couple of questions. I kinda do what you are with some subtle differences:
* Why are you using [datetime2] vs [datetime] (higher precision)?
* I've always used update triggers and you've accomplished the same without them. What triggers the [LastModified] column update without a trigger.
* You didn't really talk about the need for [JunkDate] and its reference by the PERIOD FOR clause. Not sure what that does (I guess I could read the documentation but it's easier to ask you :) ).
If I can accomplish my CDC without triggers, that would be AWESOME!!! Thanks again for this video. I'm sure I'm going to start refactoring my CDC stuff thanks to you
1. No particular reason other than some system functions use it, and the additional granularity is nice sometimes. Probably overkill here though.
2. Temporal versioning handles this internally
3. I do talk about this in the video, give it another watch. Temporal rows need both.
@@ErikDarlingData Thanks, Erik... I need to research more about Temporal features in SS (new to me).
Pretty cool way to keep a value updated without a trigger, but since the column is locked for adhoc updates, as soon as I implement it, someone will ask that they need to update it to a different value than what is generated.
Well, you shouldn't allow that anyway if it's being used for auditing. And of course, if you need it to be newer, just do a simple update to roll the date forward.
I soft deleted my writing staff. Theyre still here just no longer in charge of writing bad sql jokes :|
Hopefully you can rebuild their confidence after the reorg.
That is an interesting topic. How about using rowversion/timestamp for that?
How about it?
@@ErikDarlingData It is an idea for the new article/video
Can you explain why would we prefer this method instead of create a trigger to update the lastModified date?
No big reasons. Not having to write a trigger or worry about someone disabling the trigger is nice I suppose.
is this version or compatibility specific - i could use this
SQL Server 2016 up, where temporal tables were introduced
In that top 5 of SQL Server features has to be Notification Services - even MS UK didn’t understand how it worked properly!!!
@@stephenlawrenson2380 Oof. You're not kidding.
@@ErikDarlingData we delivered a government project - using notification services back when it was released - I worked with Christian Bolton (MS then) but stayed away from it (NS) on pain of death. The DB’s were on my 3 Node Cluster!! I miss Clusters - AG’s have overheads - some have been addressed in 22 but yet to play with that but still prefer clusters 😀
@@stephenlawrenson2380 I very much prefer clusters as well.