Perhaps a bit deeper than just a database option, I'd like to see some table options that would give NoSQL a run for their money when it comes to ETL and "Minimal Logging".
There are times where not only don't I care about such things as delayed durability, I also don't care at all about being able to "roll back" a transaction". Those times are when I'm importing files or copying certain data from one database to another and then updating the staging tables that I've loaded them into.
For example, we load a shedload of data from files every day. We load the files into staging tables (which are easily made to be minimally logged) and then we update those staging tables with other data including certain condition flags that identify the validity of the data, etc. Even in the SIMPLE Recovery Model, nothing about updates is minimally logged. This is ALL highly expendable data until we copy that newly validated data into the final permanent tables and yet we have to tolerate all of the problems associated with fully logged UPDATEs, SIMPLE Recovery Model or not. Those problems include but aren't limited to the extra amount of time it takes to write to the log file and the amount of space the log file requires.
MS made it so you can achieve minimal logging for inserts even with a Clustered Index in place and even if the table isn't empty. What I'd like to see them do (which would really help import and validation processes in expendable staging tables) is to make it so you can actually assign an option to a table saying this is a totally non-logged table and still be able to have CI, NCIs, and DRI (still super important for these tables) in place. I'd like it at the table level because then they could also make a modification to backups that would simply not include such tables instead of screwing up Point-In-Time restores because you happen to have even 1 byte of data stored in "Minimally Logged" fashion.
Yes... you CAN create a separate database for such things but UPDATEs (and the much lesser used DELETE) are still not minimally logged even in the Simple Recovery Model and, even with the use of Trace Flag 610 (which is built into 2016 so you don't have to set it), even Inserts into a table that has NCIs in place is NOT guaranteed in any way, shape or fashion.
Perhaps an even easier option would be the ability to create a File Group that you could tag as a NOT LOGGED File Group that could still use DRI to LOGGED file groups and would come up as a PreAllocated but empty space after a restart. It would be like a TempDB for each database but with no logging whatsoever. It would be the ultimate "Sandbox" for ETL and report table generation purposes.
Yep... I get around all of this with some T-SQL prestidigitation but life would be a whole lot easier (it's currently a royal PITA even with some helper procs in place) if you could assign a guaranteed minimally logged option to tables and actually have backups ignore such tables. And, no... Posh isn't the right tool for this... it's way to slow and it shouldn't be necessary anyway. Same goes for things like SSIS and other "applications").
It would also be nice if MS would make it easy to generate the code to build tables (even if limited to "just" non-partitioned monoliths) and to separately be able to generate the code for constraints and indexes in an easy "here's the name, go build the code for it" fashion. They do it in SSMS... they should be able to do it quite easily in T-SQL.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
When you put the right degree of spin on it, the number 318
is also a glyph that describes the nature of a DBAs job. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs