The ALL() function seems very simple on the surface however it has layers of complexity. In its most simple usage it is a function that simply returns a table (virtual or materialised). The syntax for ALL() is as follows
ALL() will always return a table, not a value. Because it is a table, you cannot put the result directly into a cell in a Pivot Table or a Matrix. Think about it, you can’t put a table with (potentially) multiple columns and (potentially) multiple rows into a single cell in a visual – it wont “fit”.
There’s a lot to ALL() and Matt does a great job explaining it.
I’d summarize the two “competing” curricula as follows:
- Base R first: teach syntax such as
[], loops and conditionals, data types (numeric, character, data frame, matrix), and built-in functions like
tapply. Possibly follow up by introducing dplyr or data.table as alternatives.
- Tidyverse first: Start from scratch with the dplyr package for manipulating a data frame, and introduce others like ggplot2, tidyr and purrr shortly afterwards. Introduce the
%>%operator from magrittr immediately, but skip syntax like
$or leave them for late in the course. Keep a single-minded focus on data frames.
I’ve come to strongly prefer the “tidyverse first” educational approach. This isn’t a trivial decision, and this post is my attempt to summarize my opinions and arguments for this position. Overall, they mirror my opinions about ggplot2: packages like dplyr and tidyr are not “advanced”; they’re suitable as a first introduction to R.
I think this is the better position of the two, particularly for people who already have some experience with languages like SQL.
This query succeeds but returns results we don’t really want:
This brings back all 9 records tied to products 1 and 2 (because product 3 didn’t exist on July 2nd at 8 AM UTC). But it gives us the same start and end date, so that’s not right. What I really want to do is replace
DatePredictionMade, so let’s try that:
This returns a syntax error. It would appear that at the time
FOR SYSTEM_TIMEis resolved,
QuantitySoldPredictiondoes not yet exist. This stops us dead in our tracks.
This is one of the two things I’d really like to change about temporal tables; the other thing (now that auto-retention is slated for release) is the ability to backfill data without turning off system versioning.
Sometimes we want to apply a filter to an array or other collection of objects, but keep both the items that pass the filter and those that fail it. Instead of cycling twice through the collection, there’s a one-step method.
Instant File Initialization is a privilege assigned in the local security policy. Here’s some explanation by MSSQL Tiger Team.
There’s a lot to tell about it, but I’m not going to do that here. Let’s just assume it’s a good thing to assign that privilege to the account with which the SQL Service runs.
Klaas explains how to use Powershell filtering with Where-Object and the Where method for people new to Powershell, and then uses this to figure out if IFI is enabled.
Performance improvements for Prepared Statements
Improved performance for Prepared Statements through caching (including prepared statement handle re-use). This behavior can be tuned using new properties to fit your application’s needs.
Azure Active Directory (AAD) support for Linux
Connect your Linux applications to Azure SQL Database using AAD authentication via username/password and access token methods.
Federal Information Processing Standard (FIPS) enabled Java virtual machines
The JDBC Driver can now be used on Java virtual machines (JVMs) that run in FIPS 140 compliance mode to meet federal standards and compliance.
Click through for more information, including a couple interesting features like additional timeouts you can set.
Last week in Part Two I went through how to create named volumes and map them to containers in order to persist data.
However, there is also another option available in the form of data volume containers.
The idea here is that create we a volume in a container and then mount that volume into another container. Confused? Yeah, me too but best to learn by doing so let’s run through how to use these things now.
Read through for the step-by-step description.
But as you can see from the marketplace page there, by default this plugin doesn’t know PowerShell. In my user settings file
settings.json, I added PowerShell to the
cSpell.enabledLanguageIdssection so it’s always recognized:
And with that, VSCode was giving me green squiggles under lots of words – both misspelled and not. Code Spellchecker doesn’t understand PowerShell in its default setup, it doesn’t have a dictionary for it. Just to get things started, I added a
cSpell.userWordssection to my
settings.jsonand the squiggles started disappearing.
It’s an interesting post, so read the whole thing.
The following solutions were tested:
- Graphing with Grafana and Graphite
- Collection with collectd and Telegraf
- Storage with Graphite/Whisper and InfluxDB
We landed on a solution which uses InfluxDB, collectd and Grafana. InfluxDB gave us the performance and flexibility we needed, collectd is a light weight tool to collect system performance information, and Grafana is a rich and interactive tool for visualizing the data.
In the sections below, we will provide you with all the steps necessary to setup this same solution in your environment quickly and easily. Details include step-by-step setup and configuration instructions, along with a pointer to the complete GitHub project.
I’ve been a big fan of Grafana since Hortonworks introduced it as the primary monitoring tool in HDP 2.5. We use Grafana extensively for monitoring SQL on Windows and SQL on Linux.
Windows has the same setting. It’s in the Power Options under Control Panel, and for all servers, no matter what, it should be set to High Performance.
Here’s a free T-SQL script I wrote that will check for you what the power settings are. We don’t always have desktop access to a server when we are checking diagnostics, but it’s good to know if performance problems can be addressed by a really simple fix that doesn’t require the vehicle to be at rest.
(The script also respects your settings, so if you had
xp_cmdshelldisabled, it’ll turn it off again when it’s done.)
Click through for the script.