Passing a Set of Data to Microsoft SQL Server 2008: How, What, and Why

1. Required Slide SESSION CODE: DAT404 Passing a Set of Data to Microsoft SQL Server 2008: How, What, and Why Tobias Ternstrom Senior Program Manager Lead SQL Server Engine

2. Passing a set of data to SQL Server • N rows = N executed statements • N rows = 1 executed statement

3. N rows = N executed statements • One client server roundtrip per execution • All executions in one batch

4. N rows = 1 executed statement • Pass the data as a delimited list • Pass the data as XML • Pass the data as Table Valued Parameter Other options • Bulk copy • Pass data as separate arguments (current limit is 2100)

5. Examples • In the examples we will be passing a set of items to the database for storage • Example - “Store the following 1000 items” • Examples will use • Stored Procedures • C# & ADO.NET

6. Pass the data as a delimited list // C# cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText= "Test.spDelimitedString"; cmd.Parameters.AddWithValue("@Values", @"…|…|… …|…|… …|…|…"); cmd.Execute…; -- T-SQL: What happens on the server? EXEC Test.spDelimitedString @Values = '…|…|… …|…|… …|…|…';

7. Pass the data as a delimited list • To get the best performance we need to use a SQLCLR Table Valued Function • Pros • Performance is good • No exposure to SQL Injection • Cons • Requires SQLCLR to be enabled on the instance • The set of data is not strongly typed • Cumbersome implementation • Can be simplified by created one TVF per “list type”

8. Pass the data as XML // C# cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText= "Test.spXML"; cmd.Parameters.AddWithValue("@Values", xmlDoc.OuterXml); cmd.Execute…; -- T-SQL: What happens on the server? EXEC Test.spXML @Values = N'<Orders><Order…

9. Pass the data as XML • Pros • Strongly typed (if you use an XML Schema Collection) • No exposure to SQL Injection • A very good option if your data is already XML! • Great flexibility, remember XML allows for hierarchies, extensions etc. • Cons • Performance is good but not the best • Requires knowledge about XML • Less cumbersome than the delimited list but still somewhat cumbersome

10. Pass the data as a Table Valued Parameter // C# cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText= "Test.spTVP"; varp = cmd.Parameters.Add("@Values“, SqlDbType.Structured); p.TypeName= "Test.OrderTableType"; p.Value= dataTable; cmd.Execute…; -- T-SQL: What happens on the server? DECLARE @Values Test.OrderTableType; INSERT @Values … EXEC Test.spTVP @Values = @Values;

11. Pass the data as a Table Valued Parameter • Pros • Strongly typed • No exposure to SQL Injection • Performance is great! • Very easy to use, both on client and server side • Allows for streaming of data • Cons • Less flexible than XML • Allows for streaming, butonly to the server

12. Pass the data as a Table Valued Parameter Streaming // C# class MyStreamingTvp : IEnumerable<SqlDataRecord> {… } … cmd.CommandType= CommandType.StoredProcedure; cmd.CommandText= "Test.spTVP"; varp = cmd.Parameters.Add("@Values", SqlDbType.Structured); p.TypeName= "Test.OrderTableType"; p.Value= new MyStreamingTvp(…); cmd.Execute…; -- T-SQL: What happens on the server?: DECLARE @Values Test.OrderTableType; INSERT @Values … EXEC Test.spTVP @Values = @Values;

13. Pass the data as a Table Valued Parameter • Pros • No need for staging the data in memory on the client side • Cons • Doesn’t stream all the way, stages the data on the server side • Adds complexity in the client code

14. A few more words on Streaming • If you stream, how “far” do you stream? • N rows = N client server round trips & N proc. executions • Streams “all” the way to the destination table • Streaming TVP • Streams from client to just before the procedure/batch begins execution, i.e. stages the data on the server side • XML/Delimited string • Stages the data both on the client and server side • Any solution can implement streaming “manually”

15. What happens? And what about performance? • Initial parsing of the data on the Server • Querying the data

16. 1. Initial parsing of the data on the Server

17. 1. Initial parsing of the data on the Server

18. 2. Querying the data

19. 2. Querying the data

20. Required Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. DAT Track Scratch 2 Win • Find the DAT Track Surface Table in the Yellow Section of the TLC • Try your luck to win a Zune HD • Simply scratch the game pieces on the DAT Track Surface Table and Match 3 Zune HDs to win

21. Required Slide Resources Learning • Sessions On-Demand & Community • Microsoft Certification & Training Resources www.microsoft.com/teched www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet • http://microsoft.com/msdn

22. Required Slide Complete an evaluation on CommNet and enter to win!

23. Sign up for Tech·Ed 2011 and save $500 starting June 8 – June 31st http://northamerica.msteched.com/registration You can also register at the North America 2011 kiosk located at registrationJoin us in Atlanta next year

24. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

25. Required Slide