As we on the Business and Financial Management Peer Group Steering Committee bring our "12 Days of BFM" to a Close, we would like to wish you all a very Happy Holidays. We hope you have enjoyed the blog posts and that they have been helpful and useful to you. We look forward to providing you another year of valuable content in 2014!
This last post is geared toward the financial folks that love or love to hate SQL (but must use it anyway!) I've assembled a few tips for getting the most out of SQL and hoping you will have some hair left at the end.
1. Use proper formatting
One of the best techniques you can leverage in writing SQL code (or any code for that matter) is to use proper formatting. While "SELECT * FROM People" might not be too complex to read, more detailed queries should be properly formatted to aid understanding as well as trouble-shooting, both by you and subsequent users. While there isn't a set standard for formatting, it is generally accepted that reserved words should be capitalized with the remainder following the database schema. Also, it is helpful for each clause to be placed on its own line rather than strung together like a sentence. Further, using select indentation can also help legibility for complex queries.
2. Use standard language
While there are many reasons to use ANSI 92 standard SQL, I'll focus on three. First, it will help future proof your query. Many old functions and methods have been or are being deprecated. You cannot count on support of these items as you upgrade your SQL servers over time. Second, following the standard will also aid in legibility, especially around the joining of tables. Last, using the standard table joining method, you can gain much more control over outer joins and filtering tables in an outer join.
3. EXISTS Statement
Most frequent SQL users have encountered the "IN" operator when writing code, but many have not seen or utilized the "EXISTS" condition. "IN" is often used to evaluate a field meeting a discreet number of possibilities (SELECT * FROM People WHERE People.Title IN ('Associate','Partner')). However, a subquery can also be used within the "IN" statement (SELECT * FROM People WHERE People.Title IN (SELECT Title FROM Titles WHERE Active='Y')). This is where the "EXISTS" condition can become very handy. Rather than use the "IN" operator, you can use the "EXISTS" condition (WHERE EXISTS (SELECT * FROM Titles WHERE People.Title=Titles.Title AND Active='Y')). When you are working with large datasets, the "EXISTS" condition will execute much faster than using the "IN" operator. You can also join on multiple fields using "EXISTS" where you are limited to one field when using "IN".
4. Common Table Expressions
The Common Table Expression is probably one of the most useful features available for SQL Server querying. In the old days, if you needed to join a table to a query, you would need to utilize a subquery. The subquery would be defined in the "FROM" statement, and it could lead to very confusing queries. The introduction of the Common Table Expression helped to drastically simplify this and make it much more legible and easy to understand. You can also define multiple Common Table Expressions within a single query and then join them all together and/or to other database tables.
WITH MaxTitleDate AS (
SELECT
PeopleID,
Title,
MAX(StartDate) AS EffectiveDate
FROM
PeopleTitle
GROUP BY PeopleID,Title
)
SELECT
First_Name,
Last_Name,
Title,
EffectiveDate
FROM
People
INNER JOIN MaxTitleDate ON People.PeopleID = MaxTitleDate.PeopleID
WHERE Active='Y'
5. Limit Cross-server joins
The last tip I'll leave you with is to limit cross-server joins. Often, we have data that resides on multiple servers and there is a need to work on a project that uses multiple data sources. SQL Server has a handy tool called a Linked Server which allows you to connect across servers and join tables from various locations into a single query. If you are working with small data sets, this is rarely a problem. However, you may notice very poor execution times when working with larger datasets. In order to improve these execution times, one must often work to consolidate the data to a common location. One way to do this is to pare down the data on the remote server to exactly what you need, and then load it into a temp table on your local server. You can then join it to the local tables and receive much more efficient results than linking across servers.
#BusinessandFinancialManagement