A Fine Slice Of SQL Server
John MacKintosh wants to join on a greater than or less than operation:
For day 5, I had to create a function, and I’m writing this up, because it’s an example of a non-equi join between two tables.
In this particular sitation, there are are no common columns between the two tables, so my usualdata.tablehack of copying the columns of interest, renaming themjoin_col, and then keying them both does not work.
Click through for a working solution.
Comments closedTomaz Kastrun gets to 25. Day 24 covers OneLake in Fabric:
OneLake comes automatically with every Microsoft Fabric tenant and represents a single, logical data lake. Its main features are its unification and one copy of data across the organization and multiple analytical engines.
And Day 25 provides some additional references Tomaz has found useful along the way:
To wrap up the series, let’s check the material available online, for you to continue learning, exploring and enjoying Microsoft Fabric.
All in all, this has been a really good series and well worth going through if you are learning Microsoft Fabric.
Comments closedIn this video, we will show how to install SQL Server on a machine running Linux, specifically SQL Server 2022 on Ubuntu 22.04 LTS.
This ties back to one of my first videos, covering the installation of SQL Server 2022 on Ubuntu 20.04, as at that time, there was no support for Ubuntu 22.04 and some of the libraries Microsoft was counting on had changed, so you couldn’t trick installation by using the 20.04 repository.
Comments closedJosephine Bush tracks what’s happening on that Azure SQL Database:
According to Microsoft, “Kusto Query Language (KQL) is a powerful tool to explore your data and discover patterns, identify anomalies and outliers, create statistical modeling, and more. The query uses schema entities that are organized in a hierarchy similar to SQLs: databases, tables, and columns.”
Note: KQL is case-sensitive for everything. Also, remember to refrain from querying everything just like you wouldn’t with SQL — don’t do the equivalent of SELECT * from gianttable.
Microsoft also has a lot of documentation with best practices and a quick reference guide to the Kusto commands. This blog post covers the ones I use the most.
Read on for a primer on the language, specifically some of the things you can do when reading Azure SQL Database audit information.
Comments closedKevin Wilkie continues the 2023 advent of code. Part 1 of day 7 covers card hands:
I have my version of the Day 7 data in my handy dandy table called AOCDay7. This time we’re being asked to figure out what kinds of Camel hands are given to us and then told to rank all of them.
Let’s start by aggregating and analyzing the data a little bit.
This time, our wonderful friends the elves have decided that they don’t like Jacks but they do like Jokers. So, there are a few changes to our code that will need to be made:
Click through for Kevin’s solutions to these challenges.
Comments closedAlternate title: How do I tell how far I am on that command?
This little command (frequently with a WHERE clause) is a great way to tell how far along you are on any given command. Specifically, I’ve been running a bunch of DELETEs recently. I’ve got something like 5-50 million rows of data to delete and I’d like to know how far along I am. A common way to do this would be just a simple count.
Click through for Kenneth’s technique, as well as good information from Jeff Moden in the comments.
Not mentioned in this post is that hopefully, your massive delete operations are running in batches, as trying to delete 5 million or more rows in a single DELETE command is…resource-intensive.
Comments closedDavid Fitzjarrell performs some auditing:
In a world of ever-increasing access account security has become a major concern. More often than not accounts used for automated processes gather more and more privileges, on the assumption that they are used only by managed, automated processes and execute vetted code, The argument is that these automated processes can’t go rogue and do damage with these elevated privileges, which is true, sort of. In and of itself the database does not police where these account connections originate, which can pose a significant security risk should this vetted code, or passwords for these privileged accounts, become known.
Oracle has, for decades, provided after logon triggers, which can be utilized to ‘police’ accounts by reporting where they originate and, if necessary, disallowing such logons. Of course it will be necessary to record such connection ‘violations’ in a table so that they may be investigated. A “complete” script to do this is shown below:
Click through for that script, as well as additional thoughts from David.
Comments closedErik Darling does not approve:
Like many other things we’ve discussed thus far, local variables are a convenience to you that have behavior many people are still shocked by.
You, my dear and constant reader, may not be shocked, but the nice people who pay me money to fix things seem quite astounded by what happens when you invoke local variables.
So I find myself in a difficult position: do I dredge up more red meat for the millions of die-hard SQL Server performance nuts who come here for the strange and outlandish, or produce evergreen content for people who pay my substantial bar tabs.
You have at least a 50% chance to guess what Erik does next.
Comments closed