SQL Server 200x wish list – part 5, SQLCLR

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.

2 thoughts on “SQL Server 200x wish list – part 5, SQLCLR

  1. The semantic would be that both parameters are input parameters and if any grouping is done, it groups by both parm 1 and 2, with parm 1 before parm 2. The reason this comes up is that a lot of Oracle’s system aggregates use more than one column as input. Right now, all I can suggest for these is to use a UDT as an input parm.

    Cheers,
    Bob

  2. The more than one parameter UDAgg is interesting, and I keep thinking about it, but it also presents many difficulties. For instance, one thing someone might want to do is create a UDAgg that will handle string concatenation. Parameter1 might be the string to concatenate, and Parameter2 might be the order in which to return the concatenated string. This would be great, as you could do:

    SELECT
    SomeCol,
    MYSTRINGAGG(MyCol, ‘MyCol DESC’)
    FROM MyTbl
    GROUP BY SomeCol

    And if it only supported string literals, this wouldn’t be a problem. As soon as you make it support a column as a parameter, you run into problems…

    CREATE TABLE MyTbl
    (
    SomeCol INT,
    MyCol CHAR(1),
    MySortCol VARCHAR(4)
    )

    INSERT MyTbl VALUES (1, ‘A’, ‘DESC’)
    INSERT MyTbl VALUES (1, ‘B’, ‘ASC’)

    SELECT
    SomeCol,
    MYSTRINGAGG(MyCol, MySortCol)
    FROM MyTbl
    GROUP BY SomeCol

    How does this work? And how does the grouping work? Do we have to do this instead:

    SELECT
    SomeCol,
    MYSTRINGAGG(MyCol, MIN(MySortCol))
    FROM MyTbl
    GROUP BY SomeCol

    … and now, how does THAT grouping work? I can see this getting extremely confusing.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.