3 Ways to Concatenate Strings in PostgreSQL

When working with databases (and software in general), string concatenation is the operation of joining character strings end-to-end. For example if we have two words, we can combine them into one.

PostgreSQL provides us with multiple ways to concatenate strings. Below are two functions and one operator that we can use to concatenate strings in PostgreSQL.

Continue reading

Fix “The objects … in the FROM clause have the same exposed names” in SQL Server (Error 1013)

If you’re getting an error that reads something like “The objects “employees” and “employees” in the FROM clause have the same exposed names. Use correlation names to distinguish them’” in SQL Server, it could be that you’re trying to do a self-join without assigning table aliases.

When doing self-joins, we need to provide aliases for the tables so that SQL Server can distinguish between each instance.

To fix this issue, be sure to use unique table aliases in your query.

Continue reading

How to Log Data Changes with the SQL Server OUTPUT Clause

SQL Server has an OUTPUT clause that we can use any time we do an INSERT, UPDATE, DELETE, or MERGE operation. It allows us to retrieve information from modified rows during such operations. This can be especially useful for auditing, logging, or understanding the impact of database changes without needing an additional query.

Let’s look at how the OUTPUT clause can help us log the data changes in our database.

Continue reading

Using a CTE with a DELETE Statement in SQL Server to Remove Duplicates

Common Table Expressions (CTEs) are a nifty SQL feature that allow us to define a temporary result set, which can then be used in subsequent queries, including DELETE statements. In the context of removing duplicate data, a CTE can be helpful when we want to identify and remove only the duplicated rows, keeping one version of each unique record.

Let’s go through a step-by-step example of using a CTE with a DELETE statement to remove duplicate data.

Continue reading

Fix “Create View or Function failed because no column name was specified for column” in SQL Server (Error 4511)

If you’re getting an error that reads “Create View or Function failed because no column name was specified…” in SQL Server, perhaps you’re using a function or an arithmetic expression in the SELECT list, but you haven’t provided a name for the column.

As the error message alludes to, you need to provide a column name when doing stuff like that.

Continue reading

Fix “Cannot create index on view because it contains the DISTINCT keyword” in SQL Server (Error 10100)

If you’re getting an error that reads “Cannot create index on view “demo.dbo.vDistinctPrice” because it contains the DISTINCT keyword…” etc etc in SQL Server, it appears that you’re trying to create an index on a view that contains the DISTINCT keyword.

As the error message alludes to, this is not allowed.

Continue reading

2 Ways to Check the Default Fill Factor in SQL Server

Fillfactor is an option that allows us to fine tune index data storage and performance in SQL Server. It determines the percentage of space on each leaf-level page to be filled with data, therefore reserving the remainder on each page as free space for future growth.

The default fillfactor can be set as a configuration option. SQL Server sets this value to 0, and so this is what’s used when we create an index without specifying a fillfactor. However, it’s possible to change this value, and so at some stage we might want to check to see what the current value is.

Below are two ways to check the fill factor configuration option.

Continue reading

Fixing Error 258 “Cannot call methods on nvarchar(max)” When a Subquery Uses FOR XML in SQL Server

If you’re running a subquery that uses FOR XML to produce XML in SQL Server, but you’re getting error 258, which reads “Cannot call methods on nvarchar(max)” it might be that you’re trying to query the XML in the outer query, but your inner query isn’t outputting the XML as an actual xml type.

The FOR XML result must be an xml type in order to process the result on the server.

To fix this issue, make sure the subquery outputs the result using the xml data type.

Continue reading