EstimateRowsWithoutRowGoal example.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
Setup:
    Download BabbyNames.bak.zip (43 MB database backup)
    https://github.com/LitKnd/BabbyNames/releases/tag/v1.1
Then review and run the script below on a SQL Server 2016 dedicated test instance
    Developer Edition recommended (Enteprise and Evaluation Editions will work too)
	
*****************************************************************************/

/* Doorstop */
RAISERROR(N'Did you mean to run the whole thing?',20,1) WITH LOG;
GO


/****************************************************
Restore database and create and populate agg.FirstNameByYearStateWide
****************************************************/
SET NOCOUNT, ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
USE master;
GO

IF DB_ID('BabbyNames') IS NOT NULL
BEGIN
    ALTER DATABASE BabbyNames
        SET SINGLE_USER
        WITH ROLLBACK IMMEDIATE;
END
GO
RESTORE DATABASE BabbyNames
    FROM DISK=N'S:\MSSQL\Backup\BabbyNames.bak'
    WITH
        MOVE 'BabbyNames' TO 'S:\MSSQL\Data\BabbyNames.mdf',
        MOVE 'BabbyNames_log' TO 'S:\MSSQL\Data\BabbyNames_log.ldf',
        REPLACE,
        RECOVERY;
GO
ALTER DATABASE BabbyNames SET QUERY_STORE = ON
GO
ALTER DATABASE BabbyNames SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO
USE BabbyNames;
GO

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'cost threshold for parallelism', 50;
GO
RECONFIGURE;
GO


/****************************************************
This query has a row goal
****************************************************/

with NameRank AS (
    SELECT 
        ReportYear,
        RANK () OVER (PARTITION BY ReportYear ORDER BY NameCount DESC) as RankByReportYear,
        Gender,
        FirstNameId,
        NameCount
    FROM agg.FirstNameByYear AS fnby
)
SELECT
    NameRank.ReportYear, 
    NameRank.RankByReportYear,
    NameRank.Gender, 
    fn.FirstName,  
    NameRank.NameCount
FROM NameRank
JOIN ref.FirstName as fn on NameRank.FirstNameId=fn.FirstNameId
WHERE RankByReportYear <= 3
ORDER BY ReportYear DESC, RankByReportYear ASC
OPTION (FAST 1);
GO

/****************************************************
Same query without the FAST hint
****************************************************/
with NameRank AS (
    SELECT 
        ReportYear,
        RANK () OVER (PARTITION BY ReportYear ORDER BY NameCount DESC) as RankByReportYear,
        Gender,
        FirstNameId,
        NameCount
    FROM agg.FirstNameByYear AS fnby
)
SELECT
    NameRank.ReportYear, 
    NameRank.RankByReportYear,
    NameRank.Gender, 
    fn.FirstName,  
    NameRank.NameCount
FROM NameRank
JOIN ref.FirstName as fn on NameRank.FirstNameId=fn.FirstNameId
WHERE RankByReportYear <= 3
ORDER BY ReportYear DESC, RankByReportYear ASC;
GO