In SQL (and computer science in general), a Boolean expression is a logical statement that evaluates to either true or false. Some RDBMSs provide a boolean data type that can store values that are either true or false (or unknown). PostgreSQL is one such RDBMS.
In PostgreSQL, the Boolean type is called boolean and it uses 1 byte.
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.
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.
Continue readingWe’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.
Continue readingWhen 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.
Continue readingIn 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.
Continue readingWhen 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.
Continue readingSQL 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.
The WHERE clause is a fundamental part of SQL queries that allows us to filter data based on specific conditions. Understanding how to use various operators within the WHERE clause can significantly enhance our ability to retrieve precisely the data we need.
This article explores some of the operators that are most commonly used with the WHERE clause.
In SQL, the self join is a join technique where we join a table with itself. Other join types will join a table with another table, but the self join simply joins with itself.
Self joins can be useful when working with hierarchical or recursive data within a single table.
Continue reading