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.
Chad 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).
Brendan Tierney notes a new standard:
As of June 2023 the new standard for SQL had been released, by the International Organization for Standards. Although SQL language has been around since the 1970s, this is the 11th release or update to the SQL standard. The first version of the standard was back in 1986 and the base-level standard for all databases was released in 1992 and referred to as SQL-92. That’s more than 30 years ago, and some databases still don’t contain all the base features in that standard, although they aim to achieve this sometime in the future.
It’s a bit wacky to me that you have to pay for a copy of the SQL standard, but then again, it’s not really intended for regular people: it’s intended for companies developing new database products so they can adhere to the 70% of the standard that they want, outright ignore 20% of the standard, and replace 10% with their own incompatible versions.
Comments closedVitaly Bruk won’t be late to meetings anymore:
Today, I have late for the team meeting … 🙁
When I joined a Teams meeting, the team discussed ANSI_PADDING. One of the teammates fires at me with the teacher’s voice: “What are we talking about? What is ANSI_PADDING? Shoot!”.
That was a joke, but … just in case, let’s see what is this.
Read on to learn what the ANSI_PADDING attribute is and why it’s relevant.
Kevin Wilkie counts the months before his release:
Figuring out the number of years or months between 2 dates shouldn’t be hard. For some reason though, for a lot of database systems it is.
Kevin gives us answers in SQL Server and Snowflake, including a bonus bit of code to correct a problem that he spotted in the first bit of code. Date math is hard.
Comments closedKevin Wilkie min-maxes his way through life:
By the title of this post, you’re thinking “Ho hum. Sherpa will tell us about the stupid MIN and MAX functions in Snowflake and how great they are.”
Well, they are great. Let’s not kid ourselves about that one.
But Kevin pulls the rug out from under us, as this isn’t a post about MIN() and MAX().
Kevin Wilkie is looking forward to the weekend:
Sometimes, you’ll be asked to show the next Friday after a given date in a report. Sometimes, it’s the next Monday. Let’s work thru how to do it in both SQL Server and Snowflake for comparison…
Read on for some techniques Kevin uses. I’d just as soon drop in a calendar table and use it to track the appropriate day.
Comments closedKevin Wilkie is waiting for the calendar to change:
In SQL Server, we’re used to finding the end of the month via a few different methods. We can always use the DateAdd and DateDiff functions to get our data – which sometimes takes a bit of work – or we can use the EOMonth function.
Read on to see what tools are available for Snowflake users.
Comments closedKevin Wilkie remains distinct:
Now, the more fun – “new-ish” – version of the DISTINCT keyword.
Let’s take two values – A and B. Let’s define A = 7 and B = 2.
Snowflake will allow you to ask if A IS DISTINCT FROM B. Thankfully, in this case, it is.
Click through to see how this works. Also note that this syntax is available in SQL Server 2022.
Comments closedSteven Sanderson checks two objects:
In the realm of programming, R is a widely-used language for statistical computing and data analysis. Within R, there exists a powerful function called
identical()that allows programmers to compare objects for exact equality. In this blog post, we will delve into the syntax and usage of theidentical()function, providing clear explanations and practical examples along the way.
You can also take a look at the documentation for this function to see a few more examples.
Comments closed