A Fine Slice Of SQL Server
Kevin Wilkie makes some changes:
When working with data, I usually have an idea of what type of data I will push into a field. Sometimes, for whatever reason, it is decided to change the type of data allowed in the field. Today, I want to show how that’s done in Snowflake.
Click through to learn how, and how it’s not quite the same as SQL Server.
Comments closedAnurag K talks about data storage:
Choosing the right data storage option in Azure is critical for ensuring your applications run efficiently, securely, and cost-effectively. In this blog post, we’ll dive deeper into three key Azure storage services: Blob Storage, File Storage, and Disk Storage. We’ll explore their features, use cases, and provide specific examples to help you understand how to best leverage these services in your cloud environment.
I would note here that within the Blob storage section, you could carve out an explanation of Data Lake Storage Gen2, which starts from Blob storage and then adds hierarchical namespaces (i.e., folders).
Comments closedTesting code using automated tools is common throughout the software development industry. This technique can improve the quality of the code you write as a data scientist. Testing helps refine your code, supports redesign, prevents errors, and makes it harder to write single-use code.
Here, we introduce the pytest framework and show how it can be used to test Python functions. If you don’t use a testing framework as part of your daily workflow, try experimenting with the techniques presented here the next time you write or extend a function.
I am a big fan of pytest because it strikes what I consider to be a great balance between convention and customization. There’s very little administrative overhead to creating test classes and test cases, so tests are easy to build and can it’s trivial to run a test suite or a specific part of one.
Comments closedBrendan Tierney continues a series on vector databases:
Searching semantic similarity in a data set is now equivalent to searching for nearest neighbors in a vector space instead of using traditional keyword searches using query predicates. The distance between “dog” and “wolf” in this vector space is shorter than the distance between “dog” and “kitten”. A “dog” is more similar to a “wolf” than it is to a “kitten”.
Click through to learn more about some of the common techniques for performing similarity searches against vectorized data.
Comments closedSergey Gigoyan opens a can of worms:
When data is modified in a Microsoft SQL database, the corresponding indexes are also modified. These modifications cause indexes to become fragmented. Fragmentation means that the logical order of the data inside the index pages does not match its physical order. The more the fragmentation level is increased, the more performance is affected for SELECT statements. With fragmented indexes, the information is not spread logically, making the index’s data retrieving operations more time-consuming resulting in query performance issues. Thus, fragmentation of the indexes should be fixed periodically in order to maintain high performance. Rebuild and Reorganize index operations are aimed at defragmenting indexes.
Click through to learn a bit about why we may need index maintenance, followed by a comparison between index rebuilding versus index reorganization. All that said, I do not have room in my indexing philosophy (or my heart) for reorganization. Reorganization simply doesn’t provide enough value as it is. If you want a really detailed dive into the topic, Jeff Moden put together an amazing talk that we were lucky enough to be able to record. Specifically, about an hour and a quarter in (link to timestamp), Jeff gets to the topic of reorganization and how little it actually does in practice.
Comments closedIn this post I want to share one way that you can run a Microsoft Fabric notebook from Azure DevOps.
You can consider this post a follow-up to my last post about unit tests on Microsoft Fabric items. Since somebody asked me about automating notebooks and I wanted to show it in action.
Please note, currently the ability to call the API that runs a notebook on demand does not support service principals.
Despite that limitation, Kevin shows two ways to authenticate while calling the appropriate API.
Comments closedGilbert Quevauvilliers notes an implication:
Yes, you read that right, it is possible where you have shared Power BI semantic model with an external (guest user in Entra ID) they can now connect to the semantic model using Excel.
This has been around for quite a while I just did not know about it, here is the Microsoft documentation: Semantic model connectivity and management with the XMLA endpoint in Power BI – Power BI | Microsoft Learn
The first thing to note is that this currently only works in the Excel Desktop App.
I did try using Excel on the Web and unfortunately that did not work.
I then created my Excel file, uploaded to SharePoint Online and tried to interact with the Excel Pivot table and that too did not work.
Ok enough of the limitations let me show you how to get it working.
Click through to see how. Gilbert also has an important note about row-level security, co read the whole thing.
Comments closedAnthony Nocentino continues a series on T-SQL snapshot backups:
In this post, the third in our series on using T-SQL Snapshot Backup, I will guide you through using the new T-SQL Snapshot Backup feature in SQL Server 2022 to take a snapshot backup and then perform point-in-time database restores using that snapshot backup as the base, but this time using an Azure Virtual Machine. We will explore how to manage Azure storage-level operations, such as taking snapshots, cloning snapshots, and executing an instantaneous point-in-time database restore from the snapshot with minimal impact on your infrastructure. Additionally, I will demonstrate a PowerShell script that utilizes dbatools and Azure Az modules to automate the process.
Read on for the script and plenty of details.
Comments closedIn this video, I briefly review a good book: SQL Server 2022 Query Performance Tuning by Grant Fritchey.
I can recommend, without reservation, purchasing at least one copy of this book. I could probably recommend purchasing up to 5 copies of this book but not more than that. If you have more than five copies of Grant’s book, it’ll look a little weird.
Comments closedPeter Ellis finds interesting results with sampling in R:
A week ago I was surprised to read on Thomas Lumley’s Biased and Inefficient blog that when using R’s
sample()function without replacement and with unequal probabilities of individual units being sampled:“What R currently has is sequential sampling: if you give it a set of priorities w it will sample an element with probability proportional to w from the population, remove it from the population, then sample with probability proportional to w from the remaining elements, and so on. This is useful, but a lot of people don’t realise that the probability of element i being sampled is not proportional to w_i”
Read on for a demonstration. H/T R-Bloggers.
Comments closed