SET NOCOUNT ON;
DROP TABLE BillingInfo;
CREATE TABLE BillingInfo(
ID INT IDENTITY,
BillingDate DATETIME,
BillingAmt MONEY,
BillingDesc varchar(500));
 
DECLARE @I INT;
DECLARE @BD INT;
SET @I = 0;
WHILE @I < 1000000 
BEGIN
  SET @I = @I + 1;
  SET @BD=CAST(RAND()*10000 AS INT)%3650;
  INSERT BillingInfo (BillingDate, BillingAmt) 
  VALUES (DATEADD(DD,@BD,
    CAST('1999/01/01' AS DATETIME)),
    RAND()*5000);
END 
 
ALTER TABLE BillingInfo 
  ADD  CONSTRAINT [PK_BillingInfo_ID] 
  PRIMARY KEY CLUSTERED (ID);
 
CREATE NONCLUSTERED INDEX IX_BillingDate
  ON dbo.BillingInfo(BillingDate);

CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
AS
SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @BeginDate AND @EndDate;

SET STATISTICS IO ON;
DBCC FREEPROCCACHE;
EXEC dbo.DisplayBillingInfo 
  @BeginDate = '1999-01-01',  
  @EndDate  = '1999-12-31';  
  
EXEC dbo.DisplayBillingInfo 
  @BeginDate = '2005-01-01',  
  @EndDate  = '2005-01-03';

Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
Table 'BillingInfo'. Scan count 1, logical reads 3593, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO ON;
DBCC FREEPROCCACHE;
EXEC dbo.DisplayBillingInfo 
  @BeginDate = '2005-01-01',  
  @EndDate  = '2005-01-03';
  
EXEC dbo.DisplayBillingInfo 
  @BeginDate = '1999-01-01',  
  @EndDate  = '1999-12-31';

Table 'BillingInfo'. Scan count 1, logical reads 2965, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
 
Table 'BillingInfo'. Scan count 1, logical reads 337040, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server 2022 is on its way and there are a lot of new features that could be of interest. Check out this summary of new features and things to investigate. 

Scott Klein and Joey D'Antoni provide an overview of the SQL Server 2016 features and enhancements, teasing up many of the features discussed in the other videos as well as covered in the training kit.
 

CREATE PROCEDURE sp_PagedItems
(
 @Page int,
 @RecsPerPage int
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
Name varchar(50),
Price currency
)


-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
 SELECT COUNT(*)
 FROM #TempItems TI
 WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec


-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Query Store is enabled by default in SQL Server 2022 

Cool Stuff in SQL Server 2022 – IS DISTINCT FROM 

SQL Server 2022 community technology preview 2.1 is now available