SET NOCOUNT ON
-------------------------------------------------------------------------------
-- SET UP
-------------------------------------------------------------------------------
USE master
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'TEST_TVP')
	DROP DATABASE [TEST_TVP]
GO

CREATE DATABASE [TEST_TVP] ON  PRIMARY 
( NAME = N'TEST_TVP', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TESTVP.mdf' , SIZE = 5000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB )
 LOG ON 
( NAME = N'TEST_TVP_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TESTVP_log.LDF' , SIZE = 5000KB , MAXSIZE = 2048GB , FILEGROWTH = 10MB)
GO

USE TEST_TVP
GO

CREATE TABLE dbo.TestData ( id int NOT NULL IDENTITY(1,1) CONSTRAINT pk_testdata PRIMARY KEY,
									name char(72) )
GO

CREATE TABLE dbo.TestResults ( num_Filters int, num_repeats int, Method char(10), [nanoseconds] bigint)
GO

INSERT INTO  dbo.TestData ( name )
SELECT REPLICATE(CAST(NEWID() AS CHAR(36)),2)
FROM ( SELECT 1 as id
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 0 ) A
GO

DECLARE @i int
SET @i =1
WHILE @i<4
BEGIN
	INSERT INTO  dbo.TestData ( name )
	SELECT REPLICATE(CAST(NEWID() AS CHAR(36)),2)
	FROM dbo.TestData d,
	( SELECT 1 as id
		UNION ALL SELECT 2
		UNION ALL SELECT 3
		UNION ALL SELECT 4
		UNION ALL SELECT 5
		UNION ALL SELECT 6
		UNION ALL SELECT 7
		UNION ALL SELECT 8
		UNION ALL SELECT 9
		UNION ALL SELECT 0 ) A
	SET @i += 1
END
GO
CREATE TYPE filtertable AS TABLE ( filterid int PRIMARY KEY)
GO
CREATE PROCEDURE dbo.GetDetailsWithTVP ( @tvp filtertable READONLY )
AS
	SELECT t.id, t.name
	FROM dbo.TestData t
JOIN @tvp f ON f.filterid = t.id
GO

CREATE PROCEDURE dbo.GetDetailsWithXML ( @xmlfilter xml )
AS
SELECT t.id, t.name
FROM dbo.TestData t
JOIN @xmlfilter.nodes('/root/filter') AS f(filter) ON f.filter.value('@id', 'int') = t.id
GO

CREATE PROCEDURE dbo.GetDetailsWithXMLExists ( @xmlfilter xml )
AS
	SELECT t.id, t.name
	FROM dbo.TestData t
WHERE @xmlfilter.exist('/root/filter[@id = sql:column("id")]') = 1
GO

CREATE PROCEDURE dbo.GetDetailsWithOpenXML ( @xmlfilter varchar(max) )
AS
   DECLARE @idoc  int,
           @err   int

   EXEC  @err = sp_xml_preparedocument @idoc OUTPUT, @xmlfilter
   SELECT @err = @@error + coalesce(@err, 4711)
   IF @err <> 0 RETURN @err

	SELECT t.id, t.name
	FROM dbo.TestData t
	JOIN	OPENXML(@idoc, '/root/filter', 1)
		          WITH (id int) AS s ON t.id = s.id

	EXEC sp_xml_removedocument @idoc
GO

UPDATE STATISTICS dbo.TestData WITH FULLSCAN
GO