A Fine Slice Of SQL Server
Steven Sanderson wants to check for row existence:
Hello, fellow R users! Today, we’re going to explore a common scenario you might encounter when working with data frames: checking if a row from one data frame exists in another. This is a handy skill that can help you compare datasets and verify data integrity.
There are several ways to do it. Click through for two of those ways.
Comments closedKurt Buhler performs comparative analytics:
In a previous article about format strings, we showed an example of how format strings can improve visualizations. The visualizations in that article compared the performance of a company’s marketing videos on a streaming platform to the average of all their videos released that year. In this article, we explain how to conduct this analysis yourself in DAX, where you compare series that occur in different periods.
Click through for an explanation, both in video and in blog form.
Comments closedSam Garth troubleshoots an issue:
On a recent case, a customer was trying to restore a database from a storage account using a SAS token when they received the below error.
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)Additional information:
Cannot open backup device
‘https://storage.blob.core.windows.net/container/dbbackup_2024_03_21_121901.bak‘
Operating system error 86(The specified network password is not correct.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
Read on for the troubleshooting steps Sam followed to solve the problem.
Comments closedCary Huang explains how row visibility works in Postgres:
Simply put, the visibility refers to whether a row of data (Heap Tuple by default) should be displayed to the user in certain states, backend processes, or transactions.
For example,
- A user changes a data record from ‘A’ to ‘B’ via UPDATE
- PostgreSQL handles this by INSERTing a new record call ‘B’ while marking record ‘A’ as ‘invisible’
- Both records exist in the system, but ‘B’ is visible, while ‘A’ is not.
- A deleted or invisible record is also referred as a ‘dead’ tuple
- One of the responsibilities of VACUUM process is to clear these ‘dead’ tuples to free up space.
Read on to learn more about various visibility indicators and how they ultimately tell us whether a row should be visible or not.
Comments closedRyan Booz explains how recursive common table expressions work:
The first two articles in this series demonstrated how PostgreSQL is a capable tool for ELT – taking raw input and transforming it into usable data for querying and analyzing. We used sample data from the Advent of Code 2023 to demonstrate some of the ELT techniques in PostgreSQL.
In the first article, we discussed functions and features of SQL in PostgreSQL that can be used together to transform and analyze data. In the second article, we introduced Common Table Expressions (CTE) as a method to build a transformation query one step at a time to improve readability and debugging.
In this final article, I’ll tackle one last feature of SQL that allows us to process data in a loop, referencing previous iterations to perform complex calculations: Recursive CTE’s.
Given that Postgres allows for materialized common table expressions, I’m a bit curious about how recursive common table expressions perform compared to SQL Server.
Comments closedNicky van Vroenhoven performs a search:
You probably also use the same method as I did to search through the Admin portal and tenant settings: CTRL + F from your browser. It does the trick, but not very well.
For example, it only searches the titles of the settings, not the descriptions.
Next to that, you also can get a lof matches that you have to scroll or loop through, which makes it not very clear because more often than not, you don’t know in what section of the tenant settings you ended up.
Read on for an alternative method of searching. Or, I guess, two of them because without Nicky’s post, it can be easy to confuse the two search boxes.
Comments closed