In this level, we will focus on optimization techniques to apply while building the nonclustered columnstore index, which is available in all versions of SQL Server from 2012 up.
2025-04-23 (first published: 2016-03-16)
5,931 reads
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.
2024-09-04 (first published: 2016-01-20)
9,406 reads
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?
2023-10-11 (first published: 2017-05-24)
5,008 reads
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.
2022-08-10 (first published: 2015-06-10)
10,293 reads
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.
2022-06-29 (first published: 2015-04-29)
13,277 reads
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.
2020-07-22 (first published: 2017-06-07)
7,219 reads
This level introduces of you to the fundamentals of columnstore indexes, introdused in SQL Server 2012 to manage the indexing of very large tables.
2019-12-18 (first published: 2015-02-25)
23,434 reads
In this level, Hugo Kornelis looks at how to rewrite your queries to best take advantage of batch mode.
2019-03-26 (first published: 2016-11-23)
3,699 reads
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.
2019-03-26 (first published: 2017-01-04)
2,653 reads
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.
2019-03-26 (first published: 2015-11-04)
7,426 reads
By gbargsley
Whether you’re a seasoned DBA or just exploring database tools, DBeaver offers a powerful,...
DBAs should never run SSMS under their everyday Windows account If you open SSMS under...
The honeymoon is over, and macOS 26 Tahoe broke the Rosetta 2 emulation layer...
Import-Module ImportExcel # Path to your .sql file $sqlFile = "C:\Data\MyQueries.sql" $excelPath = "C:\Data\SqlExtract_$(Get-Date...
I have noticed sp_executesql also makes a single plan for a stmt with parameter...
If I want to track which login called a stored procedure and use the value in an audit, what function can I use to replace the xxx below?
create procedure AddNewCustomer
@customername varchar(200)
AS
BEGIN
DECLARE @added VARCHAR(100)
SELECT @added = xxx
IF @customername IS NOT NULL
INSERT dbo.Customer
(
CustomerName,
AddedBy
)
VALUES
(@customername, @added)
END See possible answers