13 November 2023
13 November 2023

Getting Started with MongoDB

0
4
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
09 November 2023
09 November 2023

The NTILE Function

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
03 November 2023
03 November 2023

Getting connected to PostgreSQL for the first time

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
16 October 2023
16 October 2023

Don’t use DISTINCT as a “join-fixer”

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
15 October 2023
15 October 2023

SQL Server Security Primer

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
12 October 2023
12 October 2023

A Beginners Guide To MySQL Replication Part 6: Security Considerations in MySQL Replication

0
3
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
09 October 2023
09 October 2023

PostgreSQL Indexes: What They Are and How They Help

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
05 October 2023
05 October 2023

First Normal Form Gets No Respect

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
25 September 2023
25 September 2023

MySQL joins

0
93
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
07 September 2023
07 September 2023

Working with MySQL constraints

0
1
MySQL provides a set of constraints that you can include in your table definitions to help ensure the integrity of the data. The constraints let you better control the types of data that can be added to the database. For example, you can use constraints to set a column’s uniqueness or nullability, specify a default … Read more