A Fine Slice Of SQL Server
Kevin Wilkie is ready for that end-of-month paycheck:
When you work with data, you’ll probably need to work with dates at least once a month. That is the nature of the beast. Today, let’s compare working with them in SQL Server and Snowflake. I want to focus only on adding and subtracting months when provided with a specific day.
Along the way, I would also push for a calendar table, so that you can remove some of the more difficult (or even most common) date calculations.
Comments closedA while back, I wrote a couple of tips about simulating
TRY_CONVERT– a function added in SQL Server 2012 – in earlier versions (see Part 1 and Part 2). Recently, someone in the community had a similar requirement: they wanted to makeTRY_PARSEwork for dates in SQL Server 2008. Now, I’m not one to help people stay on versions of SQL Server that are now 15 years old, but I do acknowledge that hands may be tied, and they can’t always control the version they’re stuck with.
Aaron does this community member a solid, though it’s probably something you’d never want to use if you’re on SQL Server 2012 or later.
Comments closedKevin Wilkie makes a lateral move:
In Snowflake, you can do something akin to an APPLY, but not. That, my friends, is a LATERAL join. According to Snowflake documentation, this type of join “allows an inline view to reference columns from the table expression that precedes the inline view.”
Yes, that’s gibberish. Let’s show what you’re doing.
I’m such an APPLY apologist that the description made sense to me.
Ryan Booz shares a few bits of advice about people learning about Postgres:
I was much further in my SQL Server career than I should have been before I understood the usefulness of
APPLYin T-SQL. I didn’t realize thatAPPLYisn’t a SQL standard and so when I started using PostgreSQL and needed similar functionality, I was perplexed trying to find the equivalent. Fortunately, I wasn’t the only one searching.
My recollection here is that Microsoft introduced the APPLY operator before there was an ANSI standard, so as long as my memory isn’t faulty here, this wasn’t a case of a vendor just deciding to name something differently for the sake of it.
Regardless, Ryan has a lot of good advice for learners of PL/SQL.
Comments closedBalmukund Lakhani announces a feature has gone generally available:
Today, we are announcing General Availability (GA) of native implementation of APPROX_PERCENTILE in Azure SQL Database and Azure SQL Managed Instance. We announced preview of these functions in October 2022. Since then, many customers have adopted these for the applications where response time of percentile calculation was more important than the accuracy of the result.
I have and will continue to extol the virtues of these two functions wherever I go. They’re considerably better than the originals once you start getting into the hundreds of thousands or millions of rows. They’re also available in SQL Server 2022.
Comments closedChad Callihan doesn’t need to wait for the query’s end credits sequence:
When checking for the existence of a value or values in a table, typically, the whole table does not need to be read. The goal is to obtain more of a true or false answer, whether a criteria is met or not. If the criteria is met with the first few records then there’s no need to read on. Nonetheless, you may come across scripts written to unnecessarily retrieve the complete count of a value in a table. Let’s compare the performance difference between using COUNT(*) and using “IF EXISTS” when checking a table for values.
One’s going to give you a full scan and the other will give you a semi-join. Read on to see what the practical effect of this is.
Comments closedRajendra Gupta pivots abruptly:
A pivot table is a popular tool in Microsoft Excel that shows summarized data and helps you analyze it in various ways. Pivot tables collect and organize data from different rows, columns, and tables. Pivot tables are a great way to summarise data, and a handy tool for analyzing sales revenue, products sold, sales performance, etc.
Relational database tables store data in multiple rows and columns. You can calculate data using various functions such as count, sum, and average. SQL Server provides the PIVOT and UNPIVOT functions for working with pivot tables. How do we create the pivot tables in PostgreSQL? Let’s find it out.
Read on for a demonstration.
Comments closedKevin Wilkie only believes in two dimensions:
The function FLATTEN takes our JSON object and gives us the ability to put the data into an actual table. If we run the original query above, then we’ll get the data shown below:
Read on for an example using JSON, though as Kevin mentions, FLATTEN() will also work with arrays and XML data.
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.