A Fine Slice Of SQL Server
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 closedThe first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.
And that same thing probably happened the next several times, too.
But eventually, I took a deep breath and read the code more closely to understand what it was doing.
I do like to joke that this is cryptic code that gets handed down from generation to generation, with each generation saying “Don’t touch the code, for you do not understand it.” But as of SQL Server 2017, you don’t need to do this anymore and can use STRING_AGG().
When you’re working with a database, it’s very hard to not deal with strings at some point in your journey. There are lots of different functions that you will be working with when you’re working with strings. Today, I want to go over some of the basic ones that you’ll use in Snowflake.
The first two that you’ll deal with make the string either upper or lowercase. Yes, that’s right – you’ve probably figured out the names of the functions already. UPPER() and LOWER() are the 2 functions respectively.
Kevin mentions title capitalization (though not by name) and the quick rule depends on which rulebook you’re using. I grew up with MLA, which I summarize as:
And a quick bit of advice: understanding title capitalization really does make you look more professional, I promise. Unless we’re using different rulebooks, in which case at least one of us is a heretic.
Comments closedKevin Wilkie generates some random data:
One of the many things that the business team asks me to do is to create random-ish data. Thankfully, in Snowflake, there are many ways to make this happen. Today, I want to go thru just a few of them.
Perhaps the one that most people are familiar with is making Snowflake create a random number.
Click through for initial coverage of the RANDOM() function, as well as how you can generate data across a uniform distribution over a given range.
Brendan Tierney rounds up the usual suspects:
This type of index is a little different to your traditional index. With DBMS_SEARCH we can create an index across multiple schema objects using just a single index. This gives us greater indexing capabilities for scenarios where we need to search data across multiple objects. You can create a ubiquitous search index on multiple columns of a table or multiple columns from different tables in a given schema. All done using one index, rather than having to use multiples. Because of this wider search capability, you will see this (DBMS_SEARCH) being referred to as a Ubiquitous Search Index. A ubiquitous search index is a JSON search index and can be used for full-text and range-based searches.
This is an interesting approach to the problem, though as I think about it, it makes me wonder, if you’re constantly searching in A+B+C+D, is that really four separate attributes or has something gone wrong in the design? It’s early enough in the morning for me that I’m willing to accede to there being use cases in a well-designed database.
Comments closedSteven Sanderson shows off a function in R:
As a programmer, you’re constantly faced with the need to repeat tasks efficiently. Repetition is a fundamental concept in programming, and R provides a powerful tool to accomplish this: the
rep()function. In this blog post, we will explore the syntax of therep()function and delve into several examples to showcase its versatility and practical applications. Whether you’re working with data manipulation, generating sequences, or creating repeated patterns,rep()will become your go-to function for mastering repetition in R.
Read on for the basic syntax, as well as several examples of how to use the rep() function.
Thom Andrews solves a problem:
Notice that the error line states line 2 not line 8, which is the line the
sys.sp_executesqlwas called on. Knowing the line the error occured on within the dynamic batch is certainly important, but if you’re working with multiple dynamic batches you have no way of knowing which dynamic batch might have produced the error; was it the one executed on line 100? Line 200? Line 350?Solving this problem wasn’t wasn’t exactly simple, and it came with a couple of caveats.
Click through for the approach. As Thom mentions, it isn’t perfect, but it is reasonable and interesting.
Comments closed