/*============================================================================
  File:     CachingExamples.sql

  Summary:  This script shows how to clear cache, see what's in cache and find
		out more about DMVs. Use DMObjects.SQL to create the needed
		functions to access metadata about DMVs.

  Date:     Feb, 2006

  Tested for SQL Server Version: RTM
------------------------------------------------------------------------------
  Copyright (C) 2006, SYSolutions, Inc.
  All rights reserved.

  This script is intended only as a supplement to demos and lectures
  given by SQLskills.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

use pubs
go

-- auto parameterized? No, too complex
SELECT title_id, sum(qty) from sales
group by title_id
having sum(qty) = 30

-- exact match requires
-- even comments are part of "query string"
SELECT title_id, sum(qty) from sales
group by title_id
having sum(qty) = 30

-- Auto parameterized and cached...
SELECT * 
FROM titles 
WHERE price = $19.99

-- So, will subsequent executions use it?
SELECT * 
FROM titles 
WHERE price = $19.99

-- But what if there's a different value?
SELECT * 
FROM titles 
WHERE price = $7.99 -- money

SELECT * 
FROM titles 
WHERE price = 7.99 -- still money?

SELECT * 
FROM titles 
WHERE price = 19.99 -- still the same?

-- Dynamic String Execution vs sp_executesql
DECLARE @price    money,
                @ExecStr    nvarchar(4000)
SET @price = 19.99
SELECT @ExecStr = 'SELECT * FROM dbo.titles WHERE price = ' 
                  + convert(varchar(10), @price)
EXEC(@ExecStr)


-- Client application
-- Caching by specification, variable is explicitly typed
EXEC sp_executesql
	N'select * from titles where price = @price',
	N'@price money',
	19.99

EXEC sp_executesql
	N'select * from titles where price > @price',
	N'@price money',
	1099.99
