November 2005 - Posts
User Defined Types in SQL Server 2005 is a great new feature that allows you to extend the type system of the database. By authoring your new data type as .NET assemblies according to a specific contract, you can register these assemblies in the database and then use the types in them. This new capability opens up a lot of interesting possibilities in the database to store information. This article provides an overview of how to create, define and use user defined types using an example. We also see how to consume the user defined type from a client application like ADO.NET. The article is available at:
Creating User Defined Types. Have fun!
On 24th, November 2005, I did a presentation on XQuery basics at the
B.NET user group. Suffice to say that it was really interesting and the audience was quite interactive. It also made me learn a lot of XQuery before getting on to present about it. Have created a
new location in
my web site to host the code samples that I keep using in the various presentations. You can download the XQuery samples at this location. Have fun.
Normally, when we create CLR objects for SQL Server 2005, you would first execute a CREATE ASSEMBLY to catalog the assembly in the database. Then, depending on what you are writing, you may map procedures, functions etc to the CLR entry points. If, on the other hand, you are using Visual Studio, when you choose the “Deploy” method, VS does the entire thing for you. However, for production class systems, you would not want VS to automatically deploy to those systems and you may rather resort to the manual CREATE ASSEMBLY method. In this case, after registering the assembly, manually mapping the various objects can be a lot of typing. Fortunately, the CLR team has put in a small piece of code, that acts like a trigger on the CREATE ASSEMBLY statement and then using Reflection, automatically registers the various objects found in the assembly as procedures, triggers etc. You can view the code here:
CLR Trigger for Automatic Registration of UDXs on CREATE ASSEMBLY.
Over the last two days, I've been trying to install the latest releases of SQL Server 2005 and VS 2005 on my laptop. Earlier, I had them on a VPC and since the RTMs are out, I wanted to install them on the actual OS. SQL Server 2005 installed without a problem. However, when I installed VS 2005, it did finish successfully, but I started getting “Package Load Failures....” when opening any project. After some search on the internet, I came across this article: http://blogs.msdn.com/astebner/archive/2005/04/16/408856.aspx and also this one: http://blogs.msdn.com/astebner/archive/2005/04/19/409905.aspx. The symptoms explained in these posts were the exact ones that I'm facing (basically old Beta 2 bits lying around) and after going through the cleanup tool recommended in these articles, I got around 95% of the IDE working. The problem that I'm having are with the BI Studio components of SQL Server 2005. When I create a new Integration Services project, everything loads fine, but if I drag-and-drop some of the toolbox items like foreach containers etc, nothing happens on the design surface. However, other toolbox items like data flow, database backup etc are working fine. Not sure where the problem is.
Incidentally, I had to run the cleanup tool more than once and each time I run it, it still says that it has detected the Beta bits around. Thus, am not sure if I need to run it more times. Anyways, am almost there and should soon cleanup the other problems. For now, I'm happy with the revised performance that I'm getting by running the SQL Server 2005 and VS 2005 IDEs on my host machine :-) Also got back the 8GB that my earlier VPC had gobbled up!
People who have worked with SQL Server are familiar with table valued fuctions (TVF). A TVF is one that returns as its output a table which can then be used as part of a SELECT statement or consumed in other manner. One of the things to remember with T-SQL TVFs is the fact that when you request for output from the function, SQL Server materializes the results into a temporary table and then returns the results to you. This means that if the function returns a few thousand rows, it might take some time for your query to start processing the results from the function.
With the integration of the CLR into SQL Server 2005, you now have a new variant of the TVF. Its called the streaming TVF and its written using .NET code. The mechansim here is very similar to how you would implement objects that implement IEnumerable and IEnumerator. The SQL Server query engine calls into a method in your class that implements the IEnumerable interface and then iterates over the various elements using the GetEnumerator method that returns an instance of another object that implements the IEnumerator interface. Where the behavior of the streaming TVF is different from the regular T-SQL TVF is that, in the streaming TVFs, the results are made available as soon as they are present and your client application can continue to receive them without having to wait for all the results to be materialized. This can result in faster applications that call TVFs which return large rows. The implementation mechansim is very simple, however, because of the streaming behavior, there are a few limitations, but they are quite minor.
I've written an article about this new feature at my web site: SQLCLR Streaming Table Valued Functions (TVFs). This articles explores the basics of this feature and shows the equivalence in the .NET world and how the new SQLCLR version works. Do send me your feedback about this article at srisamp@gmail.com.
The whole of last week (October 24th - October 28th) was spent in training on the new features of Visual Studio 2005. This was a training program done by Microsoft for ISVs who are about to embrace the new platform. The training was extremely useful and we got to try out hands on some of the cool new features. Some of things that I especially liked are the new Web Parts feature in ASP.NET 2.0 and some of the data binding enhancements in the smart-client world. Also of great use is the new set of server controls for easing the development of common tasks like Login, Menu creation etc. Its amazing how little code you now need to write!
Overall, a very useful training and am starting to try my hands in a few of those features for a project that we are working on.