Category: DBMS

Database Management Systems

Why You Might be Getting the 4104 Error When Using the OUTPUT Clause in SQL Server

Posted on by Ian

You may be familiar with SQL Server error 4104 that reads something like “The multi-part identifier “DELETED.Name” could not be bound“, which tells us that the “multi-part identifier” couldn’t be bound.

You may have seen this error when performing joins across tables when using the wrong table prefix for a column or using the table name instead of its alias. But that’s not the only place we can get this error.

If you’re getting this error while using the OUTPUT clause (which we can use during INSERT, UPDATE, DELETE, or MERGE operations), then it could be that you’ve accidentally used the wrong prefix for the affected column/s.

Continue reading →

4 Ways to Get the Last SQL Server Startup Time using T-SQL

Posted on by Ian

Occasionally we might want to check to see how long SQL Server has been running uninterrupted. For example, how long has SQL Server been running? Or when was the last restart?

Fortunately there are a multitude of ways we can go about this. Some of these methods involve checking the event viewer or going through the SMSS GUI. But here are four ways we can do it with a T-SQL query.

Continue reading →

A Possible Cause for the 102 Error When Running a Subquery in SQL Server

Posted on by Ian

If you’re running a subquery in SQL Server, but you’re getting error 102 that reads something like “Incorrect syntax near ‘;’“, there could be any number of reasons, because this is a generic error that simply means wrong syntax.

But one possible cause could be that you haven’t declared an alias for the subquery.

Continue reading →

How to List all Domains in PostgreSQL

Posted on by Ian

In PostgreSQL, domains are basically data types with optional constraints. We can create them as a kind of user-defined data type, and then reuse them in our columns going forward.

As with any user-created object, we sometimes need to see a list of existing domains in a PostgreSQL database. Listing all domains can be useful for database management, documentation, or troubleshooting.

This article will show you how to retrieve a list of all domains in your PostgreSQL database.

Continue reading →

How to Tell Whether a Given Row was Inserted, Updated, or Deleted During a MERGE in SQL Server

Posted on by Ian

If you’re using the MERGE statement in SQL Server to merge data between tables, you may or may not be aware that the statement gives us the ability to check which rows were affected by the MERGE operation, as well as how they were affected.

To get this info, we can use the OUTPUT clause along with the $action argument.

Continue reading →

3 Ways to Find a Table’s Primary Key Constraint Name in SQL Server

Posted on by Ian

Primary keys are fundamental to relational database design, ensuring each row in a table can be uniquely identified. They help to maintain data integrity in our databases.

There are many ways to find the primary key column in SQL Server, but sometimes we might need to identify the name of the primary key constraint itself.

Below are three examples of how we can do this.

Continue reading →

Possible Reason You’re Getting an Error When Using HANDLER … LAST or PREV in MySQL

Posted on by Ian

If you’re getting an error when specifying LAST or PREV for MySQL’s HANDLER statement, it could be that you’re trying to do a table scan using the index syntax.

While the HANDLER statement does accept the LAST and PREV options, we can only use them with an index.

So to fix this issue, be sure to specify an index when using the LAST and PREV options.

Continue reading →

Understanding the Various ON DELETE Options in SQL Server Foreign Keys

Posted on by Ian

It’s widely understood among SQL Server developers that foreign keys can prevent us from deleting a row from the parent table if there’s child table that references the key in that row.

But did you know that we can specify a different course of action in such cases?

For example, we could configure our foreign key to delete the child as well. Or we could set it to NULL, or to its default value.

These options are all available, thanks to the ON DELETE clause.

Continue reading →