WHILE loops are a fundamental concept in T-SQL programming, allowing us to execute a block of code repeatedly as long as a specified condition is true. Nested WHILE loops take this concept further by placing one WHILE loop inside another, enabling more complex iterations.
This guide will walk you through the basics of nested WHILE loops in SQL Server, complete with simple examples and a demonstration of the BREAK statement.
In SQL, the self join is a join technique where we join a table with itself. Other join types will join a table with another table, but the self join simply joins with itself.
Self joins can be useful when working with hierarchical or recursive data within a single table.
Continue readingIf you’re getting SQL Server error 1987 that reads something like “Cannot alter nonclustered index ‘UQ_Employees_Email’ on table ‘Employees’ because its clustered index is disabled“, it’s probably because you’re trying to rebuild a nonclustered index when the clustered index for the table is disabled.
To fix this issue, either enable/rebuild the table’s clustered index first, then try again, or enable all indexes at once.
The clustered index will typically be the primary key index (unless you’ve specified another clustered index for the table).
Continue readingWhen 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 reading