USE [AdventureWorksDW2012];
GO

SELECT TOP 1000 *
INTO dbo.FactInternetSales_fi_demo
FROM dbo.FactInternetSales;
GO

CREATE INDEX fi_PromotionKey_by_CurrencyKey
ON dbo.FactInternetSales_fi_demo (PromotionKey)
WHERE CurrencyKey = 6;
GO

-- Ad hoc match?
DECLARE @CurrencyKey int = 6;

SELECT DISTINCT PromotionKey
FROM [dbo].[FactInternetSales_fi_demo]
WHERE CurrencyKey = @CurrencyKey;
GO

-- Stored procedure match?
CREATE PROCEDURE dbo.FIS_by_CurrencyKey 
	@CurrencyKey int
AS

SELECT DISTINCT PromotionKey
FROM [dbo].[FactInternetSales_fi_demo]
WHERE CurrencyKey = @CurrencyKey;
GO

EXEC dbo.FIS_by_CurrencyKey 6;
GO

-- What about this?
CREATE PROCEDURE dbo.FIS_by_CurrencyKey_v2
	@CurrencyKey int
AS

SELECT DISTINCT PromotionKey
FROM [dbo].[FactInternetSales_fi_demo]
WHERE CurrencyKey = @CurrencyKey
OPTION (RECOMPILE);
GO

EXEC dbo.FIS_by_CurrencyKey_v2 6;
GO


-- Cleanup
DROP INDEX fi_PromotionKey_by_CurrencyKey
ON dbo.FactInternetSales_fi_demo;
GO

DROP TABLE FactInternetSales_fi_demo;
GO

DROP PROCEDURE dbo.FIS_by_CurrencyKey;
GO

DROP PROCEDURE dbo.FIS_by_CurrencyKey_v2;
GO
