In SQL, a deterministic expression or function always returns the same result when given the same input. Conversely, a nondeterministic expression or function may return different results even with the same input due to factors like system time, randomization, or external data changes.
Below is a quick overview of the differences between deterministic and nondeterministic functions/expressions.
Continue readingConstraints are an important concept in relational database management systems (RDBMSs). Whenever we design a database, we need to ensure that it will be able to enforce data integrity, by checking or restricting what the user can enter to only data that conforms to the rules of the database. That’s where a constraint can help.
This article explores what SQL constraints are, the various types available, their importance, and how they are implemented.
Continue readingON UPDATE CASCADE is a referential integrity constraint option that we can use in SQL Server when creating foreign keys. It automatically updates foreign key values in child tables when the corresponding primary key in the parent table is updated.
“That query’s not sargable” says one developer to another.
“I know it’s not sargable… would you love it more if it had sargability?” replies the other developer.
“What does sargable mean?” you wonder in silence.
Continue readingIn SQL Server, we can use the DROP_EXISTING option of the CREATE INDEX statement to modify an existing index. It allows us to drop and rebuild an index in one atomic operation. This can be particularly useful when we need to make changes to an index, such as altering its columns, changing its properties, or rebuilding it with different options like fill factor or sort order.
Maintaining data integrity is critical for ensuring the accuracy, consistency, and reliability of data inside a relational database. Among the various strategies to enforce data integrity, user-defined integrity plays a significant role, especially in customized applications and systems where standard integrity constraints may not suffice.
This article explores the concept of user-defined integrity, its importance, how it is implemented, and scenarios where it can become essential.
Continue readingIn relational databases, a one-to-one relationship is a relationship between two tables where each record in the first table corresponds to one and only one record in the second table, and vice versa.
This kind of relationship is less common compared to other relationship types, but it can be useful in certain scenarios. The one-to-many and many-to-many relationships are much more common than the one-to-one relationship in SQL.
Continue readingIn SQL, constraints are essential for maintaining data integrity and ensuring the accuracy and reliability of the data stored within a database. One of the most commonly used constraints in SQL databases is the NOT NULL constraint.
In this article, I provide a quick overview of the NOT NULL constraint, its purpose, syntax, usage, and practical examples.
The DEFAULT constraint is a nifty little feature in SQL databases that simplifies data management by assigning default values to columns in a table. This can help us maintain data integrity in our databases.
Let’s take a look at the DEFAULT constraint.
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 reading