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.
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 readingCommon table expressions (CTEs) are a feature that we can use in SQL to help simplify complex queries and enhance readability. They can help with things like code readability, performance, recursive queries, and more.
This article explores what CTEs are, their syntax, types, use cases, and best practices, along with examples to help illustrate their usage.
Continue readingIn PostgreSQL, both UNIQUE indexes and UNIQUE constraints can be used to enforce uniqueness on columns.
So you may be wondering; if that’s true, why have both? And are there any circumstances that we should choose one over the other?
Let’s find out.
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 readingStructured Query Language (SQL) is the backbone of relational database management systems (RDBMSs), enabling users to query, manipulate, and define data. One of the most fundamental concepts in SQL, and one that all SQL developers should understand, is the data type.
Whenever we create a column in SQL, we must define its data type. Similarly, when we create a variable, we define its data type.
So, why is the data type so important? Let’s find out.
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.
When using PostgreSQL’s psql command line tool, we can use the \dt command to get a list of tables. This meta-command can display all tables, or just tables that match certain patterns. Below is an overview of how \dt works, its options, and examples of its various uses.
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