Press "Enter" to skip to content

Curated SQL Posts

K-Means Clustering in SQL Server

Sebastiao Pereira implements k-means clustering in T-SQL:

K-means clustering is an unsupervised machine learning algorithm used to group data into k distinct clusters based on their similarity, allowing for customer segmentation, anomaly detection, trend analysis, etc. The most common machine learning tutorials focus on Python or R. Normally, data is stored in SQL Server, and it is necessary to move data out of the database to apply clustering algorithms and then, if necessary, to update the original data with the cluster numbers. Is it possible to do it directly in SQL Server?

Given the work you have to do to implement this, I can’t imagine that it would be particularly fast. But it is neat to see that it’s possible.

Comments closed

Window Functions in SQL Server

I have a new video:

In this video (part 1 of a new series), I explain what a window function is, as well as the components of window functions.

It’s taken a couple of months for me to get back on the video production wagon. This video serves mostly as the classroom primer for what will be primarily a demo-heavy series.

Comments closed

Collations and citext in PostgreSQL

Umut Tekin picks up on a thread:

Recently, I read Laurenz Albe’s blog about case insensitive string search. He recommended case insensitive collations saying, “it won’t be any more expensive (and maybe more intuitive) than the current techniques of using upper() or the citext extension are today”. In other words, a custom collation can be used instead of citext to achieve case insensivity with a lower performance penalty. Today we will be testing it.

Click through for the results of those tests.

Comments closed

Comparing Write-Back Options for Power BI

Jon Vöge compares two options:

We’ve previously on this blog covered Power Apps write-back for Power BI/Fabric comprehensively, and in the past months we’ve taken a stab at the Fabric Native solution: Translytical Task Flows.

However, when comparing the different options, which solution actually comes out on top?

Read on as Jon contrasts the two options and explains when you might want to use each.

Comments closed

Modifying Power BI Page Visibility and Active Status via Semantic Link Labs

Meagan Longoria hides (or shows) a page:

Setting page visibility and the active page are often overlooked last steps when publishing a Power BI report. It’s easy to forget the active page since it’s just set to whatever page was open when you last saved the report. But we don’t have to settle for manually checking these things before we deploy to a new workspace (e.g., from dev to prod). If our report is in PBIR format, we can run Fabric notebooks to do this for us.

Click through for a notebook and an explanation.

Comments closed

SQL Server Auditing Bug Allows Data Exfiltration without Logging

Andreas Wolter describes a bug in SQL Server’s auditing capabilities:

Last week, I was contacted by an IT Leader from Saudi-Arabia who previously found several CVE’s in Oracle and Microsoft SQL Server. He wanted my opinion on a newly discovered security issue in SQL Server Auditing.

Interestingly, his findings directly overlap with a topic I wrote about just last month: Using Data Classification to Audit Data Access.

Emad Al-Mousa identified two vulnerabilities in the SENSITIVE_BATCH_COMPLETED Audit Action Group. Microsoft Security Response Center (MSRC) acknowledged the issue but classified it as low priority – meaning it may not be addressed until a major release, if at all.

Read on to see what the issue is and how you can trigger it today. Andreas also includes a workaround that will work in the meantime.

Comments closed

Automating SQL Server Deployments via dbatools

David Seis digs into scripted SQL Server installation:

In this and the next two blog posts I will be bringing diverse dbatools commands into scripts that can handle a complete deployment, do a checkup of major health and configuration metrics, and do a true up of a pre-existing instance. This post will cover the complete deployment, which if you have been reading the audit series will be much more than just the SQL install of last post. This time we are aiming for the whole thing. Install, update, configure host, configure SQL, Deploy maintenance. Everything  I can think of!!

Clicking next-next-next one or two times for SQL Server installation is fine—it gives you an idea of what capabilities are available and what you need to know about. By the time you’ve installed SQL Server 5-10 times, you should familiarize yourself with the configuration files (especially because they get auto-generated for you after you use the GUI—SQL Server itself uses these to install!), and should be looking for ways to automate this process and avoid misclicks or wasting time that you could otherwise be using by reading Curated SQL.

Comments closed

No More Default Semantic Models in Microsoft Fabric

Nicky van Vroenhoven has good news for us:

Another quick post, because today is an important day for everyone working with Fabric and Power BI!

Last month, Microsoft announced they are Sunsetting Default Semantic Models: Yaay! 
Today marks that day: No more automatic child semantic models!

The idea of having a default semantic model seemed like a good one, but the problem was that too many environments had very specific needs that a default semantic model couldn’t anticipate or address. As a result, these tended to confuse end users more than save them time.

Comments closed

RIP Phil Factor

Tony Davis has some sad news:

We are deeply saddened to share the news that Andrew Clarke, better known to Simple-Talk readers as Phil Factor, recently passed away. He was the site’s editor for several years and continued writing for Redgate long after. Many readers will have learned much of what they know about SQL from Andrew. Others will remember working with him on articles, benefiting from his sharp wit and knowledge, or perhaps meeting him at a PASS conference. To all who knew him, he was a uniquely talented, intelligent, kind, generous, and funny man.

I don’t think I ever met Andrew in person, but I loved his Phil Factor articles. I appreciated all of the work he would put into his testbenches, as well as the irreverent humor he’d sprinkle through. I think my favorite article he ever wrote was this one on the entity-attribute-value anti-pattern in T-SQL and how its siren-like allure drags wave after wave of developers to their doom.

Comments closed