T-SQL Tuesday 24: Ode to Composable Code

merrillaldrich — Tue, 08 Nov 2011 06:54:05 GMT

I love the T-SQL Tuesday tradition, started by Adam Machanic and hosted this month by Brad Shulz. I am a little pressed for time this month, so today’s post is a short ode to how I love saving time with Composable Code in SQL. Composability is one of the very best features of SQL, but sometimes gets picked on due to both real and imaginary performance worries. I like to pick composable solutions when I can, while keeping the perf issues in mind, because they are just so handy and eliminate so much hassle and complexity.

What is composability? Here’s a quick example. Stored procedure calls do not compose. That fact creates pretty ugly workarounds like creating temp tables, guessing at their schema requirements, and using insert … exec to get back results from a stored proc -- just to work with their results in any meaningful way:

SELECT col1, col2, col2
FROM dbo.mytable t
INNER JOIN dbo.myview v ON t.keycol = v.fkeycol
INNER JOIN EXEC dbo.storedProcThatJustReturnsRows p 
    ON oops.thatWill = NEVER.work –- #FAIL

This is because the results returned from a stored procedure are not really defined in terms of shape: the server cannot tell what to expect pack from a stored procedure, if anything, and therefore cannot allow that result to be composed in the context of an enclosing SQL Statement. That limitation is why I listed insert-exec among my Top 10 T-SQL Code Smells.

On the other hand, structures that do have a well-defined “result shape,” including views and table-valued functions, are composable:

SELECT col1, col2, col2
FROM dbo.mytable t
INNER JOIN dbo.myview v ON t.keycol = v.fkeycol
INNER JOIN dbo.myTVF( @someParameter ) ON ...

This provides a huge amount of flexibility when writing code.

The very best composable behavior is when the code also allows the SQL Server query optimizer to unpack and optimize the content of the composed structures in the larger context of your query, which is true in most cases for views and inline table-valued functions. There are some specific limitations that can prevent this type of expansion/optimization, and, of course, it’s important to be aware of those issues from a performance point of view. But in general, if you can allow the optimizer greater flexibility in choosing a query plan, it at least has a chance at improving performance.

Example: suppose I have a stored procedure that returns 10 columns and 100 rows, and I only really need the values from 2 columns in 25 rows. If I have to execute the proc, output all the results to a temp table, then work on them, there’s a decent chance the optimizer could factor out quite a lot of the work involved if it were given more options. Encapsulating the procedure code so fully as to force the optimizer into a specific execution sequence can actually limit the ways the query could be executed, and thereby “defeat” one of the server’s primary and most valuable features.

I don’t mean to argue that stored procs don’t have a place, because they do. I also would not argue that this type of inline optimization always works, because it doesn’t. But in the main I prefer to take advantage of composability when I can, only optimize back from that where there’s a legitimate reason to do so, and try in those cases to understand why optimization isn’t working.

(And three cheers to the SQL Server team for following this philosophy in moving to dynamic management views and functions and away from diagnostic stored procs!)

How to Run a Series of T-SQL Scripts in a Specific Order

merrillaldrich — Mon, 12 Sep 2011 16:36:37 GMT

Another post in the handy-but-not-bleeding-edge category.

In the past few months I’ve seen a number of folks struggle with how to reliably/repeatedly execute a heap of T-SQL Script files, in order. One could certainly argue about why there’s the need for piles of scripts in text files, but that’s outside the scope of this post – today I want to focus on how to “get 'er done,” and save that philosophical discussion for another time.

Problem: Heap o’ Script Files

You receive a folder full of scripts from (who else) your ISV, together with a list of the files in the order they should be executed. This is obviously fraught with opportunities for error if one were to manually execute them each, one at a time.

Workaround: SQLCMD and :r

Thankfully, there’s a very easy way to encapsulate this with a SQLCMD “master” script to call all the individual files, predictably, in the correct order. The :r construct in SQLCMD is a file include, which means you can easily make a script that calls another text file, or many other text files, from disk.

First, copy your ordered file list and paste it into a query window. Be careful to avoid whitespace before and after each line of text:

somefile.sql
another file.sql
thirdfile.sql

Set the query to SQLCMD mode.

Next, use a regex find/replace to

Prepend each line with the text :r "yourFilePath\
End each line with a double-quotation mark, "
Add a GO batch terminator between each line

That is, find any line containing some text, using this regex:

^{.+}

Then Replace All using something like:

:r "c:\\some Folder\\\1"\nGO

The \1 within the replace expression will be populated with the original text from each line, which will, in effect, surround the original text with the quotation marks, file path, and GO. The extra backslashes are required to escape special characters including newline and backslash itself.

The resulting script should look like this:

:r "c:\some Folder\somefile.sql"
GO
:r "c:\some Folder\another file.sql"
GO
:r "c:\some Folder\thirdfile.sql"
GO

Be careful with subtleties like whitespace around the file names – regex is tough to read and get correct, so it’s easy to make an error. It might take a couple of tries.

At the top of the script, again, you may also want this line:

:ON Error EXIT

This will cause the client to abort on any error from the scripts, instead of continuing.

Check, triple check, quadruple check your work, and you should have a meta-script that will call all the SQL files in order.

Cheers, and happy scripting!

Search results matching tags 't-sql' and 'productivity'

T-SQL Tuesday 24: Ode to Composable Code

How to Run a Series of T-SQL Scripts in a Specific Order

Problem: Heap o’ Script Files

Workaround: SQLCMD and :r