A Fine Slice Of SQL Server
Magda Bronowska rounds to the nearest minute:
From MS Learn:
DATETRUNC() function returns an input date truncated to a specified datepart.
On the surface the work similarly to
DATEPART(), however that function returns integer values, opposed to the dates returned byDATETRUNC()(we will see that better in the example below).
Read on for plenty of examples of this, as well as two more syntax updates in SQL Server 2022.
Comments closedThis month, Deepthi Goguri (@dbanuggets) asks us about our favourite new feature in SQL Server 2022 or Azure. And while there are always a few, I’m going to write about why I have a particular fondness of “IS [NOT] DISTINCT FROM“, despite the fact that it’s overly wordy and the functionality isn’t actually new at all.
People understand my point that it’s a little wordy. Typing “IS NOT DISTINCT FROM” instead of “=” doesn’t sound fun to anyone, and I think “==” or “IS” ought to be fine. The fact that the functionality isn’t new… well that statement seems to raise a few eyebrows.
Read on for Rob’s take on what IS DISTINCT FROM (and its negative cousin) actually do and what performance-killing alternative people used prior to that.
Lukas Eder hits on an interesting data point:
I’ve found an interesting question on Twitter, recently. Is there any performance impact of using
FILTERin SQL (PostgreSQL, specifically), or is it just syntax sugar for aCASEexpression in an aggregate function?
Click through for the answer and your daily reminder that SQL variants aren’t pure fourth generation languages—if they were, the optimizer would take all possible constructs of a given desired operation and generate the same execution plan for all of them.
Comments closedKevin Wilkie puts on the flannel and grabs his database lumberjack axe to split some strings:
But, sometimes, you want a small list of data inherent to a query in SnowFlake. And that’s what I want to talk about today.
In SQL Server, you would create a temp table and then insert the data into it. But in Snowflake, there may be a better / easier way.
Let’s use the function SPLIT_TO_TABLE. Shockingly, it does what’s on the label – it split data and puts it into a table.
Click through for an example. Also check out the Snowflake documentation, where they make use of the lateral operator (the ANSI version of APPLY()) to generate results for multiple strings and make use of the SEQ column.
Robert Cain continues a series on KQL:
In my previous post, Fun With KQL – Max, MaxIf, Min and MinIf, we looked at the aggregation functions
maxandmin. In this post we’ll talk about another aggregation function,sum. We’ll also look at a variant of it,sumif.
Check out the details and examples in Robert’s post.
Comments closedAnshika Agrawal shows a few examples of how Scala 3 is a bit easier to pick up than Scala 2:
Scala 3 is a remodel/refit for the scala language. It attracts developers due to its improved features such as simpler syntax, better type inference, improved error messages, and enhanced support for functional programming. In this article, we will compare the syntactical enhancement of Scala 2 & Scala 3. How indentation will help developers to write code efficiently and effortlessly.
Click through for some examples. On the whole, these are small but welcome changes in eliminating unnecessary code cruft.
Comments closedIn Snowflake, there is another function that is SOUNDEX-like that can give slightly different results – SOUNDEX_P123.
For those cases where the first and second letters of the string have the same SOUNDEX number, the SOUNDEX_P123 function will keep the number for the second letter. This variant is used in a few other database systems, for example, Teradata.
Click through for a demonstration of the two SOUNDEX() variants and how results can differ.
Deborah Melkin riddles us this on dynamic SQL:
I admit it – I do waaayyyy too much with dynamic SQL. But I just keep running into situations that require it. That being said, I ran into an interesting problem that had me puzzled. I found a bunch of different blog posts that pointed to me to the right direction but required a little extra work to find the solution.
There are several concepts that are at play here, so I’ll try to break this out so we can put the pieces together. The first once is centered around dynamic SQL. There are two parts of this I want to make sure we understand first – how it fits into sessions and how it gets executed.
Read the whole thing.
Comments closedThe
maxandminaggregation functions are common to almost every language, and the Kusto Query Language is no exception. As you would think, when you pipe in a datasetmaxreturns the maximum value for the column name you pass in. Likewiseminreturns the lowest value.In addition, there are variants for each,
maxifandminif. We’ll see examples for all of these in this post.
Click through for a few functions you can call via the summarize operator.
Ryan Adams continues a series on querying the serverless SQL pool in Azure Synapse Analytics:
There are two ways to read data inside Data Lake using the Synapse Serverless engine. In this article, we’ll look at the second method which uses an external table to query a path within the lake.
Synapse is a collection of tools with four different analytical engines (Dedicated Pool, Spark Pool, Serverless Pool, Data Explorer Pool). This gives you a lot of options for ingesting, transforming, storing, and querying your data. Here you will use the Synapse Serverless Pool to query the data in your ADLS account.
Read on for a demonstration.
Comments closed