One last SQLCLR feature I'd forgotton about but was quite highly publicized. This is extension of SQLCLR UDT and UDAgg maximum size from 8000 bytes to 2gb. You just the MaxByteSize of -1 in the appropriate attribute, like this:

[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=-1,IsNullIfEmpty=true)]

The serialization format is going to have to be UserDefined, the limt for Format.Native is still 8000 bytes.

Just catalog the UDT or UDAgg and use it as you would any other TYPE/AGGREGATE. I crufted up a proof-of-concept one pretty easily, and the new spatial data types (GEOGRAPHY and GEOMETRY) are also large (system) UDTs, so you know this works as of CTP5.

Now you can have your "infinite" UDT that holds an array or "infinite" string concatenation UDAgg. Enjoy.

Another cool SQLCLR feature in SQL Server 2008 is the ability to define a table-valued function with an ORDER clause. If you have intimate knowledge that the function always returns rows in sorted order, you declare your SQLCLR UDF with an ORDER clause in the CREATE FUNCTION DDL statement. Let's experiment with this using the cheap and easy Fibonacci sequence function from Dan Sullivan and my SQL Server 2005 Developer's Guide.

create assembly orderedtvf from 'C:\temp\OrderedTVF.dll'
go

-- no order clause
create function FibonacciUnOrdered (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
as external name orderedtvf.FData.Fibonacci
go

The Fibonacci sequences generated are always in ascending order because that's how the function is implemented. In fact, they are in order by both the "next" and "prev" column. Because there is no limit to the number of method signatures we can have over the same SQLCLR UDF, we use the same SQLCLR method, just changing the DDL statement and function name.

create function FibonacciByNext (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (next asc) -- this is new
as external name orderedtvf.FData.Fibonacci
go

create function FibonacciByPrev (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev asc) -- same concept, different ordering column
as external name orderedtvf.FData.Fibonacci
go

Now let's do some testing. The query plan iterators and plan cost are shown as comments.

-- cost: 0.0279081
-- TVF -> Sort -> Select
select * from dbo.FibonacciUnordered(3,4,5)
order by next

With an ordered TVF, there is no SORT iterator, but there are extra query plan steps to operate on the ordered set. AND... the query cost is over 10x lower.

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by next

-- cost: 0.0023802
-- TVF -> Segment -> Sequence Project -> Assert -> Select
select * from dbo.FibonacciByPrev(3,4,5)
order by prev

Note that it IS best to have a different TVF name for each sort order, and "no order", if you plan to use different ORDER BY clauses. This one has a Sort AND its cost is greater than the function that's declared UnOrdered

-- Different order
-- cost: 0.0292881 (more than Unordered = 0.0279081)
-- TVF -> Segment -> Sequence Project -> Assert -> Sort -> Select
select * from dbo.FibonacciByNext(3,4,5)
order by prev

In addition, the query plan guarentees that you don't lie in your order clause. Here's proof.

create function FibonacciWrong (@prevstart int, @start int, @rows int)
returns table (next int, prev int)
order (prev desc) -- THEY ARE IN ASCENDING ORDER, NOT DESCENDING
as external name orderedtvf.FData.Fibonacci
go

-- Error:
-- The order of the data in the stream does not conform to the ORDER hint
-- specified for the CLR TVF 'dbo.FibonacciWrong'.
-- The order of the  data must match the order specified in the ORDER hint for a CLR TVF.
-- Update the ORDER hint to reflect the order in which the input data is ordered,
-- or update the CLR TVF to match the order specified by the ORDER hint.
select * from dbo.FibonacciWrong(3,4,5)
order by prev

Remember, you're not only saving a SORT iterator in the query plan, you're saving memory too. The SORT iterator requires a memory grant. And because there are no stats for SQL Server to use in these "opaque to SQL" functions, the memory grant for the SORT iterator in FibonacciUnOrdered is 1024K. And, we hope that the rather generous memory grant is enough, else memory is being allocated during query execution.

So, declaring ordered TVFs is worth it.

One thing that I didn't find in the BOL What's New page is some of the new SQLCLR functionality in SQL Server 2008. The first one that intrigued me is support of multi-input user-defined aggregates. Suppose I wanted to implement Oracle's COVAR_POP aggregate, an analytic function that returns the population covariance between two expressions. The signature is COVAR_POP(expr1, expr2) and I want the signature to stay the same in SQL Server.

All that I need to do this is to use the "template" for a .NET UDAgg struct/class, replacing the Accumulate method that take one parameter with a 2-parameter method, like this:

public void Accumulate([SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value1, 
                               [SqlFacet(Precision = 20, Scale = 10)] Nullable<decimal> Value2)
{
  // code here
}

My CREATE AGGREGATE DDL statement would change a bit:

CREATE ASSEMBLY multiparmagg FROM 'C:\temp\multiparmagg.dll'
go

CREATE AGGREGATE dbo.covar_pop(@expr1 decimal(20,10), @expr2 decimal(20,10))
RETURNS decimal(20,10)
EXTERNAL NAME multiparmagg.CovarPop;
go

To invoke:

create table dbo.test_covar (
  i1 decimal(20,10),
  i2 decimal(20,10)
);
go
-- fill with data, then...
select dbo.covar_pop(i1, i2) from dbo.test_covar;

Happy aggregating.

With any new product or CTP, I've always gotten fast results by starting with the readme.txt file that comes with the product/CTP. Although its now called ReadmeSQL2008.htm, its still worth reading. This usually gives you answers about install scenarios and last-minute changes.

For example, this readme file answers two of the (so-far) FAQs about CTP5.
  What happened to Surface Area Configuration Utility?
  Can I install SQL Server 2008 CTP on Windows Server 2008? (ie, is it officially supported)

You'll need to RTFR for answers to these.

Right after the readme file, the first thing to do (after you've installed it) is to take a gander at the SQL Server Books Online, in the "What's New" section. Here's a bookmark for those without the facility to look in the TOC. mshelp://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10sq_GetStart/html/6a428023-e3cc-4626-a88a-4c13ccbd7db0.htm.

There's WAY TOO MANY new features in this CTP to describe in a few sentences. And the BOL writers have really had their hands full with this CTP, by the looks of the sheer volume of new material. Enough to keep anyone reading and experiementing for weeks. However...

Every once in a while something slips through the readme and What's new, or isn't finished yet, like the "What's new in SQL Server Installation" section. Or folks think of new ways to use features the BOL writers hadn't thought of. Or want to expound on the repercussions of feature XYZ. Stay tuned for these.

SQL Server 2008 adds the concept of priority for conversations. It's setup using special DDL statements, priority cannot be specified on the CREATE DIALOG CONVERSATION or SEND/RECEIVE DML statements. The DDL statements are CREATE/ALTER/DROP BROKER PRIORITY.

To specify a priority, you associate a BROKER PRIORITY object with combinations of the qualifiers LOCAL_SERIVCE_NAME/REMOTE_SERVICE_NAME/CONTRACT and the priority is associated with all messages and conversation endpoints that match that combination. The wildcard 'ALL' (or leaving the qualifier out entirely) is permitted for any or all of the qualifiers, and matching precidence is specifying in BOL under the CREATE BROKER PRIORITY syntax. The set of defined priorities is stored in sys.conversation_priorities metadata view.

In addition to this setup, the database has to be set to use priorities, with the "ALTER DATABASE...SET HONOR_BROKER_PRIORITY ON" DDL statement. The default behavior is not to honor priorities.

Once you define BROKER PRIORITY(s) and set the database to honor them, priority (default is 5) will be set on:
 sys.conversation_endpoints
 each message in the queue 
 sys.transmission_queue messages

Service Broker priorities are a much asked-for feature and its good to see it implemented. Because this is a new feature in CTP5, there are still some rough edges that need fixing. You can't set honor_broker_priority on CREATE or ATTACH database. And using the SMO scripter (Script As/CREATE on the database in Object Explorer) doesn't set honor_broker_priority either.

There are some really nice examples of the syntax and the concept in BOL. I've enclosed a really simple starter script that changes the default priority and illustrates the metadata. Enjoy.

SQL Server 2008 adds support for the 'let' clause in FLWOR expressions. The for and let clauses have a similar purpose, to bind content (tuples) to variables.  Either one can begin a FLWOR expression:

declare @x xml = '';
select @x.query('
for $i in (1,2,3)
return $i
');
> returns 1 2 3

declare @x xml = '';
select @x.query('
let $i := (1,2,3)
return $i
');
> returns 1 2 3

The distinction is that let is an assignment clause, in the simple statement using 'let' above, $i refers to the entire sequence (1,2,3). The for clause sets up an iterator. The simple statement above using 'for' loops 3 times and each time through the loop $i refers to a single member of the sequence. So, if I add an 'order by' clause, the results are quite different.

declare @x xml = '';
select @x.query('
for $i in (1,2,3)
order by $i descending
return $i
');
> returns 3 2 1

declare @x xml = '';
select @x.query('
let $i := (1,2,3)
order by $i descending
return $i
');
> error:
> XQuery [query()]: 'order by' requires a singleton (or empty sequence), found operand of type 'xs:integer +'

One limitation on the XQuery let clause is that it does not support constructed elements. So this statement works fine:

declare @x xml = '';
select @x.query('
let $x := 1
return $x
');
> returns 1

but this statement does not:

declare @x xml = '';
select @x.query('
let $x := ( <foo>2</foo>, <bar>2</bar> )
return $x
');
> error:
XQuery [query()]: 'let' is not supported with constructed XML

So Let the use of the let clause begin...no longer do I have to explain what a "FWOR" expression is, hooray. However, nota bene. When 'let' is used inside a loop, it's evaluated each time around the loop:

declare @x xml = '';
select @x.query('
for $i in (1,2,3)
let $j := 42
return ($i, $j)
');
> returns 1 42 2 42 3 42
> $j is evaluated three times

The upcoming SQL Server 2008 provider for Powershell was a big hit and sparked quite a bit of discussion when I talked about and demonstrated it during TechEd/Developers last week. At the time I thought that this might be a good discussion topic for database administators, and, after asking around, a room was found, and I'll be re-presenting this session, entitled "Using Windows Powershell with the SQL Server 2008 Provider and SMO" here at TechEd/ITForum tomorrow (Friday) at 13:30-14:45 in room 131.

If you do administrative tasks with Powershell or are interested in SQL Server, drop by and bring your opinions. See you there.

I'm up early this morning for a repeat of my T-SQL in SQL Server 2008 talk. Things really went well for the last two days, folks really seemed to like to see lots of actual working code, albeit my examples are always "minimalist". There was a lot of interest around the Spatial data talk and some discussion of upcoming and ongoing projects that will take advantage of this new functionality. The increase in interest could be been caused by announcements about spatial data at the conference and also the opening of the new Spatial Data section on the SQL Server website. My SQL query tuning segment generated a lot of interest too.

If you're around the conference this morning and interested in SQL (and who isn't?) stop by and say hi.

Today is day 1 of TechEd/ITForum in Barcelona. I have a bit less hectic of a workload at this one, a total of 4 sessions, 3 of them being "interactive sessions" (this year's word for chalktalk). Today's session is going to be on T-SQL enhancements in SQL Server 2008, at the late-in-the-day time of 5:45-7:00pm. C'mon out and I'll see you there, or, if you can't make the late session, I'm repeating it on Wednesday.

Tuesday, I'm doing my only breakout, the "day's worth of material in 75 minutes" on SQL query performance tips and tricks. And, in the afternoon, a session on spatial data (and indexes) in SQL Server 2008. See you there...

Actually yesterday, but today was the first time I'd had a chance to write about it.

Having done quite a bit with what's now being called "traditional web services", my first impression of REST were, I'll have to admit, the thought that it was web services without schema. I kinda like my metadata; it's always been irritating that stored procedures do not store ANY metadata on the number or shapes of the rowsets returned, only metadata on the parameters are stored. The closest that ANSI comes is allowing specification of the number of rowsets, a piece of the standard the SQL Server doesn't implement. So web services without schema seemed less useful than web services with.

But one of the up-and-coming data access technologies at Microsoft is Astoria, which is described as "a REST-ful set of interfaces to relational database (and other) data". It's causing a lot of excitement.

After the conference I mentioned this to an old friend, Jon Flanders, as he was wearing his "real programmers care about URIs" (or something close) teeshirt. Asked him to explain the zen of REST. To summarize, I got the impression it was all about the location specification and using HTTP verbs like GET/POST/DELETE to effect "state transfer" operations. And, although its not very common, metadata can be specified using WADL (Web Application Description Language). So specifying the location is perhaps like a "connection string" to the service? And to the data the application interacts with?

I'm still a bit skeptical of the (seeming) typelessness and contract-lessness of it all, the "HTTP is the only protocol"-ishness, and IIRC, I can find out the location of a traditional web service using WSDL's soap:address element's "location" attribute in the service portion. But at least I have an somewhat of an understanding of what all the buzz is about.

More on Astoria in future posts.

Whew! I had a full week at TechEd Barcelona, just getting around to finishing up writing about it. It was great to get out and see everyone, there were a lot of familiar faces. Thanks for showing up and saying hello. I fully enjoyed each and every presentation and interactive discussion.

A special thank you to Gunther Beersaerts for allowing me to attend and present. Got to meet a lot of "the old gang" as well as some people who I'd only known through their blogs, like Ward Pond and Johannes Kebeck. And I got to attend the Belgium/Luxembourg country drinks party, where I had some great technical discussions over beers.

I don't have exact figures, but because I had the same room for each breakout, I "counted" interest by counting the number of people who showed up. Transactions and Optimizing T-SQL still rule, although there was almost a full room for SQLCLR. And there was more than I expected for Service Broker and quite a few folks for SQLXML. Packed a double "interactive" room for the Powershell in SQL Server 2008 presentation/discussion, although the liveliest discussions were in the "OLE DB and ODBC" and "tell us where it hurts in SQL Server" sessions. The ODBC folks were happy they aren't being forgotten, it was enlightening to see the numbers using OLE DB and ODBC. Thanks to John de Longa of DataDirect for his input on ODBC/OLE DB and Elisa Flasko for helping me moderate the discussion. A number of people seem quite worried about the advent of LINQ to SQL/EDM and what they see as the upcoming deluge of dynamic SQL and its effect on the plan cache. I'll have more to write on that subject soon.

Next week is ITForum. I won't be quite as busy, but I will be doing a few cool talks on Spatial Data in SQL Server, T-SQL improvements in SQL Server 2008 and T-SQL query tuning. See you there.

Today, I'm finishing up my (many) talks at TechEd/Developers' Barcelona with a breakout on best practices for transactions and isolation and leading an interactive discussion on OLE DB and ODBC. I love talking about transactions and, in addition to demonstrating all of the isolation levels SQL Server supports their behavior and repercussions, I'll even show the fairly esoteric "MARS batch-scoped transaction".

The "OLE DB and ODBC" discussion should be good. After seemingly moving away from ODBC since the late 1990s (actually they never moved away) there has been a movement toward embracing the ODBC lifestyle and reiterating support in the pre-SQL Server 2008 timeframe. I'll be co-hosting this one with Elisa Flasko of the Microsoft data access team.

See you there!

Tomorrow (Wednesday) I'm changing to multiple best practices session in a single day. In the morning, I'll be speaking about T-SQL query plans and "why queries run slowly" in a session called "Best Practices for Procedural SQL Code". And in the afternoon, a different data model and query language (but the same optimizer) in "Best Practices for XML data type and XQuery". Be sure to drop in if you're interested in either of those topics.

Today's surprise was a preview of the upcoming SQL Server 2008 Powershell Provider. It seemed to be a big hit with the folks that came to the session and promises to be yet another step in consolidating administrative tasks in a common form by using Powershell.

See you at TechEd/Developers Barcelona!

Today is day of SQLCLR (that is, use of .NET CLR code in SQL Server) for me at TechEd/Developers in Barcelona. I'm doing two talks on it: one before and one after lunch. The before lunch talk is a breakout-style talk, illustrating with code when its a good idea to use SQLCLR and when its not a good idea. I'll also touch on some SQL Server 2008 SQLCLR enhancements. Questions and comments at end of the talk.

The "after lunch" talk is more of an open discussion. Some folks think that SQLCLR is one of the fine features of SQL Server 2005. Other just want to know: "how do you turn that off, programmers will just abuse it". Please bring your opinions along (don't check them at the door), and contribute to the discussion. I think SQLCLR is one of the most misunderstood features of SQL Server.

After the SQLCLR discussion, I'll be doing an interactive session on SQL Server and Powershell. These two are integrated by means of a .NET library called SMO, though Powershell can also be used with SQLDMO. They'll be a little surprise in this one, but you'll need to be there to find out.

See you there!

I like free, standard, sample databases. One that I've been looking at for quite a while is the Mondial database currently available at Institute for Informatics Georg-August-Universität Göttingen. This set of data (from an old CIA World factbook and other sources), is available not only as a relational database, but as XML, RDF, and even F-logic.

The problem with using this with SQL Server has been the lack of a DATE datatype with the appropriate value range. The DATE datatype is used as "Date of Independence" and some countries have independence dates before 1753. So you'd have to represent it as a VARCHAR. Yuk.

With the new datatypes in SQL Server 2008, this is do-able without compromise, so here it is. In addition, since many of the locations (in two tables) come with latitude and longitude columns, I've added a GEOGRAPHY column, so when CTP5 comes out we'll have something to use GEOGRAPHY with.

Source scripts included. Enjoy!

Ever since the hierarchyid data type was introduced in SQL Server 2008 CTP4, there's been a fair amount of discussion about renaming some of the methods of the data type to make them a bit more intuitive. In addition, there's been discussion about "missing" methods that don't exist in the hierarchyid data type. Well...

Because hierarchyid is a .NET-based system UDT, you can implement these "missing" methods yourself. There's a couple of ways to go about this:

1. Use your own assembly that uses the hierarchyid (that's Microsoft.SqlServer.Types.SqlHierarchyId to you) as parameters. Because its a "normal" SQL Server type, you can use you use it anywhere you can use a "native" SQL Server data type, like DATE.
2. Code your own UDT that inherits from SqlHierarchyId. Perhaps call it HIERARCHYID2, to further upset those folks who are offended by DATETIME2.

Wow. Did you say inheritence from a system UDT? Before we go down this path, bear in mind that this works with the CTP4 version of SQL Server 2008, but there's no guarentees about it working in the next CTP or release. I've heard nothing to that effect, but you never know. I've coded up a little stub and it seems to work, but...

I thought that T-SQL doesn't support UDT inheritence. Technically, it doesn't. That is, the SQL Server system catalogs (sys.types, etc) don't track UDT inheritence. This means that in order to allow T-SQL to "see" methods and properties in the base class, you need to write methods that do nothing but delegate to the base class. Because all the methods that you care about are public (by definition) you can do this. Simply override the methods that you don't want to pass through. I wrote about this a long time ago, see this blog post from 2004.

A few other considerations. Your UDT must be a class, not a struct, of course. Your UDT must implement IBinarySerialize and use UserDefined serialization, because the base class does. Microsoft.SqlServer.Types allows partially trusted callers, so your assembly should be able to work with permission_set safe. Finally, remember that SQL Server does not allow you to expose overloaded methods in assemblies, although you can use them in your internal implementation.

I'm working to expand the stub implementation, so if anyone has some neat ideas for derived methods and properties, I'd be interested in hearing from you. And, when CTP5 comes out...GEOGRAPHY2 anyone? If it doesn't do exactly what you want, change it.

By the way, I've wondered if deriving from a system data type isn't analogous to using undocumented system stored procedures, a practice which everyone I meet seems to disapprove of, but they do it anyway. I'm think at this point that it's more analogous to using a documented/supported system stored procedure, like sp_spaceused, in your own script. They can't change HIERARCHYID post-SQL Server 2008 without break all existing code. So you're safe, I think. Other opinions?

There's been some rumors going 'round about the immenent release of SQL Server 2008 CTP5. OK, maybe I've been asking around too. The main reason for the rumors is that there was a published CTP timetable at one point and its getting to be about that time. The other reason is the plethora of sessions, both here in Barcelona at TechEd/Developers and TechEd/ITForum and at DevConnections in Las Vegas (where some of my SQLskills collegues are this week) about features that won't appear until CTP5. Looking at the calendar of talks I can easily spot talks on Spatial Data and Filestream storage, two of the most compelling features in the next release, due in CTP5. I'll even be doing a spot of my own on Spatial data next week at TechEd/ITForum.

There have also been a number of postings by Microsoft employees about the upcoming CTP5 features, some of them including code. If you're at TechEd, don't miss Johannes Kebeck's talk on Virtual Earth, which may have some tidbits on usage of the SQL Server 2008 spatial data types. He's been working with the spatial data types since they were just a gleam in the SQL Server team's eye. Unfortunately, I'll miss it, because I've got a talk at the same time. Maybe Johannes can fill me in. And Michael Rys' talk on spatial data in SQL Server 2008.

But back to CTP5...the answer so far is just "soon, wait and see". But do attend the sessions and I believe you'll agree its something worth waiting for.

This week I'm at TechEd/Developers Barcelona. I arrived Saturday and I'm always delighted, being a Portland Oregon resident, to see the sun, even in winter. It's supposed to be sunny and mid-upper 60s for the next 10 days and that's good because I'll be staying on for TechEd/ITForum next week too. Oh boy, my sun quota for the rest of the year...

I'm going to be doing a breakout or two and some "interactive sessions" (that's what chalktalks evolved into) every day from Mon-Thurs. Talking about best practices and lessons learned for developers in SQL Server 2005. I've been working with developers since before SQL Server 2005 shipped and now that we've had a few years to practice I feel comfortable talking about it. In each session, there will be a "look forward" to SQL Server 2008 new features.

Today is day of Service Broker, one of the least understood features of SQL Server 2005. In the best practices session I'll be rewriting some of my older code to correspond with best practices and demonstrating how to use broker for performance. Performance, robustness, and co-location with the data are broker's best features when compared to other ways to implement a service-oriented architecture. 3500 transactional messages/second on commodity hardware is nothing to sneeze at.

After that, I'll be "hosting" and interactive session on Service Broker lessons learned. We'll discuss real world use cases, see what design issues folks have been running into and how they've been addressed. Bring your questions and comments about your own projects to contribute or just come along to listen in.

Later in the week, I'll be covering T-SQL queries and plans, SQLCLR, transactions and isolation, XML data and query, and have some discussion sessions on two of my favorite topics: native data access APIs (OLE DB and ODBC) and SQL Server/Powershell integration. There will even be some surprises: you'll have to show up to find out.

See you there!