Tag: errors

Fix Error “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions…” in SQL Server (Error 1033)

Posted on by Ian

If you’re getting an error that reads “The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified” in SQL Server, it appears that you’re using the ORDER BY clause in a place that it must also be accompanied by a TOP, OFFSET or FOR XML clause, but you’re not including one of those clauses.

To fix this issue, be sure to include one of those clauses in your query if you need to use the ORDER BY clause.

Continue reading →

Fix “The correlation name … is specified multiple times in a FROM clause” in SQL Server (Error 1011)

Posted on by Ian

If you’re getting an error that reads something like “The correlation name ‘a’ is specified multiple times in a FROM clause’” in SQL Server, it could be that you’re doing a join on two tables, but you’re assigning them the same aliases.

Each table’s alias must be unique.

To fix this issue, be sure to use unique table aliases in your query.

Continue reading →

Fix “Cannot create index on view because the view is not schema bound” in SQL Server (Error 1939)

Posted on by Ian

If you’re getting an error that reads something like “Cannot create index on view ‘MyView’ because the view is not schema bound” in SQL Server, it sounds like you’re trying to create an index on a view that’s not schema bound.

Indexes can only be created on schema bound views.

To fix this issue, define the view as schema bound before creating the index.

Continue reading →

Fix “Computed column … cannot be persisted because the column is non-deterministic” in SQL Server (Error 4936)

Posted on by Ian

If you’re getting an error that reads something like “Computed column ‘c3’ in table ‘t1’ cannot be persisted because the column is non-deterministic” in SQL Server, it appears that you’re trying to persist a computed column that’s nondeterministic.

A nondeterministic function or expression returns a different result for the same input. If a column uses such an expression, then it will be nondeterministic. A computed column must be deterministic.

Continue reading →

Fix “Cannot create index on view because it uses the aggregate COUNT. Use COUNT_BIG instead.” in SQL Server (Error 10138)

Posted on by Ian

If you’re getting an error that reads something like “Cannot create index on view “demo.dbo.SalesSummary” because it uses the aggregate COUNT. Use COUNT_BIG instead” in SQL Server, it’s because you’re trying to create an index on a view that uses the COUNT() function.

Continue reading →

Fix “Computed column … in table … is not allowed to be used in another computed-column definition” (Error 1759)

Posted on by Ian

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 reading →

Fix “ALTER TABLE DROP COLUMN failed because one or more objects access this column” in SQL Server (Error 4922)

Posted on by Ian

If 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 reading →

Fix “Cannot create index on view. It contains text, ntext, image, FILESTREAM or xml columns” in SQL Server (Error 1942)

Posted on by Ian

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 →

Fix “The new name is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server (Error 15335)

Posted on by Ian

If you’re getting an error that reads something like “Error: The new name ‘c1’ is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server, it appears that you’re trying to rename a column with a name that already exists in that table.

Basically, there’s already a column of that name in the table.

Continue reading →