Category: Syntax

Kenneth Fisher shows various methods for conditional ordering:

Simple sorts are pretty easy. In the ORDER BY clause you can list out the columns by name or by position in the field list and determine if you want them sorted ascending or descending. But how about going beyond that?

Every now and again you need a fairly complex sort order. Say for example you want to order your data differently depending on the Status (say DueDate if theStatus is 1, 2 or 3 and ShipDate otherwise). But you also want anyone with aTotalDue greater than $5000 sorted separately from those with a TotalDue less than $5000.

Conditional sorting isn’t something you should be doing in every query, but it’s important enough to know about.

Steve Jones shows how to use the OUTPUT clause with an INSERT statement:

I got asked a question about the OUTPUT clause recently and realized I didn’t remember the syntax. I’ve rarely used this, so I had to look it up and thought this would be a good basic post.

The idea with OUTPUT is that the data from the inserted and deleted tables can be output from the INSERT statement, outside of your triggers. This is the same data, but you can access it in the insert.

The format is

INSERT xxx OUTPUT yyyy INTO @zzz VALUES (or SELECT) mmmm

If I had one thing I could change about OUTPUT, I’d like to be able to output directly into variables for those cases in which I know I’m only going to get one result (or maybe I only care about one arbitrary result in a set).

Jason Strate shows us that you can use a UNION (or UNION ALL) to insert into a temp table:

What makes this interesting is when I am using UNION to join the results. How do you place a final resultset from a UNION, EXCEPT, or INTERSECT into a temporary table using SELECT INTO? Where does the INTO portion of the query go?

This is actually a pretty simple thing to do. The INTO for the SELECT INTO goes into the first query of the set. An example of UNIONing the results from sys.dm_exec_query_stats and sys.dm_exec_query_stats into a temporary table is provided in listing 1.

No subqueries are necessary here.

Aaron Bertrand discusses synonyms:

Let’s say you have a table called dbo.BugReports, and you need to change it to dbo.SupportIncidents. This can be quite disruptive if you have references to the original name scattered throughout stored procedures, views, functions, and application code. Modern tools like SSDT can make a refactor relatively straightforward within the database (as long as queries aren’t constructed from user input and/or dynamic SQL), but for distributed applications, it can be a lot more complex.

A synonym can allow you to change the database now, and worry about the application later – even in phases. You just rename the table from the old name to the new name (or use ALTER TABLE ... SWITCH and then drop the original), and then create a synonym named with the old name that “points to” the new name

I’ve used synonyms once or twice, but they’re pretty low on my list, in part because of network effects:  if I create this great set of synonyms but the next guy doesn’t know about them, it makes maintenance that much harder.

Kenneth Fisher points out that there are defaults when altering columns:

So here is the thing. When you change one you change them all. That means if you don’t specify a precision when you can then you get the default. That’s not exactly a common problem though. Usually what you are changing is the precision (or possibly the datatype). What is a common mistake is not specifying the nullability.

When modifying DDL, make sure that you keep it consistent and complete.

Louis Davidson on naming constraints:

It has long been a habit that I name my constraints, and even if it wasn’t useful for database comparisons, it just helps me to see the database structure all that much eaiser. The fact that I as I get more experience writing SQL and about SQL, I have grown to habitually format my code a certain way makes it all the more interesting to me that I had never come across this scenario to not name constraints.

Temp tables are special.  There’s another reason to have non-named constraints on temp tables inside stored procedures:  it allows for temp table reuse, as shown on slide 21 in this Eddie Wuerch slide deck from SQL Saturday 75 (incidentally, the first SQL Saturday I ever attended).

Andy Galbraith was trying to update a script which counts VLFs and ran into problems defining his temp table:

Michelle’s code uses INSERT…EXEC to populate a temporary table with the VLF info, and the addition of this extra column breaks the original script.  Glenn’s versions of the scripts handle this issue easily since they are version-specific – in the SQL 2012/2014/2016 versions of the script, the temp table declaration is modified to include the extra RecoveryUnitID column, which allows the rest of the script to function as designed.

—

My problem is I wanted a version of the script that could be used across versions 2005+, and this presented a problem.  At first I tried to add an IF…ELSE block to the start of the script to handle the differing CREATE TABLE statements:

This is a good example of working around a problem rather than simply giving up.