Tell me if you’ve heard this one before: I changed data from my application, but when I checked the database, I couldn’t see the change! I’ve seen this. Loads. It can be quite perplexing for folks because they expect to see an error message if the insert, update, or delete failed. I put this post … Read more
Source control is fundamental when dealing with projects and sharing code between multiple developers. Power BI present some challenges related to source control. But it’s finally providing us with a solution to these challenges. Let’s analyse this piece-by-piece. According to your previous knowledge, you may would like to skip some section of this article. This … Read more
In this article, we will introduce Microsoft Fabric and how it relates to Power BI Premium Capacity we will turn to the mechanics of capacity utilization, and we will briefly look at how to monitor the performance loads of our Fabric resources. In a future article we will dive into performance tuning of Fabric workloads. … Read more
Recently someone posted a question where they couldn’t quite figure out how to construct a predicate based on a bit parameter. They tried to write a procedure like this, which wouldn’t parse, of course: I explained that you can’t have control-of-flow inside a SQL statement like that, at least not in T-SQL. And that the … Read more
MongoDB is a scalable database management system that stores data as documents in a collection, rather than as rows in a table, such as you’d find in a relational database system. A document is an object made up of one or more field and value pairs that contain related data. A field’s value can be … Read more
The SQL NTILE() is a window function that allows you to break a table into a specified number of approximately equal groups, or <bucket count>. For each row in a grouping, the NTILE() function assigns a bucket number representing the group to which the row belong starting at one. The syntax of the NTILE() function … Read more
Earlier this year, we migrated the entire Stack Overflow for Teams platform to Azure. This was a lengthy endeavour and Wouter de Kort, one of our core engineers, wrote about multiple technical aspects of the project in these posts: Journey to the cloud part I: Migrating Stack Overflow Teams to Azure Journey to the cloud … Read more
One of the technologies that my new job brought with it was learning about all the various database platforms that are not Microsoft SQL Server. Not that I don’t still spend time learning about SQL Server, as it will happily remain one of our largest topics, but rather that I need to learn about other … Read more
PostgreSQL continues to be all the rage in 2023, whether in “vanilla” form of the fully open-source distribution or a variant like Amazon RDS, Neon, Yugabyte, and others. If you’re interested in trying PostgreSQL but only have experience with another database like SQL Server, it can feel a bit daunting to get started. In this … Read more
There are some differences and secrets between the UI of a SQL Endpoint and the UI of a Lakehouse. I believe the lakehouse UI was changed recently, otherwise you can blame me for being distracted to this level. Let’s analyze the differences and pending points between these UI’s. SQL Endpoints When using a SQL Endpoint, … Read more
There are plenty of applications and tools available that allow for the movement of data in and out of SQL Server. Some tools are built by Microsoft, such as SSIS or Azure Data Factory. Others are created by third parties, such as Databricks or Snowflake. Still other available options make use of SQL Server features … Read more
I’ve quietly resolved performance issues by re-writing slow queries to avoid DISTINCT. Often, the DISTINCT is there only to serve as a “join-fixer,” and I can explain what that means using an example. Let’s say we have the following grossly simplified schema, representing customers, products, and product categories: And then we have tables for orders … Read more
SQL Server security structure, mechanisms and methods are very thoroughly documented in the Microsoft documentation, but it is quite daunting if you don’t already know about the functionality. I recently had a request to explain some security features of SQL Server so that internal audits could be completed. While thinking about the request and preparing … Read more
Protecting and controlling access to your data against unauthorized person(s) is crucial in any organization. Unauthorized entry or modification of your data can lead to severe and sometimes irreversible damage. Just like we mentioned at the beginning of this series, MySQL Replication is a process where data from one MySQL database known as the source … Read more
In the previous blog in this series, we learned how to produce, read and interpret execution plans. We learned that an execution plan provides information about access methods, which PostgreSQL use to select records from a database. Specifically, we observed that in some cases PostgreSQL used sequential scan, and in some cases index-based access. It … Read more
Dr. Codd first described the relational model in a paper in Communications of the ACM (CACM 13 No 6; June 1970). Some more work followed up after that by other people, giving us normal forms and other things we have taken for granted for 50+ years. I was looking at some postings on a SQL … Read more
While this article is specifically geared to SQL Server, the concepts apply to any relational database platform. The Stack Exchange network logs a lot of web traffic – even compressed, we average well over a terabyte per month. And that is just a summarized cross-section of our overall raw log data, which we load into … Read more
When people start learning a new field, for example T-SQL, it’s tempting to spend very little time trying to understand the fundamentals of the field so that you can quickly get to the advanced parts. You might think that you already understand what certain ideas, concepts and terms mean, so you don’t necessarily see the … Read more
Tables in a MySQL database are commonly related to one another, often in multiple ways, and it is only by linking the tables together that you can derive meaningful information from the data. To connect these tables together, you can use the JOIN clause, which you include in your SELECT, DELETE, or UPDATE statements. The … Read more
Over the years, SQL Server Statistics have been discussed in countless blog posts, articles, and presentations, and I believe that they will remain a core topic for a while when speaking about performance. Why is that? Well, if we were to consider the equivalent of Maslow’s hierarchy of needs for database performances, statistics would be … Read more