SSWUGtv
With Stephen Wynkoop
How can you tell if your employees are engaged and motivated? How would you motivate someone who’s driven by "Identity and Purpose"? Laura Rose knows the answers!
Watch the Show
Size Matters
Recently I was assisting a client with some SQL code. The code was the script to create a stored procedure consisting of a large number of TSQL statements. Each individual SQL statement was on one line with. Even with a 23” monitor, many of the commands could not be seen without scrolling from side to side.
There are a few things about working with scripting languages such as SQL that make it harder to work with, and are contribute to difficult to format code.
When a statement addresses many columns the width of the statement continues to grow. If you do place each column on a single line, then the SQL Statement gets very tall instead of wide. Again you can’t view the entire statement.
The situation was exacerbated when it was indented in nested IF blocks.
Because SQL does not support subroutines within the same code, the only reasonable option you have is the creation of additional stored procedures. A GOTO statement is supported in TSQL, but I tend to avoid that technique preferring other techniques. Breaking the work up into smaller stored procedures creates more work and creates dependencies that are harder to track and maintain. Perhaps grouping a set of stored procedures that only work together into a single file is one technique to make code more manageable and force the dependency management. Since I only work from script files rather than reverse engineering code from a database, this is a viable option.
I find that breaking up a SQL statement onto multiple lines, one per clause of the statement, results in more readable code, and reduces the need to scroll in order to see the code. What are clauses? Here are the most common:
- SELECT
- UPDATE
- DELETE
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
Starting a new line each time you use one of these clauses makes your code easier to read and to follow. It also allows the most information about what the query is intended to do to be visible on the screen at one time.
This is a little thing that separates the pro from the novice. Do you have techniques dealing with the restrictions found in TSQL language making it easier to write, test and manage? Share them with us by writing btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
DB2 SQL: Stages 3 and 4
When you are writing your DB2 SQL queries just remember to count to four… 1… 2… 3… 4… That is the order of efficiency for those types of predicates. 1 is better than 2 is better than 3 is better than 4…
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)