Created
July 11, 2017 16:09
-
-
Save LitKnd/d888c5324d0a2de7c2f50ffe96f2e9e8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/***************************************************************************** | |
Copyright (c) 2017 SQL Workbooks LLC | |
Terms of Use: https://sqlworkbooks.com/terms-of-service/ | |
Contact: help@sqlworkbooks.com | |
Setup: | |
Download BabbyNames.bak.zip (11.6 MB database backup) | |
https://github.com/LitKnd/BabbyNames/releases/tag/v1.0 | |
*****************************************************************************/ | |
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 | |
USE BabbyNames; | |
GO | |
ALTER TABLE agg.FirstNameByYearState | |
ADD Flag TINYINT NOT NULL | |
CONSTRAINT Flag_DEFAULT DEFAULT (0) | |
GO | |
CREATE NONCLUSTERED INDEX ix_FirstNameByYearState_Flag on | |
agg.FirstNameByYearState (Flag) INCLUDE (FirstNameId); | |
GO | |
ALTER TABLE agg.FirstNameByYearState | |
ADD CONSTRAINT Flag_CHECK CHECK (Flag IN (0,1,2,3,4,5)); | |
GO | |
/* "Flag" a lot of rows */ | |
UPDATE agg.FirstNameByYearState | |
SET Flag = 1 | |
WHERE FirstNameId > (90000) | |
GO | |
SET STATISTICS IO, TIME OFF; | |
GO | |
SET XACT_ABORT, NOCOUNT, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON; | |
GO | |
ALTER DATABASE [BabbyNames] SET QUERY_STORE = ON | |
GO | |
ALTER DATABASE [BabbyNames] SET QUERY_STORE | |
(OPERATION_MODE = READ_WRITE, | |
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 180), MAX_STORAGE_SIZE_MB = 1024 | |
) | |
GO | |
DROP PROC IF EXISTS dbo.ReviewFlags; | |
GO | |
CREATE PROC dbo.ReviewFlags | |
@Flag TINYINT | |
AS | |
SET XACT_ABORT, NOCOUNT ON; | |
BEGIN TRY | |
IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5) | |
RAISERROR ('@Flag must be a value between 1 and 5', 11, 1); | |
SELECT | |
fnbd.FirstNameId, | |
fn.FirstName, | |
fnbd.Gender | |
FROM agg.FirstNameByYearState as fnbd | |
JOIN ref.FirstName as fn on | |
fnbd.FirstNameId = fn.FirstNameId | |
WHERE fnbd.Flag = @Flag | |
END TRY | |
BEGIN CATCH | |
THROW | |
END CATCH | |
GO | |
/* First run the procedure this way... | |
it hits the IF validation and raises the error */ | |
EXEC dbo.ReviewFlags @Flag = null; | |
GO | |
/* Run this with actual plans and look at the value that | |
@Flag was optimized for */ | |
EXEC dbo.ReviewFlags @Flag = 1; | |
GO | |
EXEC dbo.ReviewFlags @Flag = 1 WITH RECOMPILE; | |
GO | |
/* Dynamic SQL */ | |
DROP PROC IF EXISTS dbo.ReviewFlags; | |
GO | |
CREATE PROC dbo.ReviewFlags | |
@Flag TINYINT | |
AS | |
SET XACT_ABORT, NOCOUNT ON; | |
BEGIN TRY | |
IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5) | |
RAISERROR ('@Flag must be a value between 1 and 5', 11, 1); | |
DECLARE @dsql NVARCHAR(MAX) = N' | |
SELECT | |
fnbd.FirstNameId, | |
fn.FirstName, | |
fnbd.Gender | |
FROM agg.FirstNameByYearState as fnbd | |
JOIN ref.FirstName as fn on | |
fnbd.FirstNameId = fn.FirstNameId | |
WHERE fnbd.Flag = @Flag'; | |
EXEC sp_executesql @stmt = @dsql, @params = N'@Flag TINYINT', @Flag=@Flag; | |
END TRY | |
BEGIN CATCH | |
THROW | |
END CATCH | |
GO | |
EXEC dbo.ReviewFlags @Flag = null; | |
GO | |
EXEC dbo.ReviewFlags @Flag = 1; | |
GO | |
/* Optimize FOR */ | |
DROP PROC IF EXISTS dbo.ReviewFlags; | |
GO | |
CREATE PROC dbo.ReviewFlags | |
@Flag TINYINT | |
AS | |
SET XACT_ABORT, NOCOUNT ON; | |
BEGIN TRY | |
IF ISNULL(@Flag,0) NOT IN (1,2,3,4,5) | |
RAISERROR ('@Flag must be a value between 1 and 5', 11, 1); | |
SELECT | |
fnbd.FirstNameId, | |
fn.FirstName, | |
fnbd.Gender | |
FROM agg.FirstNameByYearState as fnbd | |
JOIN ref.FirstName as fn on | |
fnbd.FirstNameId = fn.FirstNameId | |
WHERE fnbd.Flag = @Flag | |
OPTION (OPTIMIZE FOR (@Flag = 1)) | |
END TRY | |
BEGIN CATCH | |
THROW | |
END CATCH | |
GO | |
EXEC dbo.ReviewFlags @Flag = null; | |
GO | |
EXEC dbo.ReviewFlags @Flag = 1; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment