When used in the FROM clause, a SQL subquery creates a temporary table that can be queried like any other table. These queries are sometimes called derived tables or table expressions because the outer query uses the results of the subquery as a data source.
In this article we look at three different examples of SQL subqueries that are placed into the FROM clause.
If you’ve got a table that has a UNIQUE constraint, there may come a time where you need to remove it. Whether it’s a permanent thing or temporary, you can easily delete the constraint using the following method.
If you’re getting an error that reads something like “Cannot schema bind view ‘vEmployees’ because name ‘Employees’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself” it could be that you’re trying to create a schema bound view, but you’re not using a two-part format for names (such as the table names within the view).
Continue readingIn PostgreSQL, a composite type is a data type that represents the structure of a row or record. It’s basically a list of field names and their data types.
We can use composite types in many of the same ways we can use simple types, such as in table columns.
Below are two ways of creating composite types in PostgreSQL.
Continue readingEver wanted to populate a table with data from another table? That’s where the INSERT statement with a subquery comes in handy. Using this method, we can insert the full contents of another table, or we can insert just a select number of rows or columns based on certain criteria.
Below are four examples of using the INSERT statement with a subquery, with each one slightly more complex than the previous.
If you’re getting an error that reads something like “The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name” it’s probably because you’re trying to create a UNIQUE constraint on a column that already contains duplicate values.
PostgreSQL allows us to create arrays and store them in a database column. When we do this, we can use various array related tools to retrieve data from such arrays, as well as manipulate the data within them.
We do need to define the column as an array column though. If we don’t do this, we will likely run into trouble when we want to retrieve data from the array. For example, we can’t just store an array as the text type and then expect to be able to use subscripts to refer to its individual elements.
Continue readingIf you’re getting an error that reads something like “Drop table operation failed on table ‘db.dbo.TableName’ because it is not a supported operation on system-versioned temporal tables” in SQL Server, it’s probably because you’re trying to drop a temporal table that still uses system-versioning.
In SQL Server, if a table is defined as a temporal table, we can’t drop it while it’s using system-versioning.
If you really want to drop the table, turn off system-versioning first, then try again.
Continue readingWhen working with databases, performance can be very important. This is especially true in production environments where the end users expect their queries and reports to be generated within seconds (or even milliseconds).
While blistering fast queries may be the norm with smaller datasets, as our databases grow larger and more complex, it can become much more of a challenge to keep our queries nice and snappy. When working with smaller datasets, it’s often possible to get lightning speed results even when not optimizing for speed. But as the datasets grow larger, we need more efficient tools and techniques to retrieve and manipulate data. One such tool is the index.
Continue readingTemporal tables, introduced in SQL Server 2016, provide a powerful way to track historical data changes. However, there may come a time when you need to remove a temporal table from your database.
This article will guide you through the process of dropping a temporal table in SQL Server.
Continue reading