When looking through the execution plan in SQL Server, you may occasionally see “key lookup” glaring back at you.
If you find this happening a lot, and on frequently run queries, then you might want to do something about it.
Continue readingTag: what is
What is a One-To-Many Relationship?
When working with relational databases, we tend to create a lot of relationships between tables. But not all relationships are created equal – there are different types of relationships. The one-to-many relationship is probably the most common type of relationship in relational database design.
A one-to-many relationship is a type of association between two tables where a record in one table (the “one” side) can be related to multiple records in another table (the “many” side). For example, in an e-commerce database, a single customer can place many orders. In this scenario, the Customers table would have a one-to-many relationship with the Orders table.
What is a Many-To-Many Relationship?
In SQL, a many-to-many relationship occurs when multiple records in one table can be associated with multiple records in another table.
To represent this type of relationship, a third table—often referred to as a “junction table” or “associative entity”—is used. This junction table typically contains foreign keys that reference the primary keys of the two related tables, allowing for the connection between multiple records.
Continue readingExplanation of ON DELETE NO ACTION in SQL Server
In SQL Server, ON DELETE NO ACTION is an option that we can apply to foreign key constraints to prevent the deletion of a row in the parent table if there are related rows in the child table.
Unlike ON DELETE CASCADE, which would delete the related rows in the child table, NO ACTION enforces that if a deletion would result in orphaned records in the child table, the deletion operation is prohibited, and an error is raised.
Understanding the RID in SQL Server
You may have seen the term “RID lookup” when reading about heap tables, or perhaps when viewing the query plan for a query against a heap in SQL Server.
But what exactly is a RID lookup? And what exactly is a RID?
Let’s find out.
Continue readingCompound Keys Explained
In SQL databases, a compound key is a type of primary key that consists of two or more columns combined to uniquely identify each row in a table. The key columns are used together as a single unit to ensure uniqueness.
Some within the SQL community define compound keys as composite primary keys comprising of foreign keys from other tables, so there doesn’t seem to be an agreed consensus on the precise definition.
With that in mind, let’s explore these definitions of compound keys.
Continue readingWhat is a Composite Key in SQL?
In SQL, a composite key is a key that consists of two or more columns used together to uniquely identify a record in a table. The combination of these columns creates a unique identifier, even though the individual columns might not be unique by themselves.
Continue readingUnderstanding the ON UPDATE SET NULL Option for Foreign Keys in SQL Server
In SQL Server (and relational databases in general), a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.
This relationship is a cornerstone of relational database design, ensuring referential integrity between the related tables. When designing databases, it’s crucial to understand how changes to primary keys in parent tables can affect the foreign keys in child tables.
One of the various options available in SQL Server for handling such changes is the ON UPDATE SET NULL action.
Understanding the RID Lookup in SQL Server: How It Affects Performance
If you’ve spent any time working with heaps in SQL Server, you may have seen the RID lookup in the query plan. And if you haven’t, chances are it’s because you either didn’t look at the query plan, or the queries used full table scans.
In any case, it pays to understand what a RID lookup is and how it affects query performance. If we ever see RID lookup in the query plan, it could be a good opportunity to see if we can make changes that will improve performance.
Continue readingWhat is a Surrogate Key in SQL?
In SQL and relational database design in general, an important concept is the identification of unique rows in a table. A surrogate key is one method for achieving this.
Understanding what surrogate keys are, and how they differ from natural keys, is crucial for database administrators, developers, and anyone involved in data modeling or management.
In this article we’ll explore what the surrogate key is, how it differs from the natural key, and examples of implementing it in SQL.
Continue reading