“Fixing” T-SQL

In a comment to my previous post, Rich asked

Does this mean you’re the person to fix T-SQL programmability?

I honestly don’t know the answer to that question because, coming from the outside, I’m not sure about everything that’s wrong with T-SQL. I’d love to hear more from anyone who’s got an opinion (and any pointers to complaints around the web would be welcome as well). You can also feel free to use my contact form to talk to me directly. I can’t promise I can do anything at this point, but I’m looking to learn.

What I can do is offer two thoughts about why I chose to move over to the T-SQL team:

  1. Even though I’ve been interfacing with SQL Server for nearly 20 years in one capacity or another, I only know a surface amount about it-basically, some standard SQL and that’s about it. A lot of the reason for that is because T-SQL always seemed, well, a little arcane from the outside. I sort of got the basics of querying, but beyond that I never felt I really had the time to figure things because they looked. complicated. Some of that, I’m sure, is just the usual “people look strange when you’re a stranger,” but I have always wondered what might be done to make SQL Server and T-SQL a little more approachable.
  2. Along those lines, one recurring situation that I’ve found with SQL Server (both personally and observing others) is that the perceived impenetrability of T-SQL causes people to waste the wonderful opportunity to leverage the power of the server and instead use up precious time sucking data down to the client just to do a bit of processing that could easily have been done as a part of the query. The question comes to mind: what could we do to enable people to more easily capitalize on the server resources that are available to them, and how can T-SQL play a part in that?

So that’s what my hopes are, we’ll just have to see how it plays out.

9 thoughts on ““Fixing” T-SQL

  1. RichB

    Relational Databases are general purpose data stores. And that is part of their problem.

    I want to get my data out of the database and into objects.
    I want to work with my data as an object graph.
    I want to query across this object graph efficiently.
    I want to ad-hoc join between fields in the object graph that have occasional relationships.
    I want my queries to use rich library APIs from the .Net intrinsics in their predicates.
    I want null propagation semantics in my query projections.

    It turns out that ORMs and mature query languages like HQL currently get me closer to my goals than T-SQL. But T-SQL still wins in a couple of the above areas – can you tell which?

    Please don’t believe that pushing more querying into the database engine is the best, or even optimal approach. A more optimal approach is to pull data to the client (ie AppServer) and then manipulate it there using the Unit of Work cache to save network round-trips.

    This is why ORMs are very popular in .Net-land and virtually ubiquitous in Java-land.

    But there is still a serious need for T-SQL to match the competition (PL/SQL). However T-SQL may not be the ultimate answer – an updated T-SQL is needed just to reach competitive parity. The ultimate answer lies somewhere between T-SQL and LINQ.

    Reply
  2. Andrés

    Would you pleeeeaase implement a way to do date comparisons that disregards the time part?
    You always end up having to fallback to awful things like casting, convert or dateadd that make the comparisons of something simple look unnecessarily tedious and long. And some of those don’t perform very well.

    A very common case where this gets uglier is when using "BETWEEN" (Where table1.date between table2.datestart and Table2.dateend), since you have to make sure you use 00:00:00 for datestart and 23:59:59 for dateend.

    Just today I was having to fix issues in some reports that were returning inaccurate data because of that.

    Pleeeease? 🙂

    Reply
  3. Pingback: Anonymous

  4. Pingback: Anonymous

  5. Jamie Thomson

    When I can write:
    SELECT * FROM [sproc_name]
    then a lot of my T-SQL grievances will be solved. until that day….

    (and no, SELECT * FROM [Table_valued_func] is not adequate)

    Reply
  6. Jamie Thomson

    2. Build a replacement for T-SQL.

    No joke there. T-SQL is nearly 20 years old now. We need something more modern; the .net guys get shiny new languages to use, why can’t we as well?

    Reply
  7. MattH

    T-SQL is for working with data declaratively in sets. That’s where a lot of the outsiders’ confusion comes from, not from any inherent impenetrability of the language.

    That said, there’s plenty of room for improvement in T-SQL. Consider the following example: Suppose I have a table that I use to track drive free space over time. The columns are Server, DriveLetter, DateTime and FreeSpace. I want to find out what was the free space value for each drive at the most recent sampling. I end up writing:
    SELECT s.Server, s.DriveLetter, s.FreeSpace
    FROM (SELECT Server, DriveLetter, MAX(DateTime) AS LastTime FROM DriveSpaceHistory GROUP BY Server, DriveLetter) AS m
    INNER JOIN DriveSpaceHistory AS s
    ON m.Server = s.Server
    AND m.DriveLetter = s.DriveLetter
    AND m.LastTime = s.DateTime

    This way of querying the database isn’t completely without its charms. The derived table or inline view gives me a way of breaking up the problem into smaller pieces. Nevertheless, this is a pattern that I end up writing in T-SQL all the time and I’m thinking there ought to be a better way to express this. I bet the query engine could return the results faster with a more straightforward representation of what I want too.

    Reply
  8. John

    Jamie,
    T-SQL is not 20yrs old….SQL is 25 yrs old. T-SQL is effectively only 10 yrs old at best. DBMS is well over 30 yrs old and is bad need of rebuilding as a whole technology. You wanna fix T-SQL, how about resolving the SQL kernel for processing requests.

    Thomson,
    I shiver at that thought of why you would want to overpower the SQL server with process a USP from withing a USP, from within a USP, etc (to the N-tier +1). You may want to rethink your DBMS design if you want to use USP’s as a table retun within a Join statement from within another USP.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *