SQL Server Chat: The CLR and Yukon

March 24, 2004

Published: April 8, 2004

Please note: Portions of this transcript have been edited for clarity

Introduction

Moderator: Stephen_D (Microsoft)
Welcome to today's SQL Server Chat: The CLR and Yukon. I will ask the hosts to introduce themselves.

Host: Kurt (PASS)
Hi, I'm Kurt Windisch with PASS. Thanks for joining us today!

Host: JoseB (Microsoft)
Hi: I am an architect in the SQL Server engine team working on SQLCLR.

Host: Venkatesh (Microsoft)
I am a program manager in the SQL Server team, I work on SQLCLR and UDTs.

Host: Pablo (Microsoft)
I'm a Program Manager in the webdata team working on client interfaces including ADO.NET, OLEDB and ODBC.

Host: Christian (Microsoft)
Good Morning, I'm Christian Kleinerman, Program Manager in the SQL Engine team... I work on the data access provider (System.Data.SqlServer)... I'm glad you could make it.

Host: DanWinn (Microsoft)
I'm a Program Manager in the SQL Server Engine team working on SQL/CLR in the Server and SQL/CLR Project System design in Visual Studio.

Host: Peter_Carlin (Microsoft)
I am an architect in the SQL Server engine team working on SqlClr.

Host: Lisa (Microsoft)
I work in PSS SQL server support team.

Moderator: Stephen_D (Microsoft)
And... I am Stephen Dybing, SQL Server MVP Lead. Glad you all could make it today!

Start of Chat

Host: Kurt (PASS)
Q: What can I be doing now to prepare for SQL2005?
A: For the SQLCLR piece, probably the best thing to do is to get familiar with .NET development using a language such as C# or VB.NET. Check out http://www.microsoft.com/sql/prodinfo/betanominations.mspx for updates on how you can get on the Yukon beta.

Host: DanWinn (Microsoft)
Q: How much of a learning curve do you anticipate with mastering SQL2005 over SQL2000?
A: We’ve been very careful to maintain backwards compatibility between SQL 2005 and SQL 2000 as much as possible, so the transition from 2000 to 2005 should be pretty easy. However, there is a tremendous amount of new capability which will likely take quite a bit of time to master. 2005 is a much larger (and more capable) product than 2000 was. With specific regard to SQL/CLR, if your familiar with a managed language, it should be pretty easy for you to get started. The Project system integration really eases entry. If you haven’t written code in a managed language before, then the learning curve will be a bit steeper, but you can start prepping now by teaching yourself a managed language.

Host: Peter_Carlin (Microsoft)
Q: How can I use the CLR in Yukon stored procedures?
A: You can use CLR stored procedures (also known as UDPs) anywhere TSQL stored procedures can be invoked. For example, in an exec statement, in an insert exec statement, via an RPC from the client. In UDPs you can use much of the CLR Base Class Libraries, and you can use libraries you've written and ones from third parties. You can do computation, for instance using System.String APIs to do string manipulation not available in TSQL builtins. You can access external data, for instance in files or web services.

Host: JoseB (Microsoft)
Q: Is T-SQL going to change? To become more like other .NET languages?
A: Not in SQL2005. There are internal discussions about the best way to provide a modern database programming language (DBPL) for our customers. One possibility is to implement T-SQL as a .NET language another is to evolve C# and VB into a DBPLs. These are the two options we are exploring.

Host: DanWinn (Microsoft)
Q: Besides the C# and VB.Net what other languages are supported?
A: MS MC++ will also be supported. Beyond that, any third party compiler which produces verifiable IL will be supported. Borland demo’ed Pascal running on SQL/CLR recently at a conference of theirs. COBOL is also another commonly mentioned language for which third party support is in the works.

Moderator: Stephen_D (Microsoft)
Q: How long after SQL 2005 is released will SQL 2000 be supported?
A: For these types of questions, I would start at http://support.microsoft.com/default.aspx?pr=lifecycle. This states that for Business and Development Software, we will offer a minimum of 5 years of mainstream support. Then http://support.microsoft.com/default.aspx?scid=fh;[ln];LifeSrvr indicates that mainstream support will end on 12/31/2005.

Host: JoseB (Microsoft)
Q: So in SQL2005, there will be T-SQL language enhancements to use the CLR? Or will there be two types of procedures, one type for CLR and the standard one we have now?
A: In SQL2005 there are extensions to T-SQL such as support for new data types, transitive closure, pivot/unpivot, etc. Regarding T-SQL & CLR interaction, we have added the ability to call CLR functions from T-SQL in the same way you call T-SQL UDFs. However the implementation of CLR functions, procedures, triggers, types and aggregates is done entirely in .NET languages.

Host: Venkatesh (Microsoft)
Q: How do you move .NET assemblies from server to another?
A: You can get the bytes of the assembly from the database by querying the sys.assemblies and sys.assembly_files catalog views. Then, you can issue create assembly ddl on the other database to install the assembly there. This is the TSQL DDL way to do this, take a look at the SMO apis as well.

Host: Peter_Carlin (Microsoft)
Q: Is ObjectSpaces meant to link C# code better to the datasource (this way, TSQL will become for C# like COM is for .NET in a way...)?
A: Yes, objectspaces is meant to make it easier to use C# objects with databases. I don't understand the analogy of TSQL:C# :: COM:.NET.

Host: Christian (Microsoft)
Q: lagrange: Does this mean I can mix VB.NET statements and TSQL statements in a UDP?
A: You can write stored procedures in VB.Net. From within your stored procedure you can use Ado.Net api's to execute TSQL statements and move data back and forth from the managed code.

Host: Peter_Carlin (Microsoft)
Q: How do you move .NET assemblies from server to another?
A: A followup on this: If you move a database from one server to another, for instance via backup/restore or attach/detach; all .NET assemblies and their functions, procs, types, aggs, etc. come with it. If you just want to move one assembly and the procs,functions,types,aggs that depend on it, see Venkatesh's answer.

Host: Venkatesh (Microsoft)
Q: Can .NET functions be called from within any database?
A: The function (and all other objects) are registered within a particular database. Procedures can be called fom another database by using the database-qualified 3-part name. Functions, types and aggregates are callable only within the current database.

Host: Pablo (Microsoft)
Q: And about DateTime... it's very difficult to store the "null" datetimes in C# because in .NET Framework DateTime is a struct (value type) and not a class; personally I maintain DateTime.MinValue values to mean "null" but this is not too elegant...
A: You have the option to use SqlDateTime (in System.Data.SqlTypes) that includes support for null values.

Host: Peter_Carlin (Microsoft)
Q: Sorin's followup on Objectspaces:
A: Objectspaces allows you to manipulate objects and not have to know the SQL queries they translate into. However, it does not abstract updates, and it does not abstract any procedural operations in T-SQL.

Host: DanWinn (Microsoft)
Q: One of my frequent problems is formatting DATETIME objects from the database to display. Are all of the .NET functions available?
A: You can wrap any .NET Framework function (within the supported libraries) with a managed function of your own and expose it within SQL. We support most of the Frameworks with some notable exceptions like WinForms and System.Drawing.

Host: DanWinn (Microsoft)
Q: I have heard that when SQL Server Yukon Beta 1 installs, a message shows up saying: "Installing .NET Frameworks 2" (or something like that); what is that plural for? (frameworks)... or was it a typo?
A: This should be .NET Framework. It was probably an uncaught typo.

Host: Christian (Microsoft)
Q: dick: The UDP is written in the C# or VB.NET using ADO.NET to fetch and update data in the database, yes? But the UDP is executed by the SQL Server engine to give better performance than from an external client?
A: Data access is done through the Ado.Net apis, though through a different implementation that is aware that is running inside SQL Server engine and bypasses some of the layers that a regular external client connection goes through.

Mahesh_MS says:
Q: Sorin: SQL Server 2005 will have a small version of Visual Studio .NET in it as the data tier development environment. If this is correct, will one be able to write other code (for example Windows-based applications) using that Visual Studio small version?
A: We’ll likely only ship VS IDE only SKU that allows our tools to be hosted in VS shell but you won’t be able to write other apps w/o VS or .NET SDK in addition to SQL.

Moderator: Stephen_D (Microsoft)
Thanks for joining us today! You've asked some great questions

For further information on this topic or about SQL Server, please visit the following:

Newsgroups: SQL Server Newsgroups

SQL Transcripts: Read the archive of past SQL chats.

Website: Visit the Microsoft SQL Website