Category: Syntax

David Fowler understand the order of things:

This was an interesting question that I was asked yesterday and something that I’d never really thought of before. Can you delete the top x number of rows based on an ORDER BY?

Why would you want to do that? Well let’s just assume that we have a ‘people’ table and some strange bug in the application has cause the need to delete the top 10 oldest females for whatever reason (I know, it’s very contrived example and not the situation that my colleague was facing but it’ll do to illustrate the point).

Click through for one way which doesn’t work and two ways which do.

Paul Randal takes us through one of my favorite operators:

Introduced by Microsoft in SQL Server 2005, SQL CROSS APPLY allows values to be passed from a table or view into a user-defined function or subquery. This tutorial will cover the incredibly useful and flexible APPLY operator, such as how the CROSS APPLY and OUTER APPLY operators work, how they’re like the INNER and LEFT OUTER JOIN, and give you some examples of both. All the examples use the AdventureWorks example database.

Later in the article, I’ll also discuss a highly pervasive SQL Server performance problem—one I still encounter with customers on a weekly basis. This problem is related to using the APPLY operator against a specific type of user-defined function. It’s so problematic and can overwhelm tempdb, bringing your SQL Server instance to a crawl!

If you’re not too familiar with APPLY in its two forms, read the whole thing.

Michael J. Swart has a function:

Just like PIVOT syntax, UNPIVOT syntax is hard to remember.
When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS.

Click through for the function. This is an area where I wish there was built-in * logic for PIVOT and UNPIVOT. Or at least a “Select all columns but the following” as that would make things easier.

Erik Darling is not amused:

I went looking for other docs examples from vendors who have had the syntax around for 10+ years, and there wasn’t anything all that much more interesting.

Mostly case expressions and whatnot.

Big deal.

Check out the comments for more.

Erik Darling notes some improvements:

With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.

There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.

There are still some limitations but it does look like the function is considerably better in CTP 2.1.

Itzik Ben-Gan looks at ways recent T-SQL improvements:

One of the complexities that is of a specific importance to this section is comparisons that potentially involve NULL comparands, such as ones that you use in filter and join predicates. Most operators that you use in such comparisons, including the equals (=) and different than (<>) operators, use three-valued logic. This means that there are three possible truth values as a result of a predicate that uses such operators: true, false and unknown. When both comparands are non-NULL, such operators return true or false as you would intuitively expect. When any of the comparands is NULL, including when both are NULL, such comparisons return the unknown truth value. Both filter and join predicates consider unknown as a non-match, and sometimes that’s not the behavior that you’re after.

The distinct predicate (IS [NOT] DISTINCT FROM) should simplify a lot of code in the wild.