Understanding Partial Indexes in PostgreSQL

Adding indexes to a table is a well established practice for speeding up regular queries in relational databases such as PostgreSQL. While they can do wonders for query performance, indexes can also take up a lot of disk space.

Today we’re going to be looking at a particular type of index that can help reduce the impact on disk space, and query performance – the partial index.

Continue reading

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

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

Clustered vs Nonclustered Index: What’s the Difference?

Indexes can play a crucial role in optimizing SQL query performance. A well designed index can help a query perform much faster than it otherwise might. While there are many types of indexes, two common index types are clustered and nonclustered indexes.

Understanding the difference between clustered and nonclustered indexes can have a significant impact on our database design and query optimization strategies.

In this article, we’ll take a look at clustered and nonclustered indexes as implemented in SQL Server, explore their characteristics, and examine when to use each type.

Continue reading

Indexed Views in SQL Server: A Complete Guide

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

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

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

A Quick Look at the DROP_EXISTING Option in SQL Server

In SQL Server, we can use the DROP_EXISTING option of the CREATE INDEX statement to modify an existing index. It allows us to drop and rebuild an index in one atomic operation. This can be particularly useful when we need to make changes to an index, such as altering its columns, changing its properties, or rebuilding it with different options like fill factor or sort order.

Continue reading

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 reading

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.

Continue reading