Docstoc

Demo

Document Sample
Demo Powered By Docstoc
					Demo Script
Table-Valued Parameters
Lab version:    1.0.0
Last updated:   12/1/2011




CONTENTS
OVERVIEW ................................................................................................................................................... 3
 Key Messages ........................................................................................................................................... 3
   Key Technologies ...................................................................................................................................... 3
   Time Estimates ......................................................................................................................................... 3

SETUP AND CONFIGURATION .................................................................................................................. 4
       Task 1 – Running the Configuration Wizard ......................................................................................... 4

OPENING STATEMENT............................................................................................................................... 6

STEP-BY-STEP WALKTHROUGH .............................................................................................................. 7
  Segment #1 – Using Table-Valued Parameters ....................................................................................... 7

SUMMARY .................................................................................................................................................. 14
Overview
This document provides setup documentation, step-by-step instructions, and a written script for demonstrating table-valued parameters in SQL
Server 2008.

Key Messages
    1. Table-valued parameters are an efficient mechanism that allows you to send a batch of data in a single round trip. You can use table-
       valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function,
       without creating a temporary table or many parameters.
    2. Table-valued parameters are similar to parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with
       Transact-SQL and can participate in set-based operations.
    3. Table -valued parameters are strongly typed.
    4. Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations
       such as UPDATE, DELETE or INSERT on a table-valued parameter in the body of a routine.


Key Technologies
This demo uses the following technologies:
    1. Microsoft SQL Server 2008 R2 Express (or higher)


Time Estimates
       Estimated time for setting up and configuring the demo: 0 min
       Estimated time to complete the demo: 10 min
Setup and Configuration
System Requirements
       Microsoft SQL Server 2008 R2 Express (or higher)
       Microsoft SQL Server Management Studio
After completing the demo, you can run the Cleanup.cmd script to revert any changes made to your system. If you intend to present other
demos in the training kit, avoid running this script until you have completed them (the script will drop the SQLTrainingKitDB database used
elsewhere in the training kit).


Task 1 – Running the Configuration Wizard
The following steps describe how to run the Configuration Wizard tool included with the demo to verify that all the prerequisites are properly
installed.
    1. Browse to the setup folder in the Source folder of this demo, and run the Setup.cmd script. This script will launch the Configuration
       Wizard for the demo. The Configuration Wizard is designed to check your computer to ensure that it is properly configured with all of
       the dependencies needed to run the demo.
    2. Click through the steps in the Configuration Wizard to get to the Detecting Required Software step; a scan of prerequisites will be
       performed on your computer. If you do not have the necessary dependencies, install them using the links provided by the tool and
       rescan your computer.
   Figure 1
   Dependencies check completed successfully


3. Once the required software is properly installed click Next to configure your computer for this demo. A script will be executed to create
   the server alias ('SQLServerTrainingKitAlias') used in this demo to establish a connection with the database.
        Figure 2
        Configuration Wizard tasks




Opening Statement
In this demo, I will describe the use of table-valued parameters, which is a feature introduced in SQL Server 2008 to solve the problem of
sending multiple rows of data to a stored procedure or function. This requirement is relatively common in database applications and the
canonical example of this is orders and line items, where you have one order that contains N line items. N is variable and can be very large. How
do you pass this information efficiently to a stored procedure?
One database round trip per line item, which is how applications traditionally tackled this in the past, can slow things down. One approach taken
by some applications using SQL Server 2000 and SQL Server 2005 to get around this issue is to compose the order and line items into XML
format. The application passes this information as an XML data type in SQL Server 2005 or as a large text field in SQL Server 2000 and then,
inside a stored procedure, SQL Server decomposes the XML into relational rowsets using built-in functions. This method works but is not
optimal, as XML composition and decomposition takes time. Table-valued parameters are intended to solve this problem.
Even though table variables are not new to SQL Server, SQL Server 2008 introduces the ability to use them as strongly typed parameters to
stored procedures and functions. I will now show you how you can use this feature.




Step-by-Step Walkthrough
This demo is composed of the following segments:
    1. Using Table-Valued Parameters

 Note: The SQL statements shown in the Action column provide the necessary context for a demo step. The SQL demo script file in the code
 folder may contain additional statements, where appropriate, to verify the existence and conditionally create required database objects. These
 statements have been omitted in the document for simplicity.




Segment #1 – Using Table-Valued Parameters

Action                                     Script                                       Screenshot


 1. In SQL Server Management                  I will now demonstrate the use of
    Studio, open the                           table-valued parameters. To do this, I
    TableValuedParameters.sql file             will create a new database named
    located under the code folder in the       SqlTrainingKitDB, which we will use
    Source folder of this demo.                for the remainder of this demo.

 2. Select the CREATE DATABASE
    statement and click Execute ( ) to
    create the SqlTrainingKitDB
  database.

3. Select the USE statement and click
   Execute ( ) to use the new
   SqlTrainingKitDB database.
T-SQL
CREATE DATABASE SqlTrainingKitDB
GO

USE SqlTrainingKitDB
GO


4. Select both CREATE TABLE                 First, I will create an order table and a
   statements and click Execute ( ) to       line item table, named TVPOrder and
   create the TVPOrder and TVPItem           TVPItem respectively. You would
   tables.                                   typically find similar tables in any
                                             application that manages orders.
T-SQL
CREATE TABLE dbo.TVPOrder (
   CustCode varchar(5),
   OrdNo int identity,
   OrdDate datetime
)
GO
CREATE TABLE dbo.TVPItem (
   OrdNo int,
   ProdCode int,
   Qty int
)
GO
 5. Select the first CREATE               I will now show you how applications
    PROCEDURE statement and click          traditionally handled this in previous
    Execute ( ) to create the              versions of SQL Server.
    TVPOrderInsert stored procedure.
                                          To do this, we will create a stored
 6. Select the second CREATE               procedure named TVPOrderInsert
    PROCEDURE statement and click          that inserts order information into the
    Execute ( ) to create the              orders table. This procedure receives
    TVPItemInsert stored procedure.        a customer code as a parameter. It
                                           inserts a new row into the orders
T-SQL                                      table and returns - as output
CREATE PROCEDURE                           parameters - the inserted order
dbo.TVPOrderInsert                         number and the order date.
(
   @CustCode varchar(5),                  In addition, we will create another
   @OrdNo integer output,                  stored procedure named
   @OrdDate datetime output)               TVPOrderInsert that inserts a line
AS                                         item for an order. Notice that you
                                           need to call this procedure once for
   SET @OrdDate = GETDATE();
                                           each line item in the order.
   INSERT INTO dbo.TVPOrder
(OrdDate, CustCode)
     VALUES (@OrdDate, @CustCode);
   SELECT @OrdNo =
SCOPE_IDENTITY()
GO

CREATE PROCEDURE
dbo.TVPItemInsert
(
   @OrdNo integer,
   @ProdCode integer,
   @Qty integer)
AS
   INSERT INTO dbo.TVPItem (OrdNo,
ProdCode, Qty)
    VALUES (@OrdNo, @ProdCode,
@Qty)
GO


 7. Select the DECLARE, EXEC, and             Inserting a new order that contains
    SELECT statements and click                three line items requires several
    Execute ( ) to insert an order             steps. First, you need to call
    containing three line items into the       TVPOrderInsert to insert the order
    database. Show the tables content.         row. Then, you need to make three
                                               calls to TVPItemInsert to insert each
 T-SQL                                         line item.
 DECLARE @ordno int;
 DECLARE @orddate datetime;                   Even though this approach succeeds
                                               and is the way that an application
 EXEC dbo.TVPOrderInsert 'ALFKI',              might have handled this task using
 @ordno output, @orddate output                previous versions of SQL Server,
                                               notice that it requires four database
 EXEC   dbo.TVPItemInsert @ordno,              round trips to fulfill the objective.
 111,   1
 EXEC   dbo.TVPItemInsert @ordno,
 222,   2
 EXEC   dbo.TVPItemInsert @ordno,
 333,   3
 GO

 SELECT * FROM dbo.TVPOrder;
 SELECT * FROM dbo.TVPItem;
 GO


 8. Select the CREATE TYPE statement          I will now show you how you can
    and click Execute ( ) to create the        significantly optimize this process by
    TVPParam user-defined table type.          using table-valued parameters.
T-SQL
                                              First, we need to create a new user-
IF (SELECT COUNT(*) FROM
sys.table_types                              defined table type. Table types
WHERE name = 'TVPParam' AND                  represent the definition of a table
schema_id = 1) = 0                           structure that you can use to declare
                                             table-valued parameters for stored
   CREATE TYPE dbo.TVPParam AS
                                             procedures and functions or to
TABLE(ProdCode integer, Qty                  declare table variables. We will create
integer)                                     a new table type named TVPParam
GO                                           with two columns: a product code and
                                             a quantity, and we will use it to store
                                             line items.


 9. Highlight each line of the              Next, we need to restructure our
    TVPOrderEntry procedure as you           previous order entry mechanism by
    describe it.                             replacing the TVPOrderInsert and
                                             TVPItemInsert stored procedures with
 10. Select the CREATE PROCEDURE             a single stored procedure named
                                             TVPOrderEntry.
    statement and click Execute ( ) to
    create the TVPOrderEntry stored
    procedure.                              Notice that the TVPOrderEntry
                                             procedure receives a table-valued
T-SQL                                        parameter named Items that has a
CREATE PROCEDURE                             TVPParam data type, which is the
dbo.TVPOrderEntry                            table type that we defined previously.
(                                            In addition, the procedure defines this
   @CustCode varchar(5),                     parameter as read-only. This is
                                             required for table-valued parameters.
   @Items TVPParam READONLY,
   @OrdNo integer output,
   @OrdDate datetime output)                In its body, the stored procedure
AS                                           inserts a new row into the order table.
                                             Next, it determines the order number
   SET @OrdDate = GETDATE();
                                             of the newly inserted order by
                                             retrieving the current scope identity.
  INSERT INTO TVPOrder (OrdDate,             Finally, it inserts the order's line items
CustCode)                                    by selecting all the rows from the
    VALUES (@OrdDate, @CustCode);            table-valued parameter.

  SELECT @OrdNo =                           We will now create the stored
SCOPE_IDENTITY();                              procedure and test it.

   INSERT INTO TVPItem (OrdNo,
ProdCode, Qty)
     SELECT @OrdNo, ProdCode, Qty
FROM @Items
GO


 11. Select the DECLARE, INSERT               To do this, we need to declare a table
    INTO, and EXEC statements and              variable that has a TVPParam data
    click Execute ( ) to insert an order       type.
    containing three line items into the
    database.                                 Next, we insert three rows that
 T-SQL                                         contain the line items of the order into
                                               this table. Notice that we are using
 DECLARE @ordno int;
                                               the new Transact-SQL row
 DECLARE @orddate datetime;                    constructor feature to insert all three
 DECLARE @t TVPParam ;                         rows in a single statement.
 INSERT INTO @t VALUES(444, 4),
 (555,5), (666,6);
                                              Finally, we call the TVPOrderEntry
 EXEC dbo.TVPOrderEntry 'BEAUC',               stored procedure passing in a
 @t, @ordno output, @orddate                   customer code and the table variable
 output;                                       that contains the order's line items.
 GO                                            Notice that by using this procedure,
                                               we can insert the order and its line
                                               items in a single round trip.
 12. Highlight both SELECT statements
    and click Execute ( ) to display the
    contents of the TVPOrder and              We can now retrieve the contents of
    TVPItem tables.                            the TVPOrder and TVPItem tables
                                               and see that the stored procedure
 T-SQL                                         has successfully inserted the order
 SELECT * FROM dbo.TVPOrder;                   and its line items.
 SELECT * FROM dbo.TVPItem;
 GO                                           It is important to remember that table-
                                               valued parameters are materialized in
                                               tempdb. You must consider this if you
                                            are going to send large amounts of
                                            information.


13. Highlight both SELECT statements       We can examine the contents of the
   and click Execute ( ) to show the        sys.types table. This table contains a
   content of the sys.types and             row for each system and user-defined
   sys.table_types tables.                  type. We can see our TVPParam
                                            user-defined table type included in
T-SQL                                       this list.
SELECT * FROM sys.types
GO                                         Similarly, we can view metadata for
SELECT * FROM sys.table_types               the user-defined table types only by
GO                                          querying the sys.table_types table
                                            and you can see the TVPParam type
                                            here as well.
14. Scroll to the row containing the
   TVPParam user-defined table type
   definition and highlight the
   is_table_type column.


15. Select the CREATE FUNCTION             You can use table-valued parameters
   statement and click Execute ( ) to       in functions too. I will show you an
   create the TVPFunction function.         example that uses a very simple
                                            function that receives a table-valued
T-SQL                                       parameter and returns the count of
CREATE FUNCTION TVPFunction (@t             rows that it contains.
AS dbo.TVPParam READONLY)
RETURNS int                                To use this function, we need to
AS                                          declare a table variable using our
BEGIN                                       TVPParam table data type. First, we
   RETURN (SELECT count(1) FROM             call our function passing in the table
@t)                                         variable. Notice that because we
END                                         have not inserted any rows into the
                                            table, the function returns a count of
GO
                                            zero.

16. Highlight the DECLARE, INSERT,
    and SELECT statements and click
    Execute ( ) to show the number of         Next, we insert a couple of rows into
    rows in the table-variable.                the table using row constructors and
                                               call the function again. You can see
 T-SQL                                         that it now returns a count of two.
 DECLARE @t AS dbo.TVPParam

 SELECT dbo.TVPFunction(@t)
 INSERT INTO @t VALUES
 (1,1),(2,2)
 SELECT dbo.TVPFunction(@t)
 GO


 17. Highlight the DECLARE, EXEC,             Finally, notice that the store
    and SELECT statements and click            procedure still works if we do not
    Execute ( ) to show the number of          specify the table-valued parameter.
    rows in the table-variable.                By querying the contents of the
                                               tables, we can see that a record is
 T-SQL                                         inserted in the TVPOrder table, but
 DECLARE @ordno int;                           no line item is inserted in the
 DECLARE @orddate datetime;                    TVPItem table.
 EXEC dbo.TVPOrderEntry
 'BFLKU',@OrdNo = @ordno output,
     @Orddate=@orddate output;
 SELECT * FROM dbo.TVPOrder;
 SELECT * FROM dbo.TVPItem;




Summary
In this demo, you have seen how to use table-valued parameters in SQL Server 2008 to send multiple rows of data to Transact-SQL statements or
routines, without creating temporary tables or additional parameters. You have learnt how to declare user-defined table types and use them to
define strongly typed table variables, and how to pass these table variables as parameters to stored procedures and functions. You were able to
compare alternative approaches and see how table-valued parameters provide and efficient solution that minimizes the number of round trips
required to send multiple rows of data.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:105
posted:12/1/2011
language:English
pages:15