Press "Enter" to skip to content

Curated SQL Posts

Advent of Code Day 3

Published 2023-12-12 by Kevin Feasel

Kevin Wilkie lives the struggle. Check out part one:

Then we do something very similar to what we did in Walking Through the Advent of Code Day 2 – find the digits, and the remaining text, and we’re home free!

Notice, though, that the position we grab is found at the very end. We just find the string in the line and show where it is. Except, that doesn’t always work.

And then there’s part 2:

Hopefully, by now, you’ve read my post Walking Through the Advent of Code Day 3. After looking through it, you’re probably thinking… “What have I gotten myself into? Is SQL the way to go with all of this? Can I back out now and learn a programming language that AoC can be done in “easily”?”

Click through to experience the pain or at least have a little bit of Schadenfreude in your life.

Comments closed

Error Handling in T-SQL Stored Procedures

Published 2023-12-12 by Kevin Feasel

Erik Darling intimates that some of our code might occasionally have errors or might experience circumstances in which not everything is in perfect alignment:

Okay, look, the best post about this is, of course, by Erland Sommarskog: Error and Transaction Handling in SQL Server

Just like Erland, it has three parts and three appendices. If you want to learn how to do a whole lot of things right, give yourself a couple days to read through and digest all that.

What I’m here to talk about is some of the stuff you should think about before implementing any error handling at all.

I agree with most of Erik’s opinion here. My very mild disagreement is that I’ll still protect against things like invalid parameters or logic errors (start date before end date) in the stored procedure. I do that for three reasons:

  • Defense in depth isn’t just a security principle–it’s also a code practices principle.
  • The app gets things wrong, too. Sometimes, the app dev accidentally sends parameters in the wrong order, and it’s better to get an error early on in development versus thinking everything works because the procedure called successfully and ship it.
  • Even if “the” app correctly handles inputs, there’s always a chance some other app or process will call this stored procedure and it might not have the same error handling code built in.
Comments closed

Security in Microsoft Fabric

Published 2023-12-12 by Kevin Feasel

Alex Lisboa-Wright talks security:

In Fabric, the basic logical structure for data services is the Workspace, in which users can create Items, which are the various resources that perform all the data operations available in Fabric, such as Lakehouses, pipelines, machine learning models and so on. Each workspace is a self-contained data storage and development environment, whose user access is controlled by both workspace admins and member users. User access controls include options to manage users’ workspace roles, which determine the permissions assigned to each user. Security permissions can be managed on the workspace and item levels in the Fabric UI. MEID authentication can also be employed within Fabric, as connecting Fabric items to other Azure resources requires MEID. MEID’s Conditional Access feature can also be configured for use in Fabric (see this documentation for best practice for Fabric resources linking to other Azure services).

Read on to learn more. Fabric is a broad set of tools and technologies, so security is both important and definitely non-trivial, even when you consider that it is a software-as-a-service offering and therefore doesn’t have much going on with user-facing networking or infrastructure security.

Comments closed

Databricks Security Analysis Tool

Published 2023-12-11 by Kevin Feasel

Advait Bhadane takes a look at a tool:

In today’s data-driven world a cutting-edge platform is required that seamlessly integrates with the cloud, embraces open-source innovation and prioritises robust data security. Databricks is a pioneer in this field. Not only does it provide a unified lake house platform, but it takes data protection to the next level with its Security Analysis Tool (SAT).

In this blog, we will unravel the power of Databricks’ SAT, focusing on the pivotal role it plays in generating daily health reports for your workspaces. It will also walk you through the step-by-step process of setting SAT in your workspace.

Click through to see what this tool can do for you.

Comments closed

Goodbye, Ask SQLServerCentral

Published 2023-12-11 by Kevin Feasel

Thomas Rushton gives Ask SQLServerCentral a Viking funeral:

When I started out as a full-time DBA back in 2010, it was the first Q&A site I found that was active, friendly, and easy to use.

It differs from the main SQLServerCentral Q&A boards by not being broken down into questions relating to a particular version of SQL Server, and a particular aspect of that version. This makes it easier for someone to just ask a question without worrying about if their question is going to the right place. A great benefit to those new to the scene.

Right around the time Thomas started, I was also getting into it and was active for a couple of years until a job change made it tougher for me to dedicate the time. I appreciate everything the crew did for so long, especially because it was a really good alternative to a dedicated Stack Exchange site for databases (which I think had officially started after Ask SQLServerCentral).

Comments closed

An Overview of Lakehouses in Microsoft Fabric

Published 2023-12-11 by Kevin Feasel

Kevin Chant invites you to a swank lakhouse:

By the end of this post, you will have a good overview of Microsoft Fabric Data Lakehouses, including CI/CD options. In addition, where your SQL Server background can prove to be useful and where some Power BI knowledge can come in handy.

Plus, I share plenty of links in this post. For instance, there are a couple of useful resources to help you get started towards the bottom of this post.

Click through for the article.

Comments closed

Random Number Generation in T-SQL

Published 2023-12-11 by Kevin Feasel

Andy Yun has a method:

This is a quick blog to “document” a T-SQL technique for generating random numbers. I’ve been using this for years, but don’t use it frequently enough to have it fully memorized. So whenever I do need it, I must constantly have to go look up whenever I need to use it.

Click through for Andy’s method. This will generate random numbers based on a uniform distribution: the likelihood of getting any value in the range is equal. If you want to build out some data that approximates a normal distribution, I have a blog post for that.

Comments closed