How to Make SELECT COUNT(*) Queries Crazy Fast.sql

How to Make SELECT COUNT(*) Queries Crazy Fast.sql

USE StackOverflow;
GO
DropIndexes;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO

/* Check the table's size: */
sp_BlitzIndex @TableName = 'Votes';
GO


/* Turn on actual execution plans and: */
SET STATISTICS IO, TIME ON;
GO


/* 1: Plain ol' SELECT with only a clustered rowstore index, compatibility level 2017 & prior */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO

/* 2: Compatibility level 2019 */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO



/* 3: Add nonclustered rowstore indexes, but use 2017 & prior's row mode */
CREATE INDEX IX_PostId ON dbo.Votes(PostId);
GO
CREATE INDEX IX_UserId ON dbo.Votes(UserId);
GO
CREATE INDEX IX_BountyAmount ON dbo.Votes(BountyAmount);
GO
CREATE INDEX IX_VoteTypeId ON dbo.Votes(VoteTypeId);
GO
CREATE INDEX IX_CreationDate ON dbo.Votes(CreationDate);
GO
/* What are the sizes of each index?
  Turn OFF actual plans to run this: */
sp_BlitzIndex @TableName = 'Votes';
GO


ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO


/* 4: 2019's batch mode with nonclustered rowstore indexes */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 150;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO


/* 5: Add nonclustered columnstore indexes */
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_BountyAmount ON dbo.Votes(BountyAmount);
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
SELECT COUNT(*) FROM dbo.Votes;
GO