All queries run fast against columnstore indexes, right? In this article, Edward Pollack demonstrates some query patterns that don’t perform well and how to get around the issues.… Read more
Uwe Ricken continues his series on heaps. This time he demonstrates a common scenario where the query against a heap is faster than a clustered index.… Read more
Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation.… Read more
In this article of this series, Edward Pollack demonstrates the maintenance of columnstore indexes. He also takes a look at nonclustered columnstore and memory-optimized columnstore indexes.… Read more
In the second article of this series, Edward Pollack demonstrates some ways to design and populate a columnstore index to get even better performance. … Read more
In the second article of this series, Uwe Ricken discusses ways to affect the performance of queries involving heaps, including the TOP operator, compression, and partitioning. … Read more
Most advice you see online about heaps is to avoid them. In this article, Uwe Ricken describes the basics of heaps so that you can determine when heaps are the best choice. … Read more
Microsoft introduced Columnstore with SQL Server 2012 and have improved the feature in every version since then. You may be wondering why they are different than traditional indexes and how they work. In this series, Edward Pollack explains the architecture of Columnstore indexes. In future articles in the series, he will describe best practices for Columnstore.… Read more
Views in SQL Server are used to simplify writing queries and managing security, but’s it’s easy for views to eventually get out of sync with the underlying tables. In this article, Edward Pollack shows how to overcome this problem. … Read more
Microsoft introduced several features called Intelligent Query Processing for SQL Server in 2017 and 2019 that can improve performance without making changes to code. In this article, Greg Larsen demonstrates the feature Batch Mode on Rowstore. … Read more
Microsoft added the In-Memory OLTP (aka memory optimized tables) feature to SQL Server 2014. Operations to this type of table do not need locks, therefore eliminating blocking and deadlocking. In this Article, Monica Rathbun demonstrates how to get started using memory optimized tables.… Read more
Window functions are useful for solving many SQL queries. In this article, Ed Pollack demonstrates how they can be used to analyse baseball winning streaks.… Read more
T-SQL window functions simplify solving many complex queries. In this article, Edward Pollack demonstrates how the functions can be used to find gaps and islands in a dataset. … Read more
The logic for referential integrity can be implemented in application code, but to make sure that it is enforced, include foreign key constraints in the database design instead. In this article, Joe Celko talks about the history of references in SQL and the options available today. … Read more
We participate in auctions all the time even when we don’t realize it. In this article, Joe Celko discusses the many different types of auctions and how they work… Read more
Understanding scoping rules is a basic skill for developers. In this article, Joe Celko gives a bit of the history of scoping in early programming languages and shows how scoping applies to SQL queries as well. … Read more
With SQL Server 2019 on the way, it’s time to begin learning about all the new features. In this article, Dennes Torres demonstrates how to use the new SHORTEST_PATH function that is part of the Graph Database feature. … Read more
Microsoft continues to enhance the performance of SQL Server with new features. In this article, Monica Rathbun explains how to work with columnstore indexes, a different way to store tables that can drastically improve the performance of specific workloads.… Read more