SQL Server 200x wish list – part 4, T-SQL and Engine

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.

 

 

2 thoughts on “SQL Server 200x wish list – part 4, T-SQL and Engine

  1. Consider the ‘before’ table with more than one column. Now put a trigger on the ‘before’ table, on UPDATE, that changes the value of another column. So add a column ‘foo’ so that the SQL statement is about to change the value of ‘foo’ from 1 to 5, but the trigger changes it to 10. The beforefoo is 1, afterfoo is 5. Not 10.

    Cheers,
    Bob

  2. Bob, I’m a bit confused by:

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

    Doesn’t the following do it?


    use tempdb
    go

    create table before (id int)
    go

    insert before values (1)
    go

    create table #beforeafter (beforeid int, afterid int)

    update before
    set id = 2
    output deleted.id, inserted.id
    into #beforeafter

    select * from #beforeafter
    go

    drop table #beforeafter
    drop table before
    go

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.