1 / 9

SNESSUG Lightning Talk: Table Valued Parameters

SNESSUG Lightning Talk: Table Valued Parameters. By: Matt Batalon , MCITP Twitter:@ MattBatalon. Introduced in SQL Server 2008. Another form of temporary storage that can be queried or joined against, much like a table variable, temp table or CTE

midori
Download Presentation

SNESSUG Lightning Talk: Table Valued Parameters

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SNESSUG Lightning Talk:Table Valued Parameters By: Matt Batalon, MCITP Twitter:@MattBatalon

  2. Introduced in SQL Server 2008 • Another form of temporary storage that can be queried or joined against, much like a table variable, temp table or CTE • Based on user defined table type that describes schema for a table • Commonly referred to as Table Valued Parameters (TVP) or User Defined Table Types

  3. Table Valued Parameters or User Defined Table Types… • Can be passed as a single parameter (set of rows) to a stored procedure or user defined function unlike a table variable or temp table • Can be passed between stored procedures or from client side application • Reusability; the schema for a TVP is centrally maintained

  4. Visible in SQL Server Management Studio • TVP types are displayed in SQL Server Management Studio unless manually dropped • Programmability  Types  User Defined Table Types • Can expand to see columns, constraints, indexes

  5. Other Benefits of Table Valued Parameters • Ease of encapsulating multiple parameters into a single parameter • Reduces round trips to the server, which can aid in performance • Strongly typed with the CREATE TYPE…TABLE statement • Easily maintainable code because schema can be defined only once then can be reused

  6. Before Table Valued Parameters • Forced to send multiple parameters as arguments • Use String Manipulation to encapsulate multiple parameters • Individual Database calls, not a set based approach

  7. Demo: Scenarios for Table Valued Parameters • Using a TVP vs. Delimited String • Bulk inserting a TVP • Bulk Update using a TVP • Encapsulate business logic using TVPs, and reducing round trips to server

  8. Limitations of Table Valued Parameters • Data cannot be modified, must be passed as READ ONLY • Cannot use ALTER table statement on a TVP, must be dropped and recreated • No support for Select INTO or INSERT Exec statement

  9. For more on Table Valued Parameters • Books online • Examples for using with ADO.net • Comparison with BCP • Connectivity Books: Programming SQL Server 2008 by Leonard Lobel, Andrew Bust, Stephen Forte

More Related