A window function leaves Segment and Sequence Project operators in a query plan, and a Sort in front of them. This post tests ROW_NUMBER and a running total on SQL Server 2025, shows the batch mode Window Aggregate that replaces them, and removes the Sort with an index.
The Filter Operator: When SQL Server Reads Everything and Keeps a Few Rows
The Filter operator keeps a few rows after the plan has read and sorted many more. This post shows one on SQL Server 2025 with the actual plan and the logical reads, then rewrites the query so it seeks instead and reads about a fifth as many pages.
Nonclustered Columnstore Index on a Temp Table in SQL Server
A nonclustered columnstore index works on a temp table but not on a table variable. This post loads a temp table, builds the index, and measures the same aggregate query before and after. It also shows the error a table variable raises and when the build cost pays off.
NEWID vs NEWSEQUENTIALID: What Each Does to Your Index
NEWID vs NEWSEQUENTIALID decides whether new uniqueidentifier keys land at random in your clustered index or in order at the end. This post loads 20,000 rows with each default, measures pages, fullness and fragmentation, and shows the limits of NEWSEQUENTIALID. It also answers what happens when you switch the default on a table that already has rows.
Check Index Fragmentation With Row Count in SQL Server
To check index fragmentation with row count, read sys.dm_db_index_physical_stats in SAMPLED mode and keep the page count and row count next to each percentage. This post builds a demo with three tables, lists every index, filters out the tiny ones, loops over every online database, and rebuilds the one index that matters.
How to Know If Index is Forced on Query? – Interview Question of the Week #246
Question: How to Know If Index is Forced on Query?
Answer: Let me tell you a brief story so you understand the context of the question.
Columnstore Indexes Without Aggregation: Do They Still Help?
Columnstore indexes without aggregation still help when a query reads many rows but only a few columns. This post builds a rowstore table and a columnstore copy of one million rows and compares reads, size and execution mode. It also shows the case where a plain b-tree index wins.