A Fine Slice Of SQL Server
Haripriya Naidu runs an experiment:
Instead of looking at which option is faster, you may want to look at which option is better suited for a given context. Let’s take a look at 2 common options to insert data and analyze them.
INSERT INTO TARGETTABLE SELECT * FROM SOURCETABLE OR SELECT * INTO TARGETTABLE FROM SOURCETABLE
Click through for a comparison of the two, not just for which is faster but also the pros and cons of each approach.
Comments closedLouis Davidson continues a series:
As started in part 1 of this series, I have set out to test an LLMs ability to technical edit. For my first set of tests, I am using a pair of articles I created, filled with very bad advice. The advice is the same for both articles, but what differs is the intro and the conclusion. One says the advice is good, the other said it is bad. It is all very very bad, including a really terrible
SELECTstatement versus loop construct that will cause an eternal loop that inserts into a temporary table.My goal is to see how much of that advice will be noted as bad, and if it says anything nice at all about the text, etc. If you want to see the entire documents, you can get them here in a zip file, both in text and word document formats.
Starting with an extreme example like this is fine, I believe. Given the results, they were fine, though it sounds like Louis won’t be out of a job anytime soon.
Comments closedIn this video, I explain the classic Monty Hall problem, based on the concept of the show Let’s Make a Deal. I explain the paradox behind the problem and demonstrate that it’s better to switch doors.
I’m not joking at all when I say it took me years of listening to explanations before it actually clicked. Some of it is my innate stubbornness, but I think this is a great example of a true paradox, where the intuitive answer is wrong and first-level reasoning also leads you astray.
Comments closedAndy Brownsword takes a look at one quality of life improvement in SQL Server 2022:
Window functions allow us to perform a function across a set of rows in a result set, rather than how we might typically group them. In SQL Server 2022 we have a new clause available for our queries which can help tidy up how these are defined.
You can do some neat things with the WINDOW clause, including partial matches. For example, you could define a window r1 AS (PARTITION BY x.MyColumn) and then another window r2 AS (r1 ORDER BY x.SomeOtherColumn). I don’t think there are a huge number of scenarios in which this is helpful, especially because performance typically depends upon minimizing the unique number of window functions in your query, but every once in a while it’s a really neat thing.
Vlad Drumea is looking for matches:
Figuring out object dependencies in a SQL Server database, especially one you’re not familiar with, can be a challenge.
Luckily, Microsoft provides the sys.sql_expression_dependencies system catalog view to help DBAs and developers track down various object dependencies.I leveraged this catalog view in a query that returns the necessary info in a more user-friendly way, while also allowing easy filtering for referenced or referencing object.
Click through for the script. It’s been a while since I’ve used this DMV in anger, though my recollection is that I had problems finding dependencies over linked servers.
Comments closedJonathan Garriss has an announcement:
We’re excited to unveil the Microsoft Fabric SKU estimator, now available in preview—an enhanced version of the previously introduced Microsoft Fabric Capacity Calculator. This advanced tool has been refined based on extensive user feedback to provide tailored capacity estimations for businesses.
Designed to optimize data infrastructure planning, the Microsoft Fabric SKU Estimator helps customers and partners accurately assess capacity requirements and select the most suitable SKU for their workloads, protecting users from under-provisioning and overcommitment.
And, in classic Microsoft Fabric fashion, it’s in preview.
Coming up with some fairly low estimates for a lot of things, it bounced me between an F32 and an F64, which seemed about right.
Comments closedChad Callihan fixes a mismatch:
Just as you can’t judge a book by its cover, you can’t judge a SQL Server login by its name.
With availability groups, you may notice a login on a primary replica and a “matching” login on the secondary replica. But just because the login is named the same on each replica doesn’t mean they’re the same to SQL Server.
Click through for the explanation and how you can fix it. The other way to fix it is to use Active Directory (or Entra ID) logins, though that’s not always in the cards.
Comments closedHans-Jürgen Schönig builds some indexes on vector data:
In the previous post we have imported a fairly large data set containing Wikipedia data, which we downloaded using pgai. However, importing all this data is not enough because we also need to keep an eye on efficiency. Therefore, it is important to understand that indexing is the key to success.
Click through for an overview of what options are available for indexing vectors in PostgreSQL, as well as the trade-offs and disk space requirements.
Comments closedAri Lamstein has an announcement:
With this version, I have transferred the maintenance of choroplethr to Zhaochen He, an economics professor at Christopher Newport University. Zhao addressed the issues that led to choroplethr being archived from CRAN in February. Please join me in thanking Zhao for his contribution!
Click through for the updates, as well as what Ari views as the current challenges for the project as he hands the project over Zhaochen He. H/T R-Bloggers.
Comments closedYael Biss has an announcement:
Microsoft Purview’s Data Loss Prevention (DLP) policies for Fabric now supports Fabric KQL and Mirrored DBs!
Purview DLP policies help organizations to improve their data security posture and comply with governmental and industry regulations. Security teams use DLP policies to automatically detect upload of sensitive information to Microsoft 365 applications like SharePoint and Exchange, and to Fabric’s semantic models and lakehouses.
In today’s fast-paced data-driven world, enterprises are building more sophisticated data platforms to gain insights and drive innovation. Microsoft Fabric Lakehouses combine the scale of a data lake with the management finesse of a data warehouse – delivering unified analytics in an ever-evolving business landscape. But with great data comes great responsibility. Protecting sensitive information and ensuring regulatory compliance is paramount. That’s where Data Loss Prevention (DLP) policies with restricted access come into play.
Click through to see what this preview currently offers.
Comments closed