Hugo Kornelis


Stairway to Columnstore Indexes

Stairway to Columnstore Indexes Level 6: Updating and Deleting Data in a Columnstore Index

This level looks in detail at what happens when we update or delete data from a clustered columnstore index, the impact it has on concurrent data access, and how without careful maintenance the efficiency of columnstore indexes can degrade over time.

You rated this post out of 5. Change rating

2024-09-04 (first published: )

9,531 reads

Stairway to Columnstore Indexes

Stairway to Columnstore Indexes Level 12: Clustered or Nonclustered?

The previous levels of this stairway describe details, features, and limitations of columnstore indexes in SQL Server. But they do not answer what should be the first question for every database professional: should columnstore indexes be used in my databases; on what tables should they be used; and should they be clustered or nonclustered columnstore indexes?

5 (1)

You rated this post out of 5. Change rating

2023-10-11 (first published: )

5,011 reads

Stairway to Columnstore Indexes

Stairway to Columnstore Indexes Level 3: Building The Columnstore

The performance increase columnstore indexes grant when reading data from the index is offset by the expensive process required to build the index. In this Stairway level, Hugo Kornelis walks you through the steps SQL Server takes when building (or rebuilding) a columnstore index.

You rated this post out of 5. Change rating

2022-08-10 (first published: )

10,299 reads

Stairway to Columnstore Indexes

Stairway to Columnstore Indexes Level 2: Columnstore Storage

To fully appreciate just how different columnstore indexes are, and why work so well in reporting and online analytical processing (OLAP) workloads, but not for online transaction processing (OLTP), we must first look at the traditional “rowstore” indexes.

5 (2)

You rated this post out of 5. Change rating

2022-06-29 (first published: )

13,282 reads

Stairway to Columnstore Indexes

Stairway to Columnstore Indexes Level 13: Improvements in SQL Server 2016

This stairway series was started in 2015. As such, the focus was on SQL Server 2012 and SQL Server 2014 only. When SQL Server 2016 was released, with lots of improvements in the columnstore technology, I decided to finish the planned levels with the original focus on SQL Server 2012 and 2014, and add one extra level with a brief overview of the improvements available in SQL Server 2016.

5 (2)

You rated this post out of 5. Change rating

2020-07-22 (first published: )

7,345 reads

Stairway to Columnstore Indexes

Stairway to Columnstore Indexes Level 11: Optimizing Queries For Batch Mode (Part 2)

Hugo Kornelis continues his exploration of the types of queries that can end up running in row mode when accessing columnstore indexes. He demonstrates how careful rewriting can often yield a logically equivalent query that runs in batch mode instead, and therefore gains the best possible performance benefit.

You rated this post out of 5. Change rating

2019-03-26 (first published: )

2,655 reads

Stairway to Columnstore Indexes

Stairway to Columnstore Indexes Level 5: Adding New Data To Columnstore Indexes

Earlier levels have shown how Columnstore Indexes work effectively with static data. In most tables however, data is hardly ever static. We are constantly inserting new rows, and updating or deleting existing rows. If you think about what this means for a columnstore index, you will realize that this comes with some unique challenges.

You rated this post out of 5. Change rating

2019-03-26 (first published: )

7,437 reads

Blogs

T-SQL Tuesday #191 Round Up

By

I hosted this month, late as it turns out, but we still had a...

Come See Me at the PASS Data Community Summit

By

I'm speaking on Quantum Computing at the PASS Data Community Summit. It's scheduled for...

SQL Clone at Scale with Striped Backups

By

I had a customer recently reach out to ask if SQL Clone would work...

Read the latest Blogs

Forums

Create an HTML Report on the Status of SQL Server Agent Jobs

By Nisarg Upadhyay

Comments posted to this topic are about the item Create an HTML Report on...

Managing unused indexes

By JasonO

While doing some housekeeping activity on several old but large production databases, I come...

Cloud Performance Considerations

By MichaelT

How important to SQL Server is the speed of the SSD and network latency?...

Visit the forum

Question of the Day

Striped Backup File Limits

In SQL Server 2022, how many backup files can I have in a striped backup?

See possible answers