What is a Synonym in SQL Server?

Posted on by Ian

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).

Continue reading →

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

Posted on by Ian

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).

Continue reading →

Understanding Filtered Indexes in SQL

Posted on by Ian

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.

Continue reading →

Using SQL INSERT with a Subquery

Posted on by Ian

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.

Continue reading →

Fix Error 1505 “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” in SQL Server

Posted on by Ian

If you’re getting an error that reads something like “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” it’s probably because you’re trying to create a UNIQUE constraint on a column that already contains duplicate values.

Continue reading →

What is a UNIQUE Constraint?

Posted on by Ian

A UNIQUE constraint is a rule we apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows. This means that no two rows in the table can have the same value(s) in the column(s) where we’ve applied the UNIQUE constraint. It’s an essential tool for maintaining data integrity and preventing duplicate entries in our database.

Continue reading →

How to Create an Array Column in PostgreSQL

Posted on by Ian

PostgreSQL allows us to create arrays and store them in a database column. When we do this, we can use various array related tools to retrieve data from such arrays, as well as manipulate the data within them.

We do need to define the column as an array column though. If we don’t do this, we will likely run into trouble when we want to retrieve data from the array. For example, we can’t just store an array as the text type and then expect to be able to use subscripts to refer to its individual elements.

Continue reading →

Fix Error “Drop table operation failed on table … because it is not a supported operation on system-versioned temporal tables” in SQL Server

Posted on by Ian

If you’re getting an error that reads something like “Drop table operation failed on table ‘db.dbo.TableName’ because it is not a supported operation on system-versioned temporal tables” in SQL Server, it’s probably because you’re trying to drop a temporal table that still uses system-versioning.

In SQL Server, if a table is defined as a temporal table, we can’t drop it while it’s using system-versioning.

If you really want to drop the table, turn off system-versioning first, then try again.

Continue reading →