SQL Server provides various SET options that control the behavior of our session. These options can impact query execution and results. Also, some features in SQL Server rely on certain SET options to be configured in a certain way (for example indexed views).
Getting the current SET options configuration is not as straightforward as one might think. We need to do a bit of trickery.
Let’s look at how to view our current SET options.
If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY clause.
We can’t use aggregates or subqueries in the GROUP BY clause.
To fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.
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 readingWhen 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.
If you’re getting an error that reads something like “Cannot create index on view … because its select list does not include a proper use of COUNT_BIG…” and so on in SQL Server, it could be that you’re trying to create an index on a view that uses the GROUP BY clause, but doesn’t have the COUNT_BIG() function.
If the view contains a GROUP BY clause, then it must also have COUNT_BIG(*).
To fix this issue, try adding COUNT_BIG(*) to your SELECT list.
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 readingIn 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.
If you’re getting SQL Server error 7999 that reads something like “Could not find any index named ‘IX_LastName’ for table ‘Employees’“, it appears that you’re trying to create an index with the DROP_EXISTING argument ON, but there is no existing index of that name.
When we set the DROP_EXISTING argument to ON, SQL Server tries to look for the index to drop before it recreates it with the new definition. If it doesn’t exist, then we get the error.
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 readingIn 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 reading