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…
- The table name conflicts with a keyword
- The table name is the same as another table.
- The table uses the same name as the database.
- To alias the table.
Please post your answer in the 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 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 answerer.
- Please check my Facebook page for winners 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 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
发表评论
hDcnmZ You have made some good points there. I checked on the net for additional information about the issue and found most individuals will go along with your views on this web site.
Thank you for your post.Really looking forward to read more. Want more.
Very good blog post.Thanks Again. Want more.
This is a topic that as close to my heart Cheers! Exactly where are your contact details though?
Looking forward to reading more. Great blog article.Really thank you! Cool.
Thanks again for the article post.Really looking forward to read more. Want more.
You have made some really good points there. I checked on the web to learn more about the issue and found most individuals will go along with your views on this website.
Some truly wonderful work on behalf of the owner of this web site , absolutely outstanding subject matter.
Its hard to find good help I am forever saying that its difficult to find good help, but here is
What is a blogging site that allows you to sync with facebook for comments?
we came across a cool web site which you could love. Take a appear when you want
Search engine optimization, link management services is one of the
Very good article! We will be linking to this great article on our site. Keep up the great writing.
You have made some really good points there. I checked on the internet to learn more about the issue and found most people will go along with your views on this web site.
Thanks-a-mundo for the blog post.Thanks Again. Really Great.
Looking forward to reading more. Great blog article.Really thank you! Keep writing.
Spot on with this write-up, I honestly believe that this website needs far more attention. I all probably be returning to read more, thanks for the advice!
Thanks a lot for the article.Thanks Again. Awesome.
This awesome blog is obviously educating and besides diverting. I have picked a bunch of interesting things out of it. I ad love to come back again and again. Thanks!
We all talk a little about what you should talk about when is shows correspondence to because Maybe this has more than one meaning.
I value the article post.Thanks Again. Fantastic.
Pretty! This has been an extremely wonderful article. Thank you for providing this information.
IA?AаЂаve read several excellent stuff here. Certainly value bookmarking for revisiting. I wonder how much attempt you set to make this kind of wonderful informative website.
magnificent issues altogether, you just received a new reader. What would you recommend in regards to your submit that you just made some days ago? Any certain?
Thank you for your blog.Really thank you! Awesome.
it looks good. I ave bookmarked it in my google bookmarks.
Your style is really unique in comparison to other people I have read stuff from. Many thanks for posting when you have the opportunity, Guess I will just book mark this blog.
Very nice post and right to the point. I am not sure if this is actually the best place to ask but do you folks have any thoughts on where to employ some professional writers? Thx
You have mentioned very interesting details! ps nice web site.
It as onerous to search out educated people on this matter, but you sound like you recognize what you are talking about! Thanks
Thanks for all your efforts that you have put in this. Very interesting information.
I really liked your blog.Really looking forward to read more. Really Great.
This is my first time visit at here and i am truly happy to read all at one place.
Loving the info on this site, you have done great job on the blog posts.
whoah this blog is excellent i love reading your articles. Keep up the good work! You know, a lot of people are looking around for this information, you can aid them greatly.
We stumbled over here by a different website and thought I might check things out. I like what I see so now i am following you. Look forward to finding out about your web page again.
Wow! Thank you! I always needed to write on my website something like that. Can I take a portion of your post to my website?
Simply desire to say your article is as surprising.
I?ve learn a few just right stuff here. Definitely worth bookmarking for revisiting. I wonder how so much attempt you put to make this kind of excellent informative website.
I truly appreciate this blog post.Much thanks again. Want more. here
who had been doing a little homework on this. And he actually bought me dinner because I found it for him
Updating your website frequently helps build your
I truly appreciate this blog article.Really looking forward to read more. Keep writing.
You made some decent points there. I did a search on the issue and found most people will consent with your website.
Say, you got a nice article.Thanks Again. Really Great.
Link exchange is nothing else but it is only placing the other person as webpage link on your page at suitable place and other person will also do similar in support of you.
Thanks-a-mundo for the article.Really looking forward to read more. Awesome.
Now I am ready to do my breakfast, once having my breakfast coming yet again to read other news. Look at my blog post; billigste ipad
Pretty! This has been an incredibly wonderful post. Thank you for providing these details.
Thanks for sharing your thoughts. I really appreciate your efforts and I am waiting for your further write ups thank you once again.