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.
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 readingIf 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 readingFillfactor 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.
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 readingDisabling an index in SQL Server can be a useful technique to improve the performance of bulk insert operations or when you need to perform maintenance on your database.
This article provides the code/syntax required to disable an index in SQL Server.
Continue readingIf you’re getting an error that reads “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified” in SQL Server, it appears that you’re using the ORDER BY clause in a place that it must also be accompanied by a TOP, OFFSET or FOR XML clause, but you’re not including one of those clauses.
To fix this issue, be sure to include one of those clauses in your query if you need to use the ORDER BY clause.
If you’re getting an error that reads something like “The correlation name ‘a’ is specified multiple times in a FROM clause’” in SQL Server, it could be that you’re doing a join on two tables, but you’re assigning them the same aliases.
Each table’s alias must be unique.
To fix this issue, be sure to use unique table aliases in your query.
Continue readingPostgreSQL provides us with a few functions that we can use to convert strings between uppercase and lowercase.
We can convert the whole string to uppercase or lowercase, or we can convert it to initial caps, where the first letter of each word is uppercase and the rest of the word is lowercase.
Continue readingIf you’re getting an error that reads something like “Cannot create index on view ‘MyView’ because the view is not schema bound” in SQL Server, it sounds like you’re trying to create an index on a view that’s not schema bound.
Indexes can only be created on schema bound views.
To fix this issue, define the view as schema bound before creating the index.
Continue reading