A Fine Slice Of SQL Server
Chad Callihan shows off one of the best window functions:
The LAG function in SQL Server allows you to work with a row of data as well as the previous row of data in a data set. When would that ever be useful? If you’re a sports fan, you’re familiar with this concept whether you realize it or not. Let’s look at an example.
LAG() is outstanding for business reports, such as if you want three-month trailing data.
Wolfgang Strasser looks at a recent update:
Another small but very powerful usability extension in Azure Synapse Studio was added at the beginning of June: Move artifacts across folders in Synapse Studio (without extra clicks but with drag&drop)
Once again, the release notes list contained the short sentence that made me curious… hmm… that sound nice… In one of my previous post, I described the “old” way of moving artifacts around in Synapse Studio.
Click through for a demonstration.
Comments closedAaron Bertrand recaps some recent installation issues:
I’ve created countless Docker containers running SQL Server since I first wrote about it back in 2016, but I recently had my first foray into configuring SQL Server 2019 on a real live Linux machine.
It did not go as smoothly as I expected, so I wanted to share the solution to a particular problem I haven’t seen described elsewhere.
First, let me retrace my steps.
Click through for a summary of the issues.
Comments closedKenneth Fisher keeps the receipts:
In my last post I talked about reasons why your permissions might go missing. One of the reasons, and in my experience, one of the more unusual reasons, is that a command was run that changed the permissions. SQL doesn’t natively log these. Well, technically it does. They can be found in the default trace. But I don’t generally consider that to be terribly useful because on a busy server what’s available in the default trace may not last long. Regardless I ended up creating a DDL trigger to collect any database level security commands run. There are other ways to do this but for various reasons I decided to go with a trigger. Primarily because I could create one piece of code and cover every database in the instance.
Read on to learn how to create an appropriate table and a trigger to log that data.
Comments closedEric Rouach shows off a pair of things:
Using as an example the AdventureWorks2014 database, the first script describes the process of encrypting the “CardNumber” column from the Sales.CreditCard table while keeping the data decryptable.
Our pre-requisite is the creation of a Master Key, a Certificate and a Symmetric Key.
Once having those created, we may proceed to the addition of a new column called “CardNumberEnc” (where the suffix “Enc” stands for “Encrypted”). This column has a VARBINARY(250) Data Type and is nullable.
Read on for an example of using column-level encryption, followed by how you’d decrypt the data. Then, Eric discusses hashing, though I disagree with the nomenclature of “encryption and make the data non-decryptable.” The reason is that encryption is, by its nature, a two-way process and necessarily requires the ability to decrypt. Hashing, meanwhile, is a one-way process without a direct means of reversal. Nomenclature aside, the examples are good and I appreciate Eric using one of the larger SHA2 hashing algorithms rather than MD5.
Comments closedHello! I’m very slowly working on writing a zine about debugging, so I asked on Twitter the other day:
If you’ve run into a bug where it felt “impossible” to understand what was happening – what made it feel that way?
Of course, bugs always happen for logical reasons, but I’ve definitely run into bugs that felt like they might be impossible for me to understand (until I figured them out!)
I got about 400 responses, which I’ll try to summarize here. I’m not going to talk about how to deal with these various kinds of “impossible” bugs in this post, I’ll just try to classify them.
Click through for the major categories, as well as explanations and sub-categories. I think an interesting follow-up to this is to ask why we find ourselves in situations where we get these sorts of bugs and what (if anything) we can do to minimize or eliminate the likelihood of their appearance.
Comments closedHolger von Jouanne-Diedrich shows how many ways there are to solve a problem of squares:
This time we want to solve the following simple task with R: Take the numbers 1 to 100, square them, and add all the even numbers while subtracting the odd ones!
If you want to see how to do that in at least seven different ways in R, read on!
There are many different solutions possible, making use of several aspects of the R language. So this blog post can be seen as a fun exercise to recap some of the concepts explained in our introduction to R: Learning R: The Ultimate Introduction (incl. Machine Learning!).
Give it a try and then check out the variety of solutions.
Comments closedLaura Ellis explains the benefits of pre-sketching data visualizations:
When you take on a new data visualization project, it can be tempting to jump in and create visualizations right away with the idea that after enough exploring, the final format will present itself to you. And while it is important to dedicate time to EDA (exploratory data analysis), it can also be very beneficial to define a high-level plan early in the process.
Over time, I’ve found that producing an early sketch has been helpful in reducing the total amount of time and iterations taken towards building the end product.
Read on for the reasons why.
Comments closedHasan Savran introduces us to the Cosmos DB Dedicated Gateway:
Cosmos DB team announced a new way named Dedicated Gateway to connect to Azure Cosmos DB. As you might know there is already a standard gateway to connect to Cosmos DB. Dedicated or Standard gateway means that there is a computer stays between Cosmos DB replica set and your application. Your application request goes to gateway server then goes to Cosmos DB database. The biggest difference between Standard Gateway and Dedicated Gateway is, you do not share the dedicated gateway server with other Cosmos DB customers.
Dedicated Gateway is totally yours and you are responsible for its costs. Depending on your application size, you can select different size of gateway servers.
Read on to learn how expensive it is and the benefits it brings.
Comments closedDinesh Asanka hits on one of the under-utilized grouping operators:
You will see that data is aggregated for the columns provided by the GROUP BY clause. Important to note that the data will not be ordered in the GROUP BY columns and you need to explicitly order them by using the ORDER BY clause as shown in the above query.
In the above query, if you wish to find the total for Australia only, you need to run another GROUP BY with EnglishCountryRegionName and perform a UNION ALL. This will be a very ugly method. By using GROUP BY ROLLUP you can achieve the above-said task as shown in the following query.
If I were to rank grouping operators by how frequently I use them, it’s GROUPING SETS by a country mile, then ROLLUP, and almost never do I use CUBE.