Not attending PASS Summit? Watch LIVE streaming events FOR FREE!

If you’ve not yet heard, the annual PASS Summit is less than 2 weeks away. This is the largest SQL Server and Business Intelligence conference _in the world_, sponsored by Microsoft and Dell. The return on investment of attending this conference is pretty huge, and I highly recommend you attend if you can swing it.

I am once more fortunate to be attending and presenting at the Summit. Here’s where you can find me speaking throughout the week:

Tuesday at 3PM
Lightning Talk – Page Compression
This year, PASS has decided to try something new. A daily Lightning Talk session will be held where speakers present for 5 quick minutes on interesting SQL topics. I’ll be presenting on Tuesday with 6 amazingly talented speakers. My topic is page compression — what is it, how to do it, and (most importantly, of course) how it affects performance.

Wednesday at 11:30am in the ballroom
Women-In-Technology (WIT) Luncheon
I’ll be speaking on this year’s WIT luncheon panel, which is sponsored by GoDaddy.com. Contrary to common misconception, the luncheon is NOT just for women. In fact, men are encouraged to attend! If memory serves, last year’s luncheon had about 300 attendees, with a good mix of both genders. This year’s topic is focused on the recruitment, retention, and advancement of WIT. If you’re worried that this event will end up being a feminist bitch-fest, rest assured that’s most definitely not the case. I’ve always found the WIT events I’ve attended to be informative and thought-provoking. Plus, free lunch! :)

Thursday at 2:30PM (room 3AB)
Heaps of Trouble, Clusters of Glory – A Look At Index Internals
You can click the link above to read my abstract, but in short, I’ll be taking attendees on a journey through indexes. You’ll come away with a much better understanding of the internal structures of indexes, which should help DBA’s with database design and performance tuning.

If you’re not able to attend in person, Summit does sell DVD’s of the event afterwards, which are well worth the investment. But this year, to make the event more accessible to the community, PASS and Dell have teamed up to present live streaming of the keynotes and WIT luncheon sessions.

Here’s details of the keynotes from the PASS press release:

Ted Kummert, Senior Vice President of the Business Platform Division at Microsoft Corp., will kick off PASS Summit on November 9 by highlighting the continued innovation across Microsoft’s business and information platform. Kummert will explore Microsoft’s key technical investments, as well as Mission Critical applications and the accessibility of Business Intelligence.

Quentin Clark, General Manager of Database Systems Group at Microsoft Corp., will showcase the next version of SQL Server on November 10 and will share how features in this upcoming product milestone continue to deliver on Microsoft’s Information Platform vision. Clark will also demonstrate how developers can leverage new industry-leading tools with powerful features for data developers and a unified database development experience.

David DeWitt, Technical Fellow, Data and Storage Platform Division at Microsoft Corp., will be discussing SQL query optimization and address why it is difficult to always execute good plans in his highly anticipated technical keynote. DeWitt will also cover new technologies that offer the promise of better plans in future releases of SQL Server.

While all of the keynotes are interesting and definitely worth watching, I cannot recommend the David DeWitt keynote more highly. His keynote last Summit was outstanding. It was technical, thought provoking, and one of the best things of last year’s Summit.

You can find more information and register for the PASS Summit 2010 live streaming events at their website, www.sqlpass.org/LiveKeynotes

If you ARE attending Summit, make sure to swing by and say “hi” or message me via Twitter to see if there’s a time we can meet up. Meeting people is one of my favorite things about Summit. :)

Metadata for Table Valued Parameters

Table-valued parameters (TVP) are a great feature that was new in SQL Server 2008 that allow you to insert a dataset into a table. Previously, the most common way of doing this was by passing and parsing XML. As I’ve previously posted, TVP’s perform an astounding 94% faster than singleton inserts and 75% faster than XML inserts. But for some reason, TVP’s still aren’t widely used and understood. In this post, I’ll walk you through how to use these and how to query the metadata for TVP’s.

I’ve previously posted about what TVP’s are and how to use them. But in honor of Halloween this week, I’ve updated my demo script:

/* Create some tables to work with */
CREATE TABLE dbo.orders
(
      order_id      INT IDENTITY(1,1)   Not Null
    , orderDate     DATE                Not Null
    , customer      VARCHAR(20)         Not Null
 
    CONSTRAINT PK_orders
        PRIMARY KEY CLUSTERED(order_id)
);
 
CREATE TABLE dbo.orderDetails
(
      orderDetail_id    INT IDENTITY(1,1)   Not Null
    , order_id          INT                 Not Null
    , lineItem          INT                 Not Null
    , product           VARCHAR(20)         Not Null
 
    CONSTRAINT PK_orderDetails
        PRIMARY KEY CLUSTERED(orderDetail_id)
 
    CONSTRAINT FK_orderDetails_orderID
        FOREIGN KEY(order_id)
        REFERENCES dbo.orders(order_id)
);
 
 
/* Create our new table types */
CREATE TYPE dbo.orderTable AS TABLE 
( 
      orderDate     DATE
    , customer      VARCHAR(20)
);
GO
 
CREATE TYPE dbo.orderDetailTable AS TABLE 
( 
      lineItem      INT
    , product       VARCHAR(20)
);
GO
 
 
/* Create a new procedure using a table-valued parameter */
CREATE PROCEDURE dbo.insert_orderTVP_sp
      @myOrderTable orderTable READONLY
    , @myOrderDetailTable orderDetailTable READONLY
AS
BEGIN
 
    SET NOCOUNT ON;
 
    DECLARE @myOrderID INT;
 
    INSERT INTO dbo.orders
    SELECT orderDate
        , customer
    FROM @myOrderTable;
 
    SET @myOrderID = SCOPE_IDENTITY();
 
    INSERT INTO dbo.orderDetails
    SELECT @myOrderID
        , lineItem
        , product
    FROM @myOrderDetailTable;
 
    SET NOCOUNT OFF;
 
END
GO
 
 
/* Call our new proc! */
DECLARE @myTableHeaderData AS orderTable
    , @myTableDetailData AS orderDetailTable;
 
INSERT INTO @myTableHeaderData
(orderDate, customer)
VALUES (GETDATE(), 'Zombie');
 
INSERT INTO @myTableDetailData
(lineItem, product)
SELECT 10, 'Brains' UNION ALL
SELECT 20, 'More Brains';
 
EXECUTE dbo.insert_orderTVP_sp 
      @myTableHeaderData
    , @myTableDetailData;
 
DELETE FROM @myTableHeaderData;
DELETE FROM @myTableDetailData;
 
INSERT INTO @myTableHeaderData
(orderDate, customer)
VALUES (GETDATE(), 'Vampire');
 
INSERT INTO @myTableDetailData
(lineItem, product)
SELECT 10, 'Blood Type O+' UNION ALL
SELECT 20, 'Blood Type B-' UNION ALL
SELECT 30, 'Blood Type AB+' UNION ALL
SELECT 40, 'Blood Type A+';
 
EXECUTE dbo.insert_orderTVP_sp 
      @myTableHeaderData
    , @myTableDetailData;
 
 
/* Check our data */
SELECT * FROM dbo.orders;
SELECT * FROM dbo.orderDetails;

Once you’ve run this, you should see the following data:

order_id    orderDate  customer
----------- ---------- --------------------
1           2010-10-28 Zombie
2           2010-10-28 Vampire
 
(2 row(s) affected)
 
orderDetail_id order_id    lineItem    product
-------------- ----------- ----------- --------------------
1              1           10          Brains
2              1           20          More Brains
3              2           10          Blood Type O+
4              2           20          Blood Type B-
5              2           30          Blood Type AB+
6              2           40          Blood Type A+
 
(6 row(s) affected)

Now that we’ve successfully created a couple of table types to support our TVP’s, how do we go back and find out which objects we’ve created? You can query the sys.types catalog view to find out. Just search for system_type_id 243, which identifies the record as a table type.

/* Let's check out our new data types */
SELECT name
    , system_type_id
    , is_table_type
FROM sys.types
WHERE system_type_id = 243;
GO
name                 system_type_id is_table_type
-------------------- -------------- -------------
orderTable           243            1
orderDetailTable     243            1
 
(2 row(s) affected)

Even better yet, you can use the sys.table_types catalog view. This gives us the same information as sys.types but also gives us the type_table_object_id, which we’ll need shortly.

SELECT name
    , system_type_id
    , is_table_type
    , type_table_object_id
FROM sys.table_types;
name                 system_type_id is_table_type type_table_object_id
-------------------- -------------- ------------- --------------------
orderTable           243            1             917578307
orderDetailTable     243            1             933578364
 
(2 row(s) affected)

What if you need to look up the table type definition? You can do this using the type_table_object_id and joining to sys.columns.

SELECT tt.name AS 'table_type_name'
    , c.name AS 'column_name'
    , t.name AS 'data_type'
FROM sys.table_types AS tt
JOIN sys.columns AS c
    ON tt.type_table_object_id = c.object_id
JOIN sys.types As t
    ON c.system_type_id = t.system_type_id;
table_type_name      column_name     data_type
-------------------- --------------- ---------------
orderTable           orderDate       date
orderDetailTable     lineItem        int
orderTable           customer        varchar
orderDetailTable     product         varchar
 
(4 row(s) affected)

And last, but certainly not least, how do we see if any procs are currently using the table types? SQL Server 2008 makes this easy for us with the sys.dm_sql_referencing_entities DMV.

SELECT referencing_schema_name, referencing_entity_name, referencing_id
FROM sys.dm_sql_referencing_entities ('dbo.orderTable', 'TYPE');
referencing_schema_name referencing_entity_name referencing_id
----------------------- ----------------------- --------------
dbo                     insert_orderTVP_sp      949578421
 
(1 row(s) affected)

If you’re wondering how to implement SQL Server TVP’s in your .NET code, well… I can’t tell you how to do it, but I can point you to a place that can. Stephen Forte has a post that explains how easy it is to do.

So now that you have a better understanding of how to work with TVP’s, why don’t you go implement one in your environment and see how for yourself just how awesome it is? :)

Oh, and Happy Halloween!