A Fine Slice Of SQL Server
Jeff Mlakar uses the STRING_AGG() function:
Often when generating dynamic sql we must add some separator values in the string(s) we build. A function delivered way back in 2017 called STRING_AGG is a simple way to do this. Let’s look at some examples.
There are a lot of people who have never heard of the function and don’t know that there’s a better alternative to the combination of STUFF() and FOR XML PATH to concatenate together strings.
Kelly Broekstra digs into a story:
Are you a model designer or BI developer tasked with building a data model and/or report from a series of user requirements or Agile User Stories? Do you know where to start, or what to try first? Here are some practical tips and techniques that I have used to design a great report model from Agile user stories.
Read on for questions to ask and how to translate that into a star schema model.
Comments closedKendra Little resolves an error:
Yesterday, I was writing some Transact SQL to dust off the cobwebs. I got confused when I was playing around with the STRING_SPLIT function, and kept getting the error:
Msg 195, Level 15, State 10, Line 2 ‘STRING_SPLIT’ is not a recognized built-in function name.
When I saw this, I immediately thought compatibility level, as that’s something I’ve run into several times before. In this case, it turns out that the correct answer was something else.
Comments closedMaelle Salmon shows off a few interesting functions:
How does this package depend on this other package?
pak::pkg_deps_explain()The pak package by Gábor Csárdi makes installing packages easier. If I need to start working on a package, I clone it, then run
pak::pak()to install and update its dependencies. It’s a “convenience function” that is convenient for sure! Bye byeremotes::install_deps().
Read on for an example of this, as well as details on two other functions in different packages. H/T R-Bloggers.
Comments closedSteven Sanderson shows two packages for building heatmaps in R:
Data visualization is a powerful tool for understanding the relationships between variables in a dataset. One of the most common and insightful ways to visualize correlations is through heatmaps. In this blog post, we’ll dive into the world of correlation heatmaps using R, using the
mtcarsandirisdatasets as examples. By the end of this post, you’ll be equipped to create informative correlation heatmaps on your own.
Read on to see how to build heatmaps with the corrplot and ggcorrplot packages.
Steve Jones stands by for station identification:
Sometimes you want to restore part of your data, but you still want the option to continue restores. A classic example of this is when you are restoring a number of transaction logs and want to check the data to find a place where certain values haven’t been changed.
Suppose someone deletes a bunch of data between 10am and 11am from the supplier table. You know that they added “Acme” to this table before the delete. You might restore up to 10am and check the supplier table for the old data and look for Acme. If it’s not there, maybe you restore the 10:05am log backup and check again. If it’s not there, then the 10:10am log, etc.
Click through to see how you can do that.
Comments closedTransactions, like any other database, are a key component of PostgreSQL. A transaction is a sequence of one or more database operations that are executed as a single unit of work. These operations can be queries (e.g. SELECT, INSERT, UPDATE and DELETE) that modify the database’s state.
A transaction’s main purpose is to combine multiple statements into an atomic, all-or-nothing process. It ensures that either all operations within a transaction are fully completed, or none of them are executed at all. Concurrent transactions cannot see each other’s unfinished changes. Updates from ongoing transactions remain hidden until completion, at which point all changes become visible simultaneously.
This is very similar to SQL Server, except their savepoints actually work they way they’re supposed to.
Comments closedKevin Chant can’t stop at one:
In this post I want to cover working with Microsoft Fabric Git integration and multiple workspaces. By highlighting one method that you can use in the real-world.
I must admit that I have been very keen to test this particular way of working with Microsoft Fabric Git integration and multiple workspaces.
By the end of this post, you will know one way that you can work with Microsoft Fabric Git integration and multiple workspaces. Based on real-world working practices. Including multiple branches and pull requests.
Click through to see what Kevin has in mind usingg Azure DevOps.
Comments closedGilbert Quevauvilliers needs a place to store this data:
Following on in my series, in this blog post I am going to use the dataflow Gen2 in Microsoft Fabric to load the data into a lake house table.
By doing this, it will allow me to store the data in a delta lake table.
In this series I am going to show you all the steps I did to have the successful outcome I had with my client.
Click through for links to the first two parts of the series, as well as a step-by-step guide for part 3.
Comments closedChad Callihan is distinguished:
One feature introduced with SQL Server 2022 that I’ve recently been playing around with is IS [NOT] DISTINCT FROM. This new feature can help when it comes to dealing with NULL value comparisons.
Read on for examples. Do note that x IS NOT DISTINCT FROM y does not provide a performance benefit over its equivalent of x=y OR (x IS NULL AND y IS NULL).