SQL subqueries are like little helpers that fetch data for our main query. They’re super handy when we need to filter our results based on some other data in our database.
Probably the most common placement of a subquery in SQL is in the WHERE clause. Placing a subquery in the WHERE clause allows us to compare a column against a set of rows that match a certain criteria.
Let’s look at three quick examples of subqueries in the WHERE clause.
If you’re getting an error that reads something like “Computed column ‘c3’ in table ‘t1’ cannot be persisted because the column is non-deterministic” in SQL Server, it appears that you’re trying to persist a computed column that’s nondeterministic.
A nondeterministic function or expression returns a different result for the same input. If a column uses such an expression, then it will be nondeterministic. A computed column must be deterministic.
Continue readingA UNIQUE constraint is a rule that we can apply to one or more columns in a database table to ensure that the values in those columns are unique across all rows.
In SQL Server we have a few options when it comes to creating a UNIQUE constraint. But it’s usually done when we create the table or alter it. That is, we include the constraint code in the CREATE TABLE statement or the ALTER TABLE statement.
We can also create a UNIQUE index, which can be used in place of the previous methods, but can have the same effect.
Let’s check out several ways to create a UNIQUE constraint in SQL Server.
If you’re getting an error that reads something like “Cannot create index on view “demo.dbo.SalesSummary” because it uses the aggregate COUNT. Use COUNT_BIG instead” in SQL Server, it’s because you’re trying to create an index on a view that uses the COUNT() function.
PostgreSQL provides us with various string functions, including some that allow us to trim a given string on both sides or a given side.
Below are four functions that we can use to trim strings in PostgreSQL.
Continue readingIf you’re getting an error that reads something like “Cannot DROP TABLE ‘dbo.t1’ because it is being referenced by object ‘v1’” in SQL Server, it looks like you’re trying to drop a table that’s referenced by a schema-bound object (such as a schema-bound view).
Continue readingIf you’re getting an error that reads something like “Computed column ‘c2’ in table ‘t1’ is not allowed to be used in another computed-column definition” in SQL Server, it appears that you’re trying to create a computed column that uses another computed column.
We can’t use other computed columns in our computed column definitions.
Continue readingIndexed views can significantly enhance the performance of complex queries in SQL Server. However, they come with several prerequisites and considerations that need to be carefully understood before implementation.
In this article, I provide an overview of indexed views, discussing their utility, prerequisites, and best practices, along with performance considerations and a simple example.
Continue readingIf you’re getting an error that reads something like “ALTER TABLE DROP COLUMN c2 failed because one or more objects access this column” in SQL Server, it looks like you’re trying to drop a column that’s accessed by another object.
This can happen when we try to drop a column that’s referenced by a schema-bound view. It can happen even when we only try to alter the columns too.
Continue readingON UPDATE CASCADE is a referential integrity constraint option that we can use in SQL Server when creating foreign keys. It automatically updates foreign key values in child tables when the corresponding primary key in the parent table is updated.