If you’re getting the “Incorrect syntax…” error in SQL Server when trying to create a filtered index, it could be that you’re using an unsupported operator.
Filtered indexes in SQL Server only support simple comparison operators. If you use an unsupported operator or expression, you’ll likely get the dreaded “Incorrect syntax” error.
To fix, either use a supported operator, or create a view.
Continue readingTransact-SQL (T-SQL) offers a nice set of functions and methods for manipulating strings. Let’s look at some handy techniques that can help us work more efficiently with string data in SQL Server.
Continue readingTemporal tables can be a powerful way to track historical data changes over time in our SQL Server databases. When we’re working with a database that uses temporal tables, we may need to identify all such tables quickly. Or we may simply want to check to see if it has any temporal tables.
Either way, the following SQL queries can be used to return a list of all temporal tables in the database.
Continue readingWhen attempting to truncate a table in PostgreSQL, you might encounter the error “cannot truncate a table referenced in a foreign key constraint“. This is the default behaviour for the TRUNCATE statement whenever you try to truncate a table that is referenced by another table through a foreign key relationship.
If you want to truncate both tables, you can use the CASCADE option of the TRUNCATE statement. Alternatively, you could truncate both tables at the same time.
When working with relational databases, it’s common to encounter situations where you need to delete data from both parent and child tables that are linked by foreign key relationships. This article explores two methods for accomplishing this task in SQL Server, providing detailed examples and explanations for each approach.
Continue readingBy default, if we try to delete a row in the parent table of a referential relationship in SQL Server, we’ll get an error. That’s because the default action is NO ACTION. This means that the delete doesn’t happen, and an error is raised.
But we’re not quite doomed yet. We can use the ON DELETE CASCADE option to ensure that the delete operation does happen, and that no error is returned. This option automatically deletes related records in the child table when a record in the parent table is deleted.
If we’re going to use this option, we need to define it when creating the foreign key. That’s because ON DELETE CASCADE is an optional argument that we can provide when creating the foreign key.
If you’re getting SQL Server error 1988 that reads something like “Cannot rebuild clustered index ‘PK__Employee__7AD04FF1A39ECAB1’ online because it is disabled.“, it’s probably because you’re trying to rebuild a disabled clustered index online.
We can’t rebuild a disabled clustered index with (ONLINE = ON).
In SQL Server, a filtered index is a special type of index that only includes a subset of rows from a table or view, based on specified filter criteria.
This criteria would match the criteria of some of the most commonly run queries on the table, thereby helping to improve the performance of those queries.
Below is an example of creating a filtered index in SQL Server.
Continue readingThe SQL CASE statement is a handy tool that allows us to add conditional logic to our queries. It’s particularly useful when we need to categorize or transform data based on multiple conditions.
In this article, we’ll explore how to use the CASE statement with multiple conditions, providing simple examples that should work across most major relational database management systems (RDBMSs).
If you’re getting SQL Server error Msg 129 that reads “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100“, it appears that you’re trying to set the fillfactor of an index to zero.
While it’s true that zero is the default setting, we can’t actually explicitly set the fillfactor to zero when creating or rebuilding an index.
But all is not lost. Below are two solutions to this problem.
Continue reading