A Fine Slice Of SQL Server
Henrietta Dombrovskaya shows us an execution plan in Postgres:
In the last blog (When PostgreSQL Parameter Tuning is not the Answer), we compared several execution plans for a SQL statement as we made changes to parameters and indexes. Still, there was no mention of what an execution plan is, how one can obtain an execution plan for a query, and how to interpret the result. In this blog, we will take a deep dive into this topic.
Read on for the full article.
Comments closedStephane Laurent writes some code:
I implemented the framed closed curves exposed in this blog post, in Julia and R. In fact it is useless with R, because the rgl function
cylinder3dis faster and better.Here is the Julia implementation:
Click through for that code, as well as equivalent R code. H/T R-Bloggers
Comments closedSteven Sanderson explains the value of the str() function:
In a nutshell,
str()stands for “structure” and offers a concise summary of the structure of an R object. It presents essential details about the object, including its data type, dimensions, and the first few values. By providing an overview of your data,str()allows you to grasp the fundamentals at a glance and proceed with a clearer understanding of what you’re working with.
str() is a really useful function and people who develop objects in R thoughtfully can pack a lot of useful data into the one call.
For 20+ years of database and application development, time-series data has always been at the heart of the products I work with. Inevitably, as the quantity of data grew over time, management became more difficult and query performance suffered. Over the years, the primary method for managing this growth in data effectively would be to partition it. The problem is, until recently, partitioning wasn’t easy to setup in most OLTP databases like PostgreSQL or SQL Server.
Fortunately, PostgreSQL has significantly improved its ability to partition large data tables over the last 6 years, starting with PostgreSQL 10.
Read on for Ryan’s recommendations around partitioning and a few thoughts on sharding.
Comments closedChris Webb doesn’t want query folding limits:
A few months ago a new option was added to the Sql.Database and Sql.Databases functions in Power Query in Power BI and Excel which allows Power Query queries that combine data from different SQL Server databases to fold. Here’s a simple example showing how to use it.
Read on for the setup and the demonstration. This is pretty nice for cases where you have user data in one database and reference data in another, or you’re trying to join together data from two separate systems.
Comments closedMichael Christofides defines terms:
It has been possible to do partitioning in PostgreSQL for quite a while — splitting what is logically one large table into smaller physical tables. Since version 10, a huge leap was made with the introduction of declarative partitioning, and more improvements have come every year since.
Sharding is a different story — splitting what is logically one large database into smaller physical databases. The primary tool for this in the PostgreSQL ecosystem is the Citus extension. But you can also handle the sharding logic at the application level, as recent posts from the likes of Notion and Figma have described. Somewhat confusingly, some forms of sharding are sometimes referred to as vertical partitioning, including by the team at Figma.
Read on for a few thoughts on when to perform each action and what the costs and benefits are.
Comments closedRebecca O’Connor builds a map:
Power BI can leverage Geospatial data from snowflake with my favourite map visual – Iconmap – https://www.icon-map.com/ . Icon map can render points, polygons and linestrings using ‘Well Known Text’ format (WKT).
Snowflake supports converting geospatial datatypes to WKT. Not only this, Snowflake has the capabilities to perform the Engineering and analytical needs for Geospatial analysis without using any other tool. And the results can be visualised in a variety of medias such as Tableau, Hex, Carto or even a Custom built Streamlit application. I have written a Streamlit blog on this very recently.
Click through for information on how to get the data shaped in a way that Power BI likes.
Comments closedWolfgang Strasser takes screenshots:
In order to access the new version of the Purview portal, you need to migrate/upgrade you existing Purview account to the new, one account per tenant model. You can find more information about that process in the documentation (https://learn.microsoft.com/en-us/purview/account-upgrades)
Wolfgang shows off what you get if you do make this migration or create a new account.
Comments closedErik Darling talks up a famous Canadian:
I know there’s been a lot of blog content about the creation of and updates to my stored procedures lately. I try to consolidate as much as possible, but this one comes from Valued Contributor©.
The one, the only, the Canadian: Michael J Swart (b|t) recently contributed a really cool piece of code that I’ve been dying to have since I created sp_HumanEventsBlockViewer: the entire blocking chain is visualized in the results.
Click through to see what it looks like, and next time you see Michael, give him a thumbs up.
Comments closedDeepthi Goguri makes a connection:
Applicable to-
SQL Server 2022 on-prem on Windows and Linux and SQL Server 2022 hosted on Windows Virtual Machines.
Once you install the SQL Server, there are three different authentication methods that you can use to connect SQL Server along with the Windows and SQL Server authentication. They are –
- Azure Active Directory Universal with Multi-Factor Authentication
- Azure Active Directory Password
- Azure Active Directory Integrated
Read on for the pre-requisites as well as a detailed guide on how to set everything up.
Comments closed