Ian

Understanding the Difference Between SCALE(), MIN_SCALE() and TRIM_SCALE() in PostgreSQL

by

Anyone who looks at PostgreSQL’s list of mathematical functions will notice that there are some functions that have “scale” in their name. In particular, scale(), min_scale(), and trim_scale().

While these functions all have a similar name, they each have a different purpose. That said, they’re all related to the scale of the given number. The scale is the number of digits to the right of the decimal point in a number. For example, the number 1.95 has a scale of 2, and 1.958 has a scale of 3.

Read more

Fix Error 1987 “Cannot alter nonclustered index … because its clustered index is disabled” in SQL Server

by

If you’re getting SQL Server error 1987 that reads something like “Cannot alter nonclustered index ‘UQ_Employees_Email’ on table ‘Employees’ because its clustered index is disabled“, it’s probably because you’re trying to rebuild a nonclustered index when the clustered index for the table is disabled.

To fix this issue, either enable/rebuild the table’s clustered index first, then try again, or enable all indexes at once.

The clustered index will typically be the primary key index (unless you’ve specified another clustered index for the table).

Read more

Examples of SQL Subqueries in the FROM Clause

by

When used in the FROM clause, a SQL subquery creates a temporary table that can be queried like any other table. These queries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.

In this article we look at three different examples of SQL subqueries that are placed into the FROM clause.

Read more

What is a Synonym in SQL Server?

by

In SQL Server, a synonym is a database object that provides an alternative name for another database object, such as a table, view, stored procedure, or function. Synonyms act as aliases, allowing us to reference objects using different names without changing the underlying object itself.

The underlying object that the synonym references is typically referred to the base object. Either way, the synonym allows us to reference the base object without actually referring to its name (we refer to the synonym’s name instead).

Read more

Fix Error 4512 “Cannot schema bind view” in SQL Server Due to “two-part format” Issue

by

If you’re getting an error that reads something like “Cannot schema bind view ‘vEmployees’ because name ‘Employees’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself” it could be that you’re trying to create a schema bound view, but you’re not using a two-part format for names (such as the table names within the view).

Read more

Understanding Filtered Indexes in SQL

by

In SQL, indexing can be a great way to improve query performance across our database. We can create indexes to support the most commonly used queries, and these indexes can help our queries run much faster.

But it’s not always a matter of just “adding an index” and leaving it at that. If we really want the best bang for our buck, we usually need to look at how best to implement the index. There are many decisions that go into designing the best index for the job. And one such decision might be, should this be a filtered index?

But what exactly is a filtered index, and how can it improve our database efficiency?

Let’s find out.

Read more

2 Ways of Creating a Composite Type in PostgreSQL

by

In PostgreSQL, a composite type is a data type that represents the structure of a row or record. It’s basically a list of field names and their data types.

We can use composite types in many of the same ways we can use simple types, such as in table columns.

Below are two ways of creating composite types in PostgreSQL.

Read more

Using SQL INSERT with a Subquery

by

Ever wanted to populate a table with data from another table? That’s where the INSERT statement with a subquery comes in handy. Using this method, we can insert the full contents of another table, or we can insert just a select number of rows or columns based on certain criteria.

Below are four examples of using the INSERT statement with a subquery, with each one slightly more complex than the previous.

Read more