Randolph West has another nice post on temporal tables, in which he lets the cat out of the bag:
Look at the log records. They are identical to before. In fact, because a trigger is called in the same implicit transaction as the
UPDATEstatement, we didn’t even need to wrap the
INSERTstatement in a transaction.
What have we learned?
Temporal tables are doing exactly what an update or delete trigger (or stored procedure) would do, based on what we saw in the log records.
This is an in-depth look at what, exactly, is happening when temporal tables get updated.
When I needed to do an rmarkdown repository for making R Consortium Infrastructure Proposals, I was able to take the opportunity to take Jan’s code and move forward with it so that the ISC proposal is always web-facing. Here’s how I did it:
She’s using this to build the satRday planning site.
PowerShell is an ideal tool for doing health-checks of a collection of SQL Server instances, and there are several examples around, but few acknowledge the fact that individual DBAs have their own priorities for tests, and need something easily changed to suit circumstances. Omid’s Healthcheck allows tests to be SQL or PowerShell and requires only adding, altering or deleting files in directories.
Grab the tool from his GitHub repo.
Click through for a hypnotizing animated GIF. Seriously, I needed to close the window after a couple of minutes because I kept watching it over and over and over.
Should I use WITH OVERRIDE?
That was an easy post. All done! Thanks for reading.
……Oh, right. Let’s talk about why. Books Online actually has the answer:
If that’s too too heavy of reading, you can spend the day on the Transformers Wiki. I mean, it is Friday, after all.
Step 3: Easily Stop SQL Server Service.
That is very possible you performed some very heavy and memory intense operation by your local SQL Server instance and it ate all your free memory. You do not need it anymore, but SQL Server won’t easily give memory back.
The easiest way to claim all your memory is to stop your SQL Server.
There are some easy ways doing it:
– First way us using SQL Server Management Studio. You just have to do a right click on your local SQL Server instance and choose “Stop”.
The use case for Slava’s advice is a scenario in which you have SQL Server installed on a local machine with very little RAM.
Working with table partitioning can be puzzling. Table partitioning isn’t always a slam dunk for performance: heavy testing is needed. But even getting started with the testing can be a bit tricky!
Here’s a (relatively) simple example that walks you through setting up a partitioned table, running a query, and checking if it was able to get partition elimination.
I would have snipped the tl;dr section but it was too long…
There were a lot of limitations when using DirectQuery in SSAS Tabular 2012/4, but for me the showstopper was the fact that it only worked if you were running DAX queries against your model. Historically the only major client tool that generated DAX queries to get data was Power View, and Power View was/is too limited for serious use, so that alone meant that none of my customers were interested in using DirectQuery. Although we now have Power BI Desktop and PowerBI.com, which also generate DAX queries, the fact remains that the vast majority of business users will still prefer to use Excel PivotTables as their primary client tool – and Excel PivotTables generate MDX queries. So, support for MDX queries in DirectQuery mode in SSAS 2016 means that Excel users will now be able to query a Tabular model in DirectQuery mode. This, plus the performance improvements made to the SQL generated in DirectQuery mode, means that it’s now a feature worth considering in scenarios where you have too much data for SSAS Tabular’s native in-memory engine to handle or where you need to see real-time results.
Good stuff. Read the whole post, especially if (unlike me) you know a thing or two about MDX.
Again, exactly the desired behaviour. The changes made in the outer procedure were committed, the changes in the inner procedure, the procedure where the error was thrown, were rolled back.
Used correctly, savepoints can be a powerful mechanism for managing transactions in SQL Server. Unfortunately they’re not well known and as such their use can also make code much harder for later developers to debug.
I’ve used conditional transactions fairly regularly (procedures can have calling parent procedures, or sometimes can be called on their own), but never savepoints.
I have released a tool that will do just that, if you grab the SSDT-Dev Pack at least version 1.1 fromhttps://github.com/GoEddie/SSDT-DevPack/tree/master/release this adds a new menu to the tools menu in visual studio to name constraints. What I like to do is to go to “tools->options–>keyboard” and map an unused short-cut to the command “Tools.NameConstraints”, I used “ctrl+k + ctrl+n” so I can open a table in SSDT and just do ctrl+k and then ctrl+n and it automatically re-writes any tables in the active document that have unnamed primary keys with an appropriate name.