If you’re getting an error that reads something like “Cannot alter column ‘c2’ because it is ‘COMPUTED’” in SQL Server, it looks like you’re trying to alter a computed column.
We can’t alter computed columns.
Continue readingTag: how to
Fix “Cannot create index on view. The function yields nondeterministic results” in SQL Server
If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. The function ‘sysdatetime’ yields nondeterministic results…” and so on, in SQL Server, it looks like you’re trying to create an index on a view that returns nondeterministic results.
Indexes can only be created on views that return deterministic results.
Continue readingHow to View your Current SET Options in SQL Server
SQL Server provides various SET options that control the behavior of our session. These options can impact query execution and results. Also, some features in SQL Server rely on certain SET options to be configured in a certain way (for example indexed views).
Getting the current SET options configuration is not as straightforward as one might think. We need to do a bit of trickery.
Let’s look at how to view our current SET options.
Fix “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause” in SQL Server (Error 144)
If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY clause.
We can’t use aggregates or subqueries in the GROUP BY clause.
To fix this issue, remove any aggregate functions or subqueries from your GROUP BY clause.
Fix “Cannot create index on view because its select list does not include a proper use of COUNT_BIG” in SQL Server (Error 10138)
If you’re getting an error that reads something like “Cannot create index on view … because its select list does not include a proper use of COUNT_BIG…” and so on in SQL Server, it could be that you’re trying to create an index on a view that uses the GROUP BY clause, but doesn’t have the COUNT_BIG() function.
If the view contains a GROUP BY clause, then it must also have COUNT_BIG(*).
To fix this issue, try adding COUNT_BIG(*) to your SELECT list.
Fix Error 7999 “Could not find any index…” in SQL Server
If you’re getting SQL Server error 7999 that reads something like “Could not find any index named ‘IX_LastName’ for table ‘Employees’“, it appears that you’re trying to create an index with the DROP_EXISTING argument ON, but there is no existing index of that name.
When we set the DROP_EXISTING argument to ON, SQL Server tries to look for the index to drop before it recreates it with the new definition. If it doesn’t exist, then we get the error.
Fix “Incorrect syntax near the keyword ‘Order'” in SQL Server (Error 156)
If you’re getting an error in SQL Server that reads “Incorrect syntax near the keyword ‘Order’“, it could be because you’re using the word Order (or another reserved keyword) in your SQL code.
The 156 error itself merely indicates a syntax error, so there could be a multitude of reasons you might be getting this error. But when it refers to the keyword 'Order', this could suggest that you’re trying to use the word Order as an identifier (such as a column name, table name, etc).
Fix Error 156 “Incorrect syntax near the keyword ‘ON'” When Using DROP_EXISTING in SQL Server
If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘ON’“, it appears that you’re trying to recreate an existing index, but you’re using the wrong syntax for the DROP_EXISTING argument.
The same error occurs when using the wrong syntax to set DROP_EXISTING to OFF; “Incorrect syntax near the keyword ‘OFF’“.
Either way, to fix this issue, be sure to use the right syntax.
Continue readingFix Error 4145 “An expression of non-boolean type specified in a context where a condition is expected” in SQL Server
If you’re getting SQL Server error 4145 that goes something like “An expression of non-boolean type specified in a context where a condition is expected…“, it’s probably because you’re using a boolean-like syntax in the wrong context.
This error occurs when a non-boolean expression is used in a context where a condition (i.e., something that evaluates to true or false) is expected. This often happens in IF statements, CASE expressions, or WHERE clauses when a value that isn’t a boolean is mistakenly used as a condition.
Even when we do use a boolean value, we can still get this error if we use it in the wrong context.
Continue reading4 Ways to Insert Composite Data in PostgreSQL
When we have a column that’s defined as a composite type in PostgreSQL, we have some options when it comes to inserting data. For example, we can explicitly specify each individual field of the composite type, or we can use a row constructor to insert all fields at once.
Below are four different options for inserting composite values into a column in PostgreSQL.
Continue reading