SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35

Answer the simple quiz at the end of the blog post and -
Every day, one winner from India will get Joes 2 Pros Volume 1.
Every day, one winner from the United States will get Joes 2 Pros Volume 1.

Query Writing Strategy

Some people may push back on this next technique or misunderstand until getting to the very end. The goal is to have fewer errors as you write complex queries more quickly by making sure the easy stuff works first.

If you are a SQL expert who only works on the same database for the rest of your life who will never type the wrong field name in a query, then yes, you are the fastest query writer your company could ever have. Let’s face it, sometimes we as SQL experts need to get familiar with our underlying data before we get great coding velocity. Brute force or memorized keystroke solutions can be fun when learning but not good when you have a deadline and want to write queries quickly, with fewer errors, and test things as you continue to write more code. With that in mind, here is something I have yet to find in any book. When enthusiastic SQL students do this, they experience a revelation. The number of errors drops significantly and the speed at writing complex queries increases immediately.

Knowing how to narrow down what you are looking for amongst a vast list of choices helps immensely. Grabbing the right tables first and then the fields second is much like grabbing the right menu before ordering an item from it. In fact, one student named Tim took this back to his team of SQL developers and they immediately implemented this process.

We are all used to following steps we know have proven to work. Most of the time, actions are sequential from top to bottom or left to right. Other times, we complete things in phases. The two phases you are going to see here apply to joining tables or any other query that has plenty of logic. Just remember to organize first and clean up (or itemize) second.

When you go to a new restaurant, you ask to see the menu. You want to see all they have to offer. The odds are you may like half the items, but only need a few to feed your family. Looking at the menu is like starting off with a SELECT *. After looking at all the fields, you pick the one(s) you want.

Sometimes, restaurants have multiple menus. My favorite restaurant has a kids’ menu, an adult menu, a “gluten-free” menu and a drink menu. These menus were gathered at our station. Ultimately, in my head, a selection was narrowed to what our group needed.

Phase I: Organize. When you’re building a new query from many tables, you’ll find yourself wondering, “Where do I start?” Really there are three steps to this phase. First, lay the steps out by identifying which tables contain the essential data. Second, get all your joins working with a SELECT * statement. Third, add any basic filtering criteria.

Phase II: Itemize. Once all joins and criteria, such as SELECT *, FROM and WHERE are working, you are ready for Phase II. This entails going back and changing your SELECT * to an itemized select field list as your final step.

Let’s explore how this two-phase process of “Organize then Itemize” is a time-saver. We are going to use one of the challenges from the last lab. In Lab 3.2 (Outer Joins) in Skill Check 2, you needed to get four fields from two different tables. If you list all four desired fields and test one table at time, you get an error as seen on the right side of Figure 4.1.

In Figure 4.1, we write the SELECT statement and part of the FROM clause. The FROM clause will have two tables when we are done, but for now we just want to get the Location table working. When we use the SELECT *, it removes any possible errors from line 1. From there, we can focus on our more complicated join logic. We can add tables one at a time until all is working. This is the Organize phase.

SELECT * never results in an “invalid column name” error, but a SELECT list can. After your query is organized and working, you can go back and itemize the SELECT field list to display just what you want. This is the Itemize phase. These steps are broken down as follows:

--ORGANIZE PHASE: Get SELECT * query written.
-- Test first table logic
SELECT *
FROM Location
--Test second table with join
SELECT *
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
--Test all tables with criteria
SELECT *
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
WHERE [State] = 'WA'
--ITEMIZE PHASE: Change to SELECT field list
--Choose your fields
SELECT FirstName, LastName, City, [State]
FROM Location INNER JOIN Employee
ON Location.LocationID = Employee.LocationID
WHERE [State] = 'WA'

SELECT is always the first statement in a query. It’s natural to want to finish your SELECT statement before writing the FROM clause. Start with SELECT * and finish the query. Do your field list when all else is done. Use this method and you will never again get a field selection error while building queries.

Note: If you want to setup the sample JProCo database on your system, you can watch this video.

Question 4

Q. 4) Square brackets are required when…

1. The table name conflicts with a keyword.
2. The table name is the same as another table.
3. The table uses the same name as the database.
4. To alias the table.

Please post your answer in the comment section to win Joes 2 Pros books.

Rules

• Please leave your answer in the comment section below with the correct option, explanation and your country of residence.
• Every day, one winner will be announced from the United States.
• Every day, one winner will be announced from India.
• A valid answer must contain country of residence of the person who answers.
• Please check my Facebook page for winner's name and correct answer.
• Winner from the United States will get Joes 2 Pros Volume 1.
• Winner from India will get Joes 2 Pros Volume 1.
• The contest is open till my next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Filed under: CodeProject, Joes 2 Pros, Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology