Category: DBMS

Database Management Systems

Prepared Statements in PostgreSQL: A Complete Guide

Posted on by Ian

Prepared statements are a feature in PostgreSQL that can improve performance and security when executing repetitive SQL queries.

By creating a prepared statement, the server can parse and plan the query once and reuse it multiple times, leading to faster execution. Prepared statements also help in preventing SQL injection attacks by safely handling user input.

In this article, we’ll explore prepared statements in PostgreSQL, discuss how to create and use them, and cover other important considerations such as query planning and cleaning up statements.

Continue reading →

Fix “Computed column … cannot be persisted because the column is non-deterministic” in SQL Server (Error 4936)

Posted on by Ian

If you’re getting an error that reads something like “Computed column ‘c3’ in table ‘t1’ cannot be persisted because the column is non-deterministic” in SQL Server, it appears that you’re trying to persist a computed column that’s nondeterministic.

A nondeterministic function or expression returns a different result for the same input. If a column uses such an expression, then it will be nondeterministic. A computed column must be deterministic.

Continue reading →

Understanding Partial Indexes in PostgreSQL

Posted on by Ian

Adding indexes to a table is a well established practice for speeding up regular queries in relational databases such as PostgreSQL. While they can do wonders for query performance, indexes can also take up a lot of disk space.

Today we’re going to be looking at a particular type of index that can help reduce the impact on disk space, and query performance – the partial index.

Continue reading →

4 Ways to Create a UNIQUE Constraint in SQL Server

Posted on by Ian

A UNIQUE constraint is a rule that we can apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows.

In SQL Server we have a few options when it comes to creating a UNIQUE constraint. But it’s usually done when we create the table or alter it. That is, we include the constraint code in the CREATE TABLE statement or the ALTER TABLE statement.

We can also create a UNIQUE index, which can be used in place of the previous methods, but can have the same effect.

Let’s check out several ways to create a UNIQUE constraint in SQL Server.

Continue reading →

Fix “Cannot create index on view because it uses the aggregate COUNT. Use COUNT_BIG instead.” in SQL Server (Error 10138)

Posted on by Ian

If you’re getting an error that reads something like “Cannot create index on view “demo.dbo.SalesSummary” because it uses the aggregate COUNT. Use COUNT_BIG instead” in SQL Server, it’s because you’re trying to create an index on a view that uses the COUNT() function.

Continue reading →

Clustered vs Nonclustered Index: What’s the Difference?

Posted on by Ian

Indexes can play a crucial role in optimizing SQL query performance. A well designed index can help a query perform much faster than it otherwise might. While there are many types of indexes, two common index types are clustered and nonclustered indexes.

Understanding the difference between clustered and nonclustered indexes can have a significant impact on our database design and query optimization strategies.

In this article, we’ll take a look at clustered and nonclustered indexes as implemented in SQL Server, explore their characteristics, and examine when to use each type.

Continue reading →

Understanding the CONTINUE Keyword in SQL Server WHILE Loops

Posted on by Ian

WHILE loops are a fundamental construct in T-SQL (and in programming in general) for executing repetitive tasks. Within these loops, the CONTINUE keyword serves as a powerful control flow tool, allowing developers to skip iterations and optimise loop execution.

This article explores the usage and benefits of the CONTINUE keyword in SQL Server WHILE loops.

Continue reading →

Fix “Computed column … in table … is not allowed to be used in another computed-column definition” (Error 1759)

Posted on by Ian

If you’re getting an error that reads something like “Computed column ‘c2’ in table ‘t1’ is not allowed to be used in another computed-column definition” in SQL Server, it appears that you’re trying to create a computed column that uses another computed column.

We can’t use other computed columns in our computed column definitions.

Continue reading →