what is

What is Entity Integrity?

by

When working with relational databases, data accuracy and consistency are paramount. When we talk about maintaining the integrity of the data within a relational database, several types of integrity come into play. One of the most fundamental is entity integrity.

This concept is crucial for ensuring that each record within a database table is uniquely identifiable, which in turn supports the reliability of the data. In this article, we will look into what entity integrity is, why it matters, and how it is implemented in practice. We’ll also look at an example to clarify the concept further.

Read more

Understanding Fillfactor in SQL Server

by

One of the various options we have when creating or rebuilding indexes in SQL Server is specifying a fillfactor. If we create or rebuild an index without specifying a fillfactor, then the default fillfactor is applied.

In some cases, using the default fillfactor may be fine, even ideal. In other cases it could be less than ideal, even terrible.

Let’s look at what fillfactor is, how it works, and how we can use it to enhance database performance.

Read more

Understanding the BREAK Keyword in SQL Server WHILE Loops

by

WHILE loops are a powerful feature in most programming languages that allow developers to execute a block of code repeatedly as long as a specified condition is true. However, there are times when we may need to exit a loop prematurely based on certain criteria. SQL Server caters for this possibility with the BREAK keyword.

In this article, we’ll explore how to effectively use the BREAK keyword within WHILE loops in SQL Server.

Read more

What is a Clustered Index in SQL Server?

by

There are around a dozen types of index in SQL Server, but the most commonly used index type would have to be the clustered index. By default, a clustered index is automatically created when we create a primary key, and so if your tables have primary keys, then there’s a pretty good chance you’ve got clustered indexes all over your database.

But what exactly is a clustered index?

Let’s delve into what a clustered index is, how it works, its relationship with primary keys, and why it’s so important for our database performance.

Read more

What is a Nonclustered Index in SQL Server?

by

We’ll often hear SQL Server developers talk about adding indexes to a table to improve the performance of some of the more heavily run queries. Such queries could be quite complex, and/or search through large tables, perhaps with lots of joins, and generally take a long time to complete.

But it’s not just a matter of saying “add an index” and being done with it. We need to decide how to design the index, and even before that, what type of index to add.

Two of the most commonly used index types in SQL Server are the clustered index and the nonclustered index.

In this article, we’ll explore what a nonclustered index is, how it works, and when we should use it to enhance our database performance.

Read more

What is a Composite Index in SQL?

by

When looking at ways to fix slow running SQL queries, one of the first things we might do is look at what indexes are available to support the query. If none are available, then we might look at creating one. And if there is an index, perhaps it doesn’t cater for the query as well as it could.

Indexes are often created on a single column, but this isn’t the only way to create an index. We can also create indexes on multiple columns. These are typically known as composite indexes.

Read more

What is a Heap in SQL Server?

by

In SQL Server, a heap is a table without a clustered index. Unlike tables with clustered indexes, which sort data in a specific order, heaps store data in no particular order. That’s because the clustered index is what determines how the table is stored and sorted (it’s sorted on the clustered index’s key column).

If there’s no clustered index, then data is initially stored in the order in which the rows are inserted, although the database engine may change this in order to store the rows more efficiently.

Read more

Understanding the Correlated Subquery in SQL

by

When writing SQL queries, we sometimes find ourselves including a subquery in the query in order to get the results we want. Sometimes the subquery we include could be taken out of that query and run by itself to return its own independent result set. But not the correlated subquery.

A correlated subquery relies on the outer query for its data. We can’t simply move it outside of the outer query and expect it to work.

Let’s take a look the correlated subquery in SQL and observe it in action with a few examples.

Read more

The Difference Between INNER and LEFT JOINs in SQL

by

SQL joins are fundamental operations in relational databases, allowing us to combine data from multiple tables. Two of the most commonly used join types are INNER JOIN and LEFT JOIN. Understanding the difference between these joins is crucial if you intend to use joins in your queries.

In this article, we’ll look at the difference between the INNER JOIN and LEFT JOIN using a simple example.

Read more