Tuesday, June 05, 2007

Tech Ed: Day 1 Wrap Up/Day 2

For me, one of the bigger product announcements this year is SQL Server 2009 (Katmai). I'm trying to take in as much content as I can, because I definitely see some of my trademark presentations being changed to account for new functionality.

For instance, and I've been thinking a lot about this over the past few months, but just haven't articulated it:

SQL Server is evolving into much more than just a transactional data system. It's becoming its own data platform. Not every database has to be highly tuned to support millions of transactions per second, and as a result, you can begin to move more of the data processing locally (by means of SQLCLR, or by taking advantage of XML processing on the server, etc). By doing so, you eliminate the need to transfer data across the wire before starting to take action upon it, which is a good thing in my book. YMMV, depending on your particular situation.

In session yesterday, they made a point that the cost of storage is quickly approaching zero, which means that we're going to have more storage than we'll ever be able to use. Suddenly, you don't need to have all of the questions that you'll be asking about your data before you store it.

For instance, let's say that you acquire a piece of data in XML format. You have several tables defined in the system, and shred the XML in order to extract the data that you need. But, you also discover that the XML has more data than you're using (more information than the tables are set up to hold). Just because you don't have a use for that extra information today doesn't mean that you have to throw it away. Go ahead and shred it to populate your tables, but then keep a copy of the XML intact. If you need to later query all of the XML for data that you never thought you would need, you have it (and SQL Server makes it easy to do so).

SQL Server 2008 will also come with support for "Policies". Like the same concept in Windows, etc, a Policy is just a rule that is executed at runtime (query time). Let's say that you don't want anyone to create tables in the "DBO" schema. Just create a policy, and the server will enforce it for you. (That's great, and all, but I'm not really a DBA, so I'm hoping that there's utility for this functionality beyond that).

Also, SQL Server 2008 comes with built-in support for spacial geometry. Do you have two geocodes and want to know how far apart they are? No problem! Find all stores within 50 miles of your customer location? Just put it in the WHERE clause. Have a complex polygon, maybe with "holes" or "rings" in it, and need to know if a point is within the polygon? Supposedly, it's a breeze with Katmai.

Last, there's "UPSERT" support. This is the ability to either perform an INSERT or an UPDATE on a table, depending on whether the data already exists or not. UPSERT is actually implemented using the MERGE statement, as in the following simple example (provided to me by Christian Kleinerman, Group Program Manager for Microsoft SQL Server):

MERGE dest_table
USING src_table ON (src_table.keycol = dest_table.keycol)
WHEN MATCHED THEN
UPDATE SET dest_table.datacolumn = src_table.datacolumn
WHEN NOT MATCHED THEN
INSERT VALUES (src_table.keycol, src_table.datacolumn)
WHEN SOURCE NOT MATCHED THEN
DELETE


Disclaimer: These were handwritten notes, and I haven't tested on a Katmai installation yet to ensure that I transcribed his example verbatim

Today, I sat in a pretty cool session led by Karen Liu (that's Dr. Karen Liu, Ph.D, btw, who likes Raytracing, so she's got to be cool in my book!) of the Visual C# IDE Team where she demonstrated some useful features in the new IDE. My take on it: There's nothing that I saw that CodeRush and Refactor! can't do. You'll definitely be happier if you bought the Developer Express product to supplement the out-of-the-box experience that OrcasVisual Studio 2008 provides.

That's it for now... Don't expect any more entries this week (so you'll be pleasantly surprised when I do in fact write one).