A Fine Slice Of SQL Server
Vitaly 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 theÂidentical()Â 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.