Building Applications with SQL Server 2008

Yesterday I gave a webcast on developer functionality in SQL Server 2008.  I covered SQL Server spatial  support, T-SQL enhancements, Visual Studio integration, and SQL CLR. 

Here is a link to the recording on LiveMeeting: https://www.livemeeting.com/cc/mseventsbmo/view?id=1032402868&role=attend&pw=5D6A541A 

The session was supposed to be an hour, and I had way too much material since I wanted to do a lot of demos.  I ran out of time to answer questions at the end, so here are the questions that I received and my answers: 

"Since the datatables are reference objects, and all we're doing is populating their contents, we shouldn't need the "ref" keyword for these helper functions, right?"

This person was referring to this code in the TableValueTypes demo:

// Create the tables to insert
DataSet dsCurrencyValues = new DataSet();
DataTable dtCurrency = dsCurrencyValues.Tables.Add("Currency");
CreateCurrencyTable(ref dtCurrency);
DataTable dtCurrencyRate = dsCurrencyValues.Tables.Add("CurrencyRate");
CreateCurrencyRateTable(ref dtCurrencyRate);

We're creating some empty tables on a DataSet and passing them to the helper methods CreateCurrencyTable and CreateCurrencyRateTable to be filled.  And yes, you are absolutely right.  You don't need to use the "ref" keyword when passing the DataTables to the helper methods.  I was using demo code that wasn't written by me; I'm sure that the person who wrote it was just trying to get the concepts across and wasn't thinking about error handling, memory leaks, security, performance, and other best practices.  :)  Sorry that I didn't catch that prior to the webcast...I hope that this wasn't confusing for people. 

"SysDates are .Net compatible?"

Yes, the datetime2 data type that is returned by the SYSDATETIME and SYSUTCDATETIME functions is compatible with the .NET Framework's DateTime data type. 

"What is the advantage in creating the currency conversion tables as user define data types as opposed to just creating tables with the same contents?"

The advantage to Table-Valued Parameters (TVPs) is that you can pass these user-defined table types into stored procedures, and you can't pass a table into a stored proc.  So TVPs give you an easy way to send multiple rows of data to SQL Server.  Prior to SQL Server 2008, you would have to either make multiple round trips (calling a stored proc for each row of data separately) or write custom logic to parse and process the data on the server side (whereas with TVPs, you can directly query on them using T-SQL). 

For more information, see this MSDN article, this MSDN article, and this SQL team blog post. 

"Can Merge command be used to merge tables between databases?"

Yes, but the target table cannot be a remote table, although the source table can.  Here is the reference documentation for merge: https://msdn.microsoft.com/en-us/library/bb510625.aspx. 

"Compared to stored procedure, technically how fast is SQL CLR?"

There is an article on SQL CLR performance at https://msdn.microsoft.com/en-us/library/ms131075.aspx.  Wally McClure has also conducted performance analysis at https://weblogs.asp.net/wallym/archive/2006/12/28/tsql-vs-sql-clr-performance-analysis.aspx, although his analysis was done against the SQL CLR in SQL Server 2005.

Thanks to everyone who attended the webcast!