And I'm only the fiftieth person to blog about it. That's SQL Server 2005 and Visual Studio 2005/.NET 2.0, of course. I considered not blogging and saving network bandwidth 'cause everybody already knows, but...naaaahh.

CONGRATULATIONS folks! Must be quite a party going on in Redmond. The MOST exciting set of products to come out in quite some time. One man's humble opinion, as always.

Mary always accuses me of “living entirely in the future”...hmmm...what to do now that the future is here. You've already seen my wish list for V-next. Maybe I do live in future, she's right. As always.

The last five blog entries were a lot of "off-the-top-of-my-head" list writing. Mostly remembering what people had said or the "I wish there were" comments. Just so no one misunderstands the tone....

The SQL Server 2005 version of SQL Server has more new features by order of magnitude (at least from a developer perspective, though I think a rewritten SSIS and the UDM concept qualify too) than any other delta of SQL Server. It took a herculean effort by a large number of people. We know who you are. THANKS.

There had to be some things postponed, especially with radical features, to see how relatively popular the feature would be. As an example, I've yet to see the "killer user-defined aggregate" that we couldn't ever have lived without. If you have one, let me know. I AM NOT complaining that the product doesn't have every one of these features now (in most cases), just enumerating some constructive suggestions for futures. That were solicited.

I've also been told that I don't like to throw away "abusable" features, features that are powerful but can cause problems if used incorrectly. Yep. User (programmer and DBA) education, is the solution to this. Why should users who know when to use a feature (and when not to) be penalized because some folks will potentially abuse a feature?

For the long list of things that I liked about SQL Server 2005 see the early blog entry here. I actually wrote a book (something I said I'd never do again), am working on an update to the book, and changed the company I worked for, to spend more time with this product and people who are associated with it. That's my testimonial.

Use the lists for what you will. Cheers.

OK, SQLCLR wish list comes last. Mostly because I'm tired of being accused of being a SQLCLR bigot; I think it was because our book covered those topics first. Oh well.

Default parameter values in SQLCLR procedures when called by T-SQL. You obviously can't/shouldn't support these when called from-CLR-to-CLR. Right now, they work, but the metadata doesn't recognize them. Nor does the SMO scripter.

Better exception/error handling in SQLCLR when called by T-SQL. See this blog for my 6522-saga of entries.

.NET 2.0 nullable type support for parameters. People always ask.

Overloaded methods support. As an alternative, let me define each overload with different T-SQL names. Or just define one of them.

Better integration with System.Transactions for transaction nesting.

Bring back SqlExecutionContext and SqlDefinition.

UDTs and UDAggs of greater than 8000 characters. This is a big one.

Support of IComparible (maybe in UNSAFE mode, I know why its off currently), operator overloads, inheritence, and multiple sort orders for UDTs.

More framework BCLs that comply with the "SAFE spec".

UDAggs with more than one parameter, and that support .NET generics. The more than one parameter is more important of the two.

Either support UDAggs' IsInvariantToOrder=false or remove the property from the attribute.

SSMS should display a NULL UDT value like SQLCMD does.

That's all the wishes for now. Likely some that I left out. Cheers.

Transact-SQL has been around for a lot longer, the list will be a bit smaller. But the T-SQL enhancements in 2005 were numerous but, of course, left some folks wanting more. As always.

More error handling improvements - TRY-CATCH is VERY nice. For next time, how about FINALLY and RETHROW? The ability to rethrow system errors, rather than having to convert them to user-defined errors would be nice too. The error levels and semantics could be made more consistent too, as Erland Sommarskog's dissertation on the subject points out. A personal request would be to implement some of the ANSI SQL error handling constructs, or subtly change the syntax to come closer. But no one doesn't like TRY-CATCH over @@ERROR, that I'm aware of.

An option for catalog time object resolution for procedural code. It would make sysdepends more dependable too.

Subselect support for the IN clause of PIVOT.

BEFORE TRIGGERs. I didn't ever use them much when I worked in [database that had them] but it would at least help conversion.

UPSERT (insert or update in a single statement).

When using the new OUTPUT clause, a way to get row values before or after triggers fire. You only get "before" now.

ANSI DOMAIN support.

Support for SQL/MED syntax (well, it IS a wish list, right?)

Explicit windowing functions. The new Windowing has inline only.

Support for "caching part of a rowset in memory". I'm unsure what the formal name for this is, all I keep hearing is that database [some popular database] has it.

Less restrictions on what can SQL be in INDEXED VIEWs. There is a set of restrictions that are similar for INDEXED VIEWs, Query Notifications (obvious why) and Recursive CTEs. Probably an engine optimization. The one that folks seems to ask for most is UNION support in these.

Ability to debug T-SQL procedural code in SSMS.

Using FOR XML with hierarhical CTEs. Didn't know where to put this, here or part 1.

SQLCLR is next, while I'm on a roll.

This one is about SQL Server Service Broker. It's not necessarily a cool or sexy feature, but may be the most revolutionary feature in there. The problem with Service Broker is that most people don't get it. They think YAQS (yet another queuing system). It's more than that. It's already used for 3 features inside the server (query notifications, event notifications, and mailer), there could easily be more. A SQL Agent replacement comes to mind. But I digress, this is supposed to be a wish list. If you still don't get it, think harder. Or come talk to me. Or listen. Here's wishes.

SSMS support. Currently only does display functionality. A "route, security, and message testing" piece and configuration would be nice too.

Monolog support. So I won't have to keep explaining why it's "BEGIN DIALOG" but "END CONVERSATION".

Broker support on SQL Server Mobile. For obvious reasons. It could be a discreet subset.

Object model that works for internal activation, external activation, and clients. There's a nice one in engine samples; it should be officially supported. It would be cool if it was design type similar to JMS. Plus extensions.

Supported auto-deployment. For each side of a service. Again, its been done, but not officially supported.

Same (official support) for external activator.

WCF channel. For transactional messages, broker doesn't use DTC. Makes it faster. Faster still when messaging combined with database operations.

HTTP channel. Although that can be built by third parties too.

WCF and WWF assemblies should be able to be marked as "SQL Server safe". Or they'll have to run in the external activator.

Hmmm...many/most of these contained the word "support" somewhere.

More wishes to come.

OK, because it was Michael that asked originally, XML/XQuery wishes for SQL Server 200x. In no particular order.

Full compliance with "standard" W3C XQuery. If the standards committee finishes before SQL Server 200x ships. Else it will become a "standard" no one follows. Hmm...like SQL. Reason for this is twofold.
1. Folks who get a "generic" XQuery book at the store are now totally confused. Many give up after the third or fourth "not supported" message.
2. RCG (recent college grads) who may learn "generic" XQuery in college. There's a reason why C# looks somewhat like another popular programming language. Because it makes it easier to switch environments. And code.

XQuery editor. That does schema or schemaless. Strong typing. Files or the SQL table row of your choice. They had a good start in the beta, but had to finish VS XML tools.

Client side XQuery. How can I write code that might run on server or client, when it can't run on the client? XLINQ is nice, but it's not a substitute. Should be compatible with server, modulo input and ouput.

Output in encodings other than UTF-16. A CONVERT with extra parm, like DATETIME has.

Full support for XSD schema productions. Yes, I know that its techinically outrageous. But its another source of confusion/frustration. Get an industry standard schema (even Office 2003 schemas). Can't store it without changing "lax" to "skip". But then its not the same schema if I change it.

Standard grammer for composition and decomposition. Now, I'm a big fan of SELECT...FOR XML. It's easier and more powerful than the alternative. But let's talk conversions again. Just layer on XmlElement(), XmlAttribute, etc. Kind of like CAST vs CONVERT in TSQL, they implement both.

A XML.validate method.

A more powerful modify method. But again, this could wait (but not long) for the W3C. Most people don't know that modify IS based on a standard proposal. But XDR and XML Patterns were proposals too. So maybe implement "update" (in addition to insert, replace value of, and delete) for next time. Bet SQL Server is finished before W3C. Multiple node inserts/updates/replace value ofs.

XQuery collection() semantics over rows in a table. That could be considered a collection.

XQuery 1.0 and XPath 2.0 data model (or ANSI SQL 200x data model) support. They're relatively close now.

FOR XML works with UDTs.

Ability to specify DOCUMENT on well-formed data. Like it works on schema-valid. This is a nit, I can do my own constraint. Arguably.

Finally, Node-level locking or Node-level versioning. Yes, I know. Technically outrageous. But it IS a WISH list after all.

It's just a few weeks until the launch of SQL Server 2005. I'm been hiding under a rock since returning from Hong Kong. TechEd there was a great time, biggest attendence they've ever had. Security and SQL Server 2005 were the hot topics. Yes, even after PDC in September.

It's been over three years since I got my first look at SQL Server 2005, in September 2002. I knew then it was a major undertaking; kudos to everyone who worked on it so hard for so long. I've been teaching developer topics (its hard to make a hard distinction, for example, is snapshot isolation a developer or administrator topics?) for over 2 years, since the first class of 45-or-so Microsoft folks up in Redmond. Everyone I've presented features too has been really excited. Or pretended to be excited because I was.

They're already talking about the next version and Micheal Rys (of XQuery and XML data type fame) was asking what folks would like in the next version. Thought I'd take a shot at it, at least from the developer perspective. No better time...so here goes. I'll do multiple blog entires, starting with infrastructure. It's a big WISH list, I don't expect all of them. Or even most of them. Mostly, they are customer wishes, but some are mine too.

In this arena, we could use Row-level security. It was in beta 1 for a while, and folks always asked about it. Certain business sectors require it.

Next, true ANSI DATE and TIME data types. We had them too, for a while, but they were written using SQLCLR. Some folks didn't appreciate that, they were cut. They're needed to provide a conversion path from databases that use them. Like [main competitors names go here]. Frankly it didn't bother me as much as most *how* they were implemented just that we had them. I'm still looking for the promised source code so we could compile 'em and use 'em ourselves. Whatever happened to that? You could implement them yourself using SQLCLR, but a supported version would be better.

A hint/set of options so you could keep the default READ COMMITTED locking behavior, but use READ COMMITTED versioning (aka statement level snapshot) through a hint. Oh, and while we're at it, a separate “rollback database“ instead of using TEMPDB for old versions when using versioning.

Finally, some folks asked about DML event notifications (using Broker), FILESTREAM data type, higher capacity MAX data types, and there's a set of people who always ask about bitmapped indexes. You know who you are...

XML/XQuery wishes are next.

A couple of hours after checking into my hotel on the Hong Kong waterfront, fireworks started going off outside...the real kind. Turns out I'd arrived on China National Day and the streets outside were closed because crowds had gathered to watch the fireworks display at the harbour. 56th anniversary of the People's Republic of China. I hussled myself outside to watch. Quite an amazing show.

Luckily for me I'd arrived before the streets were shut down. But after having been here earlier this year, I could have got here, open streets or not, on the excellent subway that gets to within a quarter mile or so of the hotel. It's always easier to find your way around the second time.

Tomorrow starts the pre-con for TechEd Hong Kong, which promises to be quite an event. If you haven't registered yet, you might consider it. See previous post about me, pre-cons, and too much material. Be prepared. ;-) I have some breakout sessions too, that ought to bring the pre-con material overflow down.

There's even quite a bit of new material since I was in Hong Kong last. Some folks see this as a "churn problem" with the feature list of SQL Server 2005. Ha. I see it as indicative of the immense number of features and countless hours of development. Kimberly has two slides that come to mind; SQL Server 2000 new features (a very dense slide with feature bullet points about 80-100 features) and SQL Server 2005 new features, where she has to bring the font size down about 6 points to fit them all in, almost 2-3 times as many features.

See you there.

Made a quick exit from SQLPass in Dallas, hopped on a plane, and 4 planes later (plus a stop at home to get things together) I'm in Hong Kong.

I enjoyed SQLPass immensely and by the sounds of it, so did the folks that were in my two-day long pre-con on SQL Server 2005 for developers. We filled up the room they allocated and, by the end of the day, folks were sitting on tables in the back. We did away with the traditional "lecture to a large crowd" format and I accepted questions at any time, "did requests" for demos, and generally made a free-for-all out of it.

As usual when trying to cram 5-6 days worth of material in 2 days, we went overtime; in fact the second day they had to physically extract us from the room at the end of the day. There were shouts of "just keep going" but there was a meeting afterwards in the room. They kicked us out.

People looking for the "extended demos" on the SQLskills website (where I said they'd be) may have to wait a few days while I co-ordinate things, but they ARE available on the SQLPass site now. As are the XQuery tutorial demos.

The XQuery tutorial spotlight talk was pretty well attended. The explanation was that there were way more DBAs at the conference than devs, but...surprise the language tutorial was *supposed* to be for DBAs too. If you think you'll get away without using XQuery, DBAs, have a look at the output from DDL triggers and event notifications.

BTW, I did defer one question and never got back to the questioner, so here goes...regardless of whether you use the "old or new" BULK INSERT syntax (ie the statement or the BULK provider) you need "ADMINISTER BULK OPERATIONS" and INSERT on the table in question, and some of the options may require ALTER TABLE on the table in question.

Thanks to the SQLPass conference folks and the attendees for a good time.