Saturday, January 29, 2005 - Posts
Who: Luke Schollmeyer, GoSQLServer Co-Founder
What: T-SQL enhancements in SQL Server 2005
When: 2005-02-17 18:30:00-6
Where: Vatterott College, 11818 "I" Street, Omaha, Nebraska 68137
Why: Here's your chance learn about the new T-SQL enhancements in SQL Server 2005 from on our local Gurus. And of course, we'll have some prizes to win too!
How: Watch the GoSQLServer Web site for news and registration.
I decided it was finally time to reorganize the post catagories for this blog since things were getting a bit clutered. The new categories -- and what you can expect to find in them -- are:
| .NET | Fun with Microsoft's Common Language Runtime |
| BizTalk | Posts about MS's Process Integration Package |
| Community | News and thoughts about the IT communities I'm a member of |
| Cool Tools | Programs and technologies that I use |
| DevelopMentor | Happenings and such from the DevelopMentor front |
| Events | Event news, information and so on |
| GoSQLServer | Stuff for the Greater Omaha SQL Server User Groups |
| IronChef | Rants and raves about Iron Chef/IronChef America |
| MS MVP | On being an Microsoft Most Valuable Professional |
| Open Source | Free as in Free Beer too. |
| Out of Band | Well, whatever doesn't fit elsewhere. |
| Rants | Sometimes you've just got to. |
| Security | It's everybody's job, you know. |
| SQL Server - Admin | Somebody has to keep those devs in check |
| SQL Server - CLR | TMTOWTDI |
| SQL Server - Express | Watch the Elephant dance. |
| SQL Server - Otherwise | Think of it as a default constraint |
| SQL Server - Programming | Where all the fun is |
| SQL Server - Reporting | Because everybody reports to somebody else |
| SQL Server - Service Broker | The sleeper has awakened |
| SQL Server - XML | It’s a first class data type, you know. |
| XML | XML off the server side |
Subscribe as you like.
Over here, Roger posts a query asking exactly what FLOWR keywords the SQL Server XQuery engine supports?" and then states "...but I doubt if all the modifiers, quantifiers, etc., are supported." While Michael Rys would be far more qualified to answer that I am, here's my stab at it. I'm betting that Michael will fill in the gaps and correct my misunderstandings and omissions.
As to FLOWR, Roger is correct that LET is not supported, but order by, where and return are fully supported -- at least to the extent that I've used them with a couple of exceptions. Ordering by "empty least", "empty greatest" and "collation" aren't supported. Another limitation has to do with sequencing: heterogeneous sequences aren't allowed, so you can't a query that returns both nodes and atomic values concurrently.
As to the quantifiers, both the existential and universal quantifiers are supported using the keywords some and every. However, I'm not sure what Roger means by modifiers, though.
Note that not all typical functions are currently not supported, notably distinct-values, but these may make it end to the product by RTM (or not.)
If memory serves me right, I've commented before on what great questions Andy Mackie has been asking on the SQL Server 2005 XML news group. It seems that he's really been tearing into the performance stuff as of late too and he's been asking good questions. The latest response from Michael Rys has confirmed what I assumed about the nature of the XML indexing engines. While both posts make for interesting reads, here's the key points as I see them:
- If query performance is a primary goal for your application, you should both type (bind to an XMLSchemaCollection) where you can, and create both a Primary and appropriate Secondary XML indexes.
- The Primary XML index acts like a materialized view on a table-valued function, meaning that while they aren't actually all that useful to the query engine directly (unless the document is fairly large), but they are basis (think metadata) for the other types of XML indexes (or indices) which really do make a difference in performance terms.
- Like we should expect, the more heavily indexed a column or set of columns are, the less performant insert queries will be.
Fellow HDRite Mark Sowash has a phrase that we loves to fling at me about some Microsoft products: Feature Supported Only In The Documentation. Yeah, sometimes he's mostly right about that too. But there's one that I've not been talking about until I saw somebody from MS post on it first.
One of the really cool things that your supposed to be able to do with Visual Web Developer is that you're supposed to be able to attach to an SSX database simply by specifying to do so in the connection string. And that works in testing, but not in deployment? Why?
Security Contexts. If you're living the non-admin lifestyle, it wouldn't work from Studio either, because in Beta1, attaching to a database file requires the calling process to have administrator rights. In the recommended configuration of IIS, all active users are running in a very low right, annonymous access account, so this approach consistently generates an Access Denied exception.
Roger Wolter address this the other day in the SQLExpress newsgroup and let us know that the team is working on changing the behavior for -- hopefully -- Beta Two. That's a relief!
Now putting one and one together, you might understand why I had to abandon the "Alter View, Create Function" series that I was working on over on SSWUG. Sorry about that, I didn't want to, but I also felt it was important to not start leading folks down this path once I discovered it.
This is out in the open now, I can resume my regular programming.
Friends Tony and Stacy Spulak had their first child Thursday night: Lillie June was born at 7:24 pm last night. She weighed 8 lbs 2.6 oz. and was 21 inches long. She takes after Dad with a 14" head and 14" inch chest. Congrats guys!
I do worry about her though. Tony's just as geeky as I am about SQL Server and IIS. I wonder if Leapster has anything about Query Plans... :)
In keeping with my efforts to get up to speed on BizTalk, Alan's BizTalk brain dump on GDN has proven hugely valuable. Highly recommended stuff.
...don't you think its missing something?
I do, but its probably a case of looking for the forest when everybody else is interested in the just the trees.
What this really needs, IMHO, is a live or Webcast introduction that covers what's the big rocks of this offering -- a guided path if you will -- where the presenter talks us through the process of building apps based on one or more of the blocks. At a minimum, such a presentation should cover the configuration block, the configuration, exception, logging and data access blocks since that seems like to be what most of us will use the most frequently. Then we both see the value and have the confidence in ourselves to know where to start. I'm not saying that these things aren't possible to do with just the bits, but I am saying it would make it ton easier to have somebody show us where to get started.
I can think of at least four places you could do it too:
- As soon as possible, make it the quarterly content for the DCCs to deliver at the MSDN event series. If just do a third or if they do the whole 1/2 day on it, that's seems like a no-brainer to me.
- Webcast that, since you'll probably want to put the Web Cast on the MSDN event DVDs.
- Have at least one session at TechEd 2005 that does that. A Hands-On-Lab would be great too.
- Get your other champions and community stars to deliver it through INETA's speaker bureau, at local UG events and the various Code Camps and so on. Keep in mind, though, that these folks will likely require the most priming.
Its pretty hard to eat this whole elephant in a single bite. It is way cool (and way needed) stuff, but if you help us assimilate it, it's pretty likely we will.