Probably the most widely known key type in SQL is the primary key, which is chosen to uniquely identify each row in a table. Perhaps next is the foreign key, which is used to establish a relationship between tables.
But there are more key types than this, and the differences between them can be subtle, but important. Here we’ll look at nine of the various key types in SQL.
Continue readingIf you’ve been working with prepared statements in PostgreSQL, you may get to a point where you no longer need one or more of them. In that case, you might want to remove them, or delete them, drop them, or however you want to say it.
Fortunately, this is one of the quickest, easiest things to do in PostgreSQL.
Continue readingA common table expression (CTE) in SQL Server is used to create a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can simplify complex queries and improve readability. However, there are specific rules for using the ORDER BY clause inside a CTE.
If you’re getting an error that reads something like “Column names in each table must be unique. Column name ‘c1’ in table ‘t1’ is specified more than once” in SQL Server, it appears that you’re trying to create a table with duplicate column names, or you’re trying to add a column to a table that already has a column of that name.
Column names must be unique within each table.
Continue readingError 156 in SQL Server is a generic error that doesn’t tell us much, other than the fact that we’re using the wrong syntax.
But if you’re running a subquery, and you’re getting error 156, which reads “Incorrect syntax near the keyword ‘WHERE’.” or whatever keyword it’s near, it could be that you haven’t declared an alias for the subquery.
Continue readingIn 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.
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 readingThe 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.
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.
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.