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 […]
Bob Beauchemin
SQL Server Blog
Large user-defined types and aggregates in SQL Server 2008
Posted on: November 21, 2007 3:39 pm
SQL Server 2008: Ordered SQLCLR table-valued functions
Posted on: November 21, 2007 12:39 am
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 […]
SQLCLR in SQL Server 2008: Multi-input user-defined aggregates
Posted on: November 20, 2007 11:24 pm
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 […]
What’s new in SQL Server 2008 CTP5? Start by RTFR and RTFBOL
Posted on: November 20, 2007 7:49 pm
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. […]
Service Broker Priorities in SQL Server 2008 CTP5 – An overview
Posted on: November 20, 2007 7:08 pm
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 […]
SQL Server 2008 XML: Let there be ‘let’
Posted on: November 20, 2007 5:02 pm
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 […]
ITForum – Day 5 – Powershell and SQL Server 2008 – the reprise
Posted on: November 15, 2007 10:11 am
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 […]
ITForum – Days 2 and 3
Posted on: November 14, 2007 7:22 am
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 […]
ITForum – Day 1 in Barcelona
Posted on: November 12, 2007 12:44 pm
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. […]
Astoria… I learned something today…
Posted on: November 10, 2007 10:32 pm
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 […]
TechEd Barcelona Wrapup…and on to TechEd/ITForum
Posted on: November 10, 2007 9:59 pm
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 […]
Barcelona Day 4 – Transactions/Isolation and OLE DB v ODBC
Posted on: November 8, 2007 8:26 am
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 […]
Barcelona Day 3 – TSQL Query Plans and XML in SQL Server
Posted on: November 6, 2007 4:39 pm
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 […]
Barcelona Day 2 – Powershell/SMO and SQLCLR
Posted on: November 6, 2007 8:17 am
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 […]
Mondial database for SQL Server 2008
Posted on: November 5, 2007 1:21 pm
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 […]
SQL Server 2008: Inheriting From a System Data Type?
Posted on: November 5, 2007 10:43 am
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 […]
Wither CTP5?
Posted on: November 5, 2007 8:17 am
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, […]
Welcome to Barcelona! TechEd and Best Practices
Posted on: November 5, 2007 7:56 am
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 […]
