WHILE loops are a fundamental construct in T-SQL (and in programming in general) for executing repetitive tasks. Within these loops, the CONTINUE keyword serves as a powerful control flow tool, allowing developers to skip iterations and optimise loop execution.
This article explores the usage and benefits of the CONTINUE keyword in SQL Server WHILE loops.
If 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 readingIn SQL, a deterministic expression or function always returns the same result when given the same input. Conversely, a nondeterministic expression or function may return different results even with the same input due to factors like system time, randomization, or external data changes.
Below is a quick overview of the differences between deterministic and nondeterministic functions/expressions.
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 readingConstraints are an important concept in relational database management systems (RDBMSs). Whenever we design a database, we need to ensure that it will be able to enforce data integrity, by checking or restricting what the user can enter to only data that conforms to the rules of the database. That’s where a constraint can help.
This article explores what SQL constraints are, the various types available, their importance, and how they are implemented.
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.
If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. It contains text, ntext, image, FILESTREAM or xml columns” in SQL Server, it appears that you’re trying to create an index on a view that uses an invalid data type.
Not all data types are supported when indexing views.
Continue reading“That query’s not sargable” says one developer to another.
“I know it’s not sargable… would you love it more if it had sargability?” replies the other developer.
“What does sargable mean?” you wonder in silence.
Continue readingWhen working with PostgreSQL databases, you may sometimes need to clear out all the data from tables that have foreign key relationships. This process, known as truncation, can be tricky when dealing with interconnected tables.
By default, if we try to truncate a table that is referenced by another table via a foreign key constraint, we’ll get an error that looks something like this: “ERROR: cannot truncate a table referenced in a foreign key constraint“.
You may have encountered this before finding this article. However, all is not lost. Below are two options for overcoming this issue.
Continue reading