How to Create a UNIQUE Constraint in MySQL

In MySQL, a UNIQUE constraint is a constraint type that ensures that all values in a column or a group of columns are distinct from each other. In other words, all values that go into the column or group of columns must be unique.

UNIQUE constraints can be applied whenever we want to prevent duplicate entries in specific columns without making them a primary key.

Continue reading

JOIN ON vs USING vs NATURAL JOIN: What’s the Difference?

Probably the most common way to join tables in SQL is with the ON clause. But that’s not the only way.

We can also join with the USING clause, which can be more concise, while providing the same or similar results. And there’s also the concept of a natural join, which is more concise again.

Let’s take a look at these three join options and compare them side by side.

Continue reading

Using the OUTPUT Clause To Log the Result of a MERGE Operation in SQL Server

The MERGE statement is a versatile feature introduced in SQL Server 2008 that allows the combination of INSERT, UPDATE, and DELETE operations into a single statement. When paired with the OUTPUT clause, it becomes even more powerful by enabling us to capture the results of these actions, providing us visibility into what changes occurred during the merge.

In this article, we’ll walk through an example that uses the OUTPUT clause during a MERGE operation in SQL Server.

Continue reading

Changing the Default Fill Factor Doesn’t Work? Check These Two Things.

If you’ve gone through the steps for changing the default fill factor in SQL Server, but it doesn’t seem to be taking effect on newly created indexes, it could be because you’ve missed one or two crucial steps.

This issue can also be seen by comparing the value and value_in_use columns when querying the sys.configuration view or using sp_configure to view the current setting.

Continue reading

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

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

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

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

7 SQL Statement Examples for Beginners

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Whether you’re just starting your journey in data management or looking to refresh your skills, understanding the basic SQL statements is crucial.

This article will walk you through seven fundamental SQL statement examples that are pretty much standard across most major Relational Database Management Systems (RDBMSs).

Continue reading