Predicates in SQL are often complex and difficult to understand. In this article, Joe Celko explains the logic behind a few of the predicates: EXISTS, SOME, ANY and ALL.… Read more
Database objects often have references to external databases which makes continuous integration problematic. In this article Liz Baron and Sebastian Meine demonstrate a solution.… Read more
Someone recently asked me which queries are ad hoc in SQL Server. An ad hoc query is a single query not included in a stored procedure and not parameterized or prepared. Depending on the server settings, SQL Server can parameterize some statements initially written as ad hoc queries. Ad hoc doesn’t mean dynamic. Here’s a … Read more
This article is an interesting approach to solving a data transformation problem in SQL and Scala. Shel Burkow uses a SQL execution plan for software design.… Read more
When inserting rows into a heap, SQL Server must sometimes update the PFS page which can PFS contention and affect performance. In this article, Uwe Ricken demonstrates how to measure PFS contention.… Read more
To prevent data change anomalies, a database should be normalized. Did you know that there are 10 normal forms? In this article, Joe Celko reviews normalizing databases including commonly used normal forms.… Read more
The SQL Server 2016 dynamic data masking feature may seem like a great way to obfuscate data for downstream systems like dev and QA. Joe Obbish shows us that the data can be “unmasked” with T-SQL statements, so it’s not secure against anyone who can write their own queries.… Read more
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
