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)

Joes 2 Pros, SQL Scripts
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

  • Correct answer is No. 1) Square brackets are required when the table name conflicts with a keyword

    Siddhesh – India

    Reply
  • Soumya Ranjan Das
    August 4, 2011 11:20 pm

    Square brackets are required when: Option 1:
    The table name conflicts with a keyword

    Soumya Ranjan Das
    Bhubaneswar, India

    Reply
  • A) The table name conflicts with a keyword

    Suri
    USA.

    Reply
  • Q4 – Square brackets are required when…1)Table name conflicts with a keyword

    David Hunter
    San Diego , USA

    Reply
  • 4) Square brackets are required when…

    1) The table name conflicts with a keyword

    Reply
  • dilipkumarjena
    August 4, 2011 11:54 pm

    Q. 4) Square brackets are required when…
    Answer: Correct Option is 1.
    1. The table name conflicts with a keyword

    There can be many a cases where we are supposed to use this:

    1)When we make a column name or table name with space like “My Name” column here we need to mention square brackets to tell sql server that its a single column name or table name “My Table”.

    EX : select [My Name] from [My Table].

    2)While giving alias Names to columns/ Tables if we require to mention space for alias name for making it more presentable.

    use AdventureWorksDW2008R2
    select FirstName+LastName as [ FirstName LastName] from DimCustomer

    (OR)

    select FirstName as [ My Name] from DimCustomer

    DILIP KUMAR JENA

    Country: India

    Reply
  • 4) Square brackets are required when…

    Answer: 1. The table name conflicts with a keyword

    Dawn, USA

    Reply
  • Sudeepta Ganguly
    August 4, 2011 11:55 pm

    I think the answer is.. The “name” conflicts with a “keyword”. It might be the table name, column name, or the database name. For example, if we want to create a database “GRANT”, we need to execute the following command:
    USE [master];
    GO
    CREATE DATABASE [GRANT];
    GO

    However, if we execute the above command without the brackets, then we will receive error:
    USE [master];
    GO
    CREATE DATABASE GRANT;
    GO

    The same error will be return for a table as well.

    Sudeepta,
    India

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

    Vinay, Pune.

    Reply
  • The table name conflicts with a keyword

    to differentiate the table name with keywords

    USA

    Reply
  • shivakumarlakkampelli
    August 5, 2011 1:18 am

    Option 1 is Correct
    1)The table name conflicts with a keyword

    Shivakumar Lakkampelli,
    Mumbai , India

    Reply
  • The correct answer is #1, but it’s also required when field name contains spaces or other special characters. Check this interesting blog by George Mastros

    Naomi from USA

    Reply
  • Arul Prakash. A
    August 5, 2011 2:48 am

    answer : option1
    Square brackets are required when…

    1) correct. The table name conflicts with a keyword
    2) wrong, even if we give square brackets we can not create the same object (table) again
    3)wrong, even with out square brackets we can create the table same name as the database
    4) wrong, its not necessary that we should use square brackets for alias names

    A. ArulPrakash

    country : USA

    Reply
  • 1.)The table name conflicts with a keyword

    manoj india

    Reply
  • Sudhir Chawla
    August 5, 2011 6:36 am

    Answer is 1 whenever there is a conflict with a keyword square brackets are required.

    I am from New Delhi, India

    Reply
  • The table name conflicts with a keyword
    kkmjssate
    India(u.p)

    Reply
  • Abhishek Bhat
    August 5, 2011 10:03 am

    The correct answer is 1

    Square brackets are used to escape Sql keywords or spaces if a table has a space in between

    Reply
  • 4) Square brackets are required when

    1. The table name conflicts with a keyword

    We need of square brackets only at the name conflicts with sql keyword.

    Reply
  • 1 is the correct ans.

    From- India

    Reply
  • Anil kumar dubey
    August 5, 2011 12:53 pm

    The correct answer is Option 1:

    1.The table name conflicts with a keyword

    Anil (India)

    Reply

Leave a Reply Cancel reply

Exit mobile version