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

Answer 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 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, sometime 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 for 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 comment section to win Joes 2 Pros books.

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 1.
Winner from India will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35

Related Posts

204 Comments. Leave new

  • Srikanth Nallamothu
    August 9, 2011 11:57 am

    Hello guys,
    I am suffering with square barackets
    I want to find procedures list which involes the object [MA].[Employee]

    SELECT name,object_definition(object_id)
    FROM sys.procedures
    WHERE object_definition(object_id) like ‘%[MA].[Employee]%’

    But it is not working properly.
    can any one give a solution for this

    Thanks & Regards
    Srikanth Nallamothu

    Reply
  • Francisco Gil
    August 12, 2011 3:11 am

    Answer #1 is correct :

    Square brackets are required when the table name conflicts with a keyword,

    Francisco,
    Miami

    Reply
  • The correct choice is 1

    Reply
  • Vinit Prajapati
    August 16, 2011 3:36 pm

    The table name conflicts with a keyword
    Option 1
    I am from India

    Reply
  • Uday Bhoopalam
    August 16, 2011 8:59 pm

    square brackets are required when the table name conflicts with the keyword.

    Uday
    USA

    Reply
  • shekhar bahuguna
    August 19, 2011 11:45 am

    Hi answer is first option
    The table name conflicts with a keyword

    Thanks
    Shekhar Bahuguna
    Delhi India

    Reply
  • Answer is #1:
    The table name conflicts with a keyword

    Thanks,
    Wayne

    Reply
  • answer is #1
    are required is when a column or table name is the name of a built in SQL Server function or keyword

    Reply
  • Thank you for every other informative web site. The place else may I am getting that type of information written in such a perfect method? I have a project that I’m just now operating on, and I have been on the look out for such info.

    Reply
  • Anjana Chandrabose
    August 22, 2011 2:46 pm

    Answer :Option 1
    The table name conflicts with a keyword

    Other Options cant be possible as we cant create a table whose name already exists.so do others.

    Anjana(India)

    Reply
  • Answer Option 1

    Reply
  • Correct Option 1
    The table name conflicts with a keyword

    Reply
  • Geetanjali Agarwal
    August 30, 2011 6:01 pm

    Correct Option 1

    The table name conflicts with a keyword

    Reply
  • Sreejith Vijayan
    September 2, 2011 12:29 pm

    Square brackets are required when…
    The table name conflicts with a keyword

    Sreejith Vijayan
    India

    Reply
  • Namburi Zithendra
    September 6, 2011 12:26 pm

    Ans: 1 The table name conflicts with a keyword

    India,bangalore

    Reply
  • I’m sorry I don’t have an answer, but a question. I have yet to fully understand joins. What did you mean specifically by “get all your joins working?” Did you mean syntactically correct and not throwing errors, or did you mean only returning at most the same number of rows as exist in the first listed table, assuming no filtering and a left/inner join? I feel like anyone who can REALLY understand joins and perform them perfectly, will inevitably become a spectacular query writer. :)

    Reply
  • when the table name conflicts with a keyword

    Babitha.P
    Bangalore,India

    Reply
  • when the table name conflicts with a keyword

    Sankar
    India

    Reply
  • Mohamed Asif
    August 1, 2012 6:53 am

    Ans : 1 ) The table name conflicts with a keyword
    Country : India

    Reply
  • Bhargav from surat
    August 17, 2012 3:41 pm

    The table name conflicts with a keyword

    Reply

Leave a Reply Cancel reply

Exit mobile version