Database Management Systems
By default, SQL Server creates indexes automatically when we do things like create certain constraints. We also have the option of creating indexes separately for our own (usually performance related) reasons. Either way, there may be times where we need to check what indexes we have in our database.
In this article, we’ll explore four ways to retrieve information about all indexes in a SQL Server database.
Continue readingIf you’re getting SQL Server error 1909 that reads something like “Cannot use duplicate column names in index. Column name ‘FirstName’ listed more than once“, it appears that you’re trying to create an index, but you’re listing the same column multiple times.
Each column can only be listed once in the index definition.
To fix this issue, make sure each column is listed only once.
Continue readingIn SQL Server, “schema-bound” refers to a specific feature that ensures a SQL object, such as a view or function, is tightly linked to the schema of the underlying tables or other database objects it references. When a view or function is schema-bound, the underlying tables or objects cannot be altered in a way that would break the view or function’s dependencies.
Continue readingWhen 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 readingIf 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 readingSQL 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 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 reading