I, along with several others in my group, have quite a bit of experience with SQL-DMO (SQL Distributed Management Objects).  However, with the recent introduction of SQL Server Management Objects (SMO), we recently found ourselves taking a look at some of that functionality.  This article will take a look at and discuss some of the new features in SMO.

Just like SQL-DMO, SMO provides you with a set of objects that allows you to manage many aspects of Microsoft SQL Server.  SMO contains a vastly improved programming and object model to provide a more robust programming experience.

One of the things I have been working with lately is the XML data type in SQL Server 2005.  I am a fan of XML, and I love working with SQL Server.  When the two “officially” came together in SQL Server 2005 I could not have been happier.

The project I am working on didn’t put the xml data type to use immediately.  I am a firm believer in the concept of not using something just because it is cool (although I do love to play with all the new cool technologies coming out).  However, it recently became readily apparent that the mechanism we had in place would not fill the requirements of what we were trying to accomplish, and the xml data type seemed to be the answer.

SQL Server 2005 is awesome.  If you are using SQL Server 2005 and don’t know how cool a product it is, you need to dig deeper.  I can’t begin to tell you how many times it has saved my rear end and made my life much easier.  A task that seemed overwhelming in SQL Server 2000 is barely a drop of sweat in SQL Server 2005.  I can probably write a whole series of articles on the cool things I keep finding.  In fact, I probably will.

One of the first great new features, for example, are CTE’s (Common Table Expressions) which have made coding in T-SQL a shear delight.  They make for clean, easy-to-read T-SQL without the overhead of creating and populating temporary tables.  CTE’s automatically do that for you.

SOA.  Service-Oriented Architecture.  This buzzword has been around for quite a while now and in the last year and a half or so Microsoft has taken quite a step into anchoring themselves into the SOA soil.  It wasn’t too long ago that they announced that they were working on something really cool called “Indigo” and that it would be the latest thing that should be added to the Service-Oriented application utility belt.

The more developers read about “Indigo” they realized that it wasn’t anything to sneeze at.  They read about a fusion of current distributed-system technologies and ease of deployment.  They read about increased productivity and, lo and behold, a single programming model.  So, we (I am including myself in this “we” and “they”) began to ask “What is Indigo?”.

There is a plethora of new T-SQL features and enhancements in SQL Server 2005 and you could probably write a small book talking about all of them.  However, this article will focus on one of them, specifically, Common Table Expressions because personally they have come in handy on many occasions.

I have used a few of the other new features and enhancements and if you have not had a chance to delve into any of them, I would highly recommend familiarizing yourself with some of them, such as the new xml data type, improved error handling, query and event notifications, new DDL (Data Definition Language) triggers, ranking functions, TOP operator enhancements, the PIVOT and UNPIVOT operators, and the new APPLY operator.  There are a few others and they all add tremendous value to T-SQL developers.

This article will introduce common table expressions and go as in-depth as space will allow, showing how CTE’s can be used to improve TSQL’s capabilities and how they can help better your T-SQL.

Today’s column has caused quite a stir within the SQL Server community.  I’m not a DBA by any stretch of the imagination but there are many DBA’s who hit the panic button as soon as they found out that Microsoft was integrating the CLR into SQL Server 2005.  For them, this meant anywhere from T-SQL being replaced to giving the application developers free reign to the database and everything in between.  Fortunately, none of this is true.  This article will discuss the integration of the CLR in SQL Server 2005 and what that means for both DBA’s and .NET developers alike.