Helping you know what you don't know about data technology

Database Weekly for September 7, 2024 Problems displaying this newsletter? [View online](. [Database Weekly]( The Complete Weekly Roundup of SQL Server News by SQLServerCentral.com Hand-picked content to sharpen your professional edge Editorial  Knowing What You Don't Know I used to think I knew everything. I felt like I had the world all figured out. But then, when I went to kindergarten, I realized that I didn't know much at all. As I grew older and finished college, I regained confidence in my knowledge and again felt like I knew everything about the universe. However, as I got out into the real world of employment and failed a few times, I began to realize just how little I actually knew. That trend has continued every year of my life borne out of plenty of failures, but also from realizing just how much stuff there is to know in the world. Socrates once said, "The only true wisdom is in knowing you know nothing," I've really taken that to heart over the years, and recently, this concept came to mind as I was learning about how PostgreSQL works. At first glance, it appeared very similar to other SQL Server products, leading me to assume that the internals and workings would be very similar. This assumption was a bit dangerous. While there are indeed many similarities, I discovered unexpected differences, such as how concurrency is implemented. For example, SQL Server uses an entirely lockless approach to implement MVCC (Multi-Version Concurrency Control) for their in-memory objects, and I expected PostgreSQL would also. But for some parts of the isolation process, PostgreSQL implements locks. So, from what I understand currently, it seems to mean that PostgreSQL behaves pretty similar to how SQL Server does when using Read Committed Snapshot... but I am not quite sure yet. And if I was about to start a PostgreSQL project where isolation and concurrency were very important, I would not feel immediately confident to trust my knowledge or instincts. Seeking out knowledge on topics you believe to be true is really hard, but quite often an essential, repeatable step in learning. Not only do you need to read that something is true and honestly still true, but you have to overcome that "I know everything" attitude that you are bound to have in your head. Add to that that one of the most concerning parts of "doing your research" is that when you read blogs or articles about a topic you don't fully understand, you might unknowingly accept incorrect information. Depending on how critical some knowledge is, it's essential to thoroughly test and validate the principles you learn, especially when working on complex topics. And is that ever hard. Too often people, me included, think they know a lot more than they actually do and assume they are right without really knowing so. If you aren’t sure about a topic, take some time to learn from various sources and try to find sources you know are credible. And if you can verify that they are right, do that too.  Louis Davidson (@drsql) [Join the debate, and respond to the editorial on the forums](  The Weekly News All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. Vendors/3rd Party Products [Instant Database Reset and Easier Bug Fixing using Clones]( The article demonstrates how the use of lightweight, containerized clones removes the problem of 'mopping up' after a database migration fails part way through, particularly in database systems like MySQL that don't support DDL transaction rollback. A quick reset to the previous saved revision provides 'instant rollback'. It also demos how they make it much easier and less disruptive to do retrospective bug fixing on older migrations. AI/Machine Learning/Cognitive Services [Few Have Tried OpenAI’s Google Killer. Here’s What They Think.]( From IT Pro - Microsoft Windows Information, Solutions, Tools OpenAI’s search tool shows promise but lacks Google’s specialized functions and can suffer from hallucinations. Administration of SQL Server [Missing Columns in Extended Events Live Data Explorer]( From Scary DBA (Grant Fritchey) Let me be extremely clear up front, this is not my... Azure SQL [Real-Time Streaming in Azure]( From Curated SQL Temidayo Omoniyi takes us through an architecture: In today’s world, billions of data are generated daily from messaging applications like WhatsApp, financial data like the… [Using T-SQL Snapshot Backup - Point in Time Recovery - Azure Edition]( From SQLServerCentral Blogs Introduction 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... The... DMO/SMO/Powershell [PowerShell Speech Recognition: How To Set up Voice Commands and Responses]( From IT Pro - Microsoft Windows Information, Solutions, Tools Learn how to create a PowerShell script that can listen to your voice and respond with spoken words. Data Mining / Data Analysis [EsProc SPL and SQL: Complimentary, But Different, Technologies]( Have you ever surfed the internet and felt overwhelmed by personalized advertisements that appear to know your every desire? Have you ever been amazed at how accurate some weather forecasts can be? These actions are based on data processing. Database Design, Theory and Development [Choosing Data Types and Column Options]( From Simple Talk An entire post on choosing the correct data types ... [Geographic Sharding is Usually a Pretty Bad Idea.]( From Brent Ozar Unlimited Let’s say you built a web app and it became glob... DevOps and Continuous Delivery (CI/CD) [Unlock Business Value Through Effective DevOps Infrastructure Management]( From IT Pro - Microsoft Windows Information, Solutions, Tools Effective DevOps infrastructure management is key to accelerating development cycles, enhancing collaboration, and maintaining security while aligning with organizational goals and customer needs. Microsoft Fabric ( Azure Synapse Analytics, OneLake, ADLS, Data Science) [Querying a Fabric SQL Endpoint via Notebook and T-SQL]( From Curated SQL Sandeep Pawar talks about a Spark connector: I am not sharing anything new. The spark data warehouse connector has been available for a couple months now. It… [Run a Microsoft Fabric notebook from Azure DevOps]( From Kevin Chant In this post I show two different ways to authenticate when calling APIs. One way to authenticate with a service principal and another with a Microsoft Entra ID user account that has multifactor authentication (MFA) disabled. As per the below diagram. [Expert Guide: GitHub Integration for Microsoft Fabric]( From Guy in a Cube We talked about Git integration with Azure DevOps in the past, but now you can integrate GitHub with Microsoft Fabric! Once things are configured most things are the same because it’s just Git! Adam walks you through it! Oracle/PostgreSQL/MySQL/other RDBMS [MySQL Index Overviews: Composite B-Tree Indexes]( Indexes in MySQL are one of the primary ways to enhance query performance and they are especially useful when the primary use case of our project refers to reading data stored in a database. We‘ve already told you about the nuanced world of indexes in MySQL – and there we‘ve mentioned that MySQL has multiple types of SQL indexes available for you to choose from. [Henrietta Dombrovskaya: “Sometimes, It’s Slow!”]( From Planet Postgres These are my least favorite mysteries to resolve. ... [How to Efficiently Change Data Types in Snowflake for Optimal Performance]( From Sherpa of Data When working with data, I usually have an idea of ... Performance Tuning SQL Server [Identify CPU Intensive SQL Server Queries from an Implicit Conversion]( From MSSQL Tips In a previous tip on SQL Server 2016 Query Store Introduction, we've seen how to enable Query Store and find out the top resource consumers. We've experienced high CPU utilization recently and wanted to know the root cause and how to fix it. How can we use Query Store to achieve this? [Annoyances With Filtered Indexes And Computed Columns In SQL Server]( From Erik Darling Data Annoyances With Filtered Indexes And Computed Colu... [SQL Index Rebuild vs Reorganize Comparison]( From MSSQL Tips This SQL tutorial focuses on the differences and similarities of these operations. Before beginning, some important concepts related to this topic will be explained. In particular, background information about the fill factor and statistics will be provided, as these concepts are referred to while discussing Index Rebuild and Reorganization operations. [When To Use DBCC DROPCLEANBUFFERS When You’re Tuning SQL Server Queries]( From Erik Darling Data When To Use DBCC DROPCLEANBUFFERS When You’re Tuning SQL Server Queries Thanks for watching! Going Further If this is the kind of SQL Server stuff you love learning about,... [A Foreign Key Join Elimination Limitation In SQL Server]( From Erik Darling Data A Foreign Key Join Elimination Limitation In SQL S... PostgreSQL [Solving the Overlap Query Problem in PostgreSQL]( Range queries are a very common task in SQL: selecting dates, numbers, or even text values that fall within some specified range. For instance: all loan applications for the month of March, or all sales transactions between $50 and $500. SQL developers are generally adept at writing such queries and indexing them to ensure they perform well. PowerPivot/PowerQuery/PowerBI [Power BI with SQL Always Encrypted Data. Database Connectivity...Open you say?]( From SQL Rod In this episode, we're going to look into how we can read that Always Encrypted data (On Prem, with the Key secured by Azure Key Vault), using Power BI and PowerBI Desktop. [The Surprising Enhancements for DAX Query View in Power BI]( From Guy in a Cube The DAX Query View for Power BI got some nice updates and a special item for those on a Mac! Zoe Douglas walks us through what we can do! T-SQL and Query Languages [How Learning SQL Basics Transformed My Productivity]( From SQLServerCentral Blogs As a data analyst, my switch from Excel to SQL was revolutionary. This article reveals how an online SQL basics course dramatically improved my workflow. I've always depended on... The... The Lighter Side [Simple Talks Episode 5]( From SQLServerCentral Blogs The fifth episode of Simple Talks Podcast is out! This episode was recorded in Austin, where we celebrated the Redgate 25th Birthday as a company. Grant, Ryan, and I grabbed a conference room. Ryan and I also grabbed some cowboy hats that were in the office. Tools for Dev (SSMS, ADS, VS, etc.) [Continued feedback for SSMS 21]( From MS SQL Server Blog Hello SSMS users! A short post today to remind folks to check out the feedback site regularly. As I mentioned in a previous post, I started a Friday Feedback...  [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. Note: This is not the SQLServerCentral.com daily newsletter list, and unsubscribing to this newsletter will not stop you receiving the SQL Server Central daily newsletters. If you want to be removed from that list, you can follow the instructions on the daily newsletter. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -