1/* First you need to create a table type. */
2CREATE TYPE Names AS TABLE
3(Name VARCHAR(10)) ;
4GO
5
6/* Next, Create a procedure to receive data for the table-valued parameter, the table of names and select one item from the table*/
7CREATE PROCEDURE ChooseAName
8 @CandidateNames Names READONLY
9AS
10DECLARE @candidates TABLE (NAME VARCHAR(10),
11 theOrder UNIQUEIDENTIFIER)
12INSERT INTO @candidates (name, theorder)
13 SELECT name, NEWID()
14 FROM @CandidateNames
15SELECT TOP 1
16 NAME
17FROM @Candidates
18ORDER BY theOrder
19GO
20
21/* Declare a variable that references the type for our list of cows. */
22DECLARE @MyFavouriteCowName AS Names ;
23
24/* Add data to the table variable. */
25INSERT INTO @MyFavouriteCowName (Name)
26 SELECT 'Bossy' UNION SELECT 'Bessy' UNION SELECT 'petal' UNION SELECT 'Daisy' UNION SELECT 'Lulu' UNION SELECT 'Buttercup' UNION SELECT 'Bertha' UNION SELECT 'Bubba' UNION SELECT 'Beauregard' UNION SELECT 'Brunhilde' UNION SELECT 'Lore' UNION SELECT 'Lotte' UNION SELECT 'Rosa' UNION SELECT 'Thilde' UNION SELECT 'Lisa' UNION SELECT 'Peppo' UNION SELECT 'Maxi' UNION SELECT 'Moriz' UNION SELECT 'Marla'
27
28/* Pass the table with the list of traditional nemes of cows to the stored procedure. */
29EXEC chooseAName @MyFavouriteCowName
30GO
31
1SET nocount ON
2
3DECLARE @FirstTable TABLE (RandomInteger INT)
4DECLARE @SecondTable TABLE (RandomInteger INT)
5DECLARE @WhenWeStarted DATETIME
6DECLARE @ii INT
7
8BEGIN TRANSACTION
9SET @ii = 0
10WHILE @ii < 100000
11 BEGIN
12 INSERT INTO @FirstTable
13 VALUES (RAND() * 10000)
14 SET @ii = @ii + 1
15 END
16SET @ii = 0
17WHILE @ii < 100000
18 BEGIN
19 INSERT INTO @SecondTable
20 VALUES (RAND() * 10000)
21 SET @ii = @ii + 1
22 END
23COMMIT TRANSACTION
24SELECT @WhenWeStarted = GETDATE()
25SET STATISTICS PROFILE ON
26SELECT COUNT(*)
27FROM @FirstTable first
28 INNER JOIN @SecondTable second
29 ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)
30 -- 153Ms as opposed to 653Ms without the hint
31SET STATISTICS PROFILE OFF
32SELECT 'That took '
33 + CONVERT(VARCHAR(8), DATEDIFF(ms, @WhenWeStarted, GETDATE()))
34 + ' ms'
35go
36