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 http://blog.sqlauthority.com which is next day GTM+2.5.

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

About these ads

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

    • Also when field name contians space for example “Project Name” [Project Name]. Since compilers take the space as separator.

      Like

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

    Rene Castro
    El Salvador

    Like

  2. Hi Pinal,

    Answer # 1 is Correct.

    Square brackets are required when the table name conflicts with a keyword. Because we can use [] brackets for field name, when you want to use reserved keywords as a field name.

    Gopalakrishnan Arthanarisamy
    Bangalore, India

    Like

  3. Question 4

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

    Following two cases also includes
    —> By mistake column created with space like Employee Id
    then also we need mention square braces

    select [Employee Id]
    From …
    —> While giving alias Names if you need to mention space for alias name

    Select firstName+LastName as [Fname Lname]
    from Employee

    Like

  4. Question 4

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

    Following two cases also includes
    —> By mistake column created with space like Employee Id
    then also we need mention square braces

    select [Employee Id]
    From …
    —> While giving alias Names if you need to mention space for alias name

    Select firstName+LastName as [Fname Lname]
    from Employee

    Country: India

    Like

  5. The correct Option is 1.

    The table name conflicts with a keyword.

    Column names can contain characters and reserved words that will confuse the query execution engine, so placing brackets around them at all times prevents this from happening.

    by
    Yasodha.N (India)

    Like

  6. Correct Answer is option : 1

    1.The table name conflicts with a keyword

    Basically square brackets are required when a column/table name is the built in SQL Server function or contains space

    Thanks.

    Like

  7. Your comment is awaiting moderation.

    Correct Answer is option : 1

    1.The table name conflicts with a keyword

    Basically square brackets are required when a column/table name is the built in SQL Server function or contains space

    Thanks.

    Country
    India

    Like

  8. 1. The table name conflicts with a keyword

    Ex.
    Suppose,We Have EmpMast Table in EMP Database Where Date Represents BirthDate Of Employee And Hear Date is Keyword. So We Have Required Square Bracket ..Hear

    Select Emp_Code,Name,[DATE] BirthDate From EmpMast Where Selected=1

    Like

  9. Option 1 for keywords, but there actually seems to be two classes of keywords, and some require the brackets and others don’t. Texas, USA. Thanks for doing this Pinal – hope it helps some people out.

    Like

  10. Correct Answer is 1.
    1. The table name conflicts with a keyword

    In another case when column name is having space then also we need mention square braces

    Like

  11. Ans is : 1

    If we use any reserved keyword than it will confuse the query execution engine. When square brackets are put around it, it will go for user created tables.

    Gandhinagar,
    India

    Like

  12. Correct answer is 1. The table name conflicts with a keyword

    Explanation : It is necessary when table name Contains Space. For example “Dept Managers”.
    Or table name is built in SQL server Function/ Keyword.

    INDIA

    Like

  13. Answer is Option -1

    I think without square brackets, the query will works in rest of the cases
    i.e, option-2,3,4

    Narendra Kotha (India)

    Like

  14. Hi,
    For option 2 and 3 the Square bracket not required bcos,
    1) We cannot create a more than one table with same name in one database.
    2) We Can create a table with same name as database within a datbasethe answer.

    The answer is 1 and 4 Bcos,
    1) If your table name conflicts with any sql keyword your have to differenciate with the use square brackets.
    4) If your alias name conflict with any sql keyword then also you have to differenciate with square brackets
    ex:
    select ‘govind’ as name, 1 as [order]
    union all
    select ‘kumar’ as name, 2 as [order]

    Govindaraj P,Bangalore,India.

    Like

  15. The correct example is here:

    Hi,
    For option 2 and 3 the Square bracket not required bcos,
    1) We cannot create a more than one table with same name in one database.
    2) We Can create a table with same name as database within a datbasethe answer.

    The answer is 1 and 4 Bcos,
    1) If your table name conflicts with any sql keyword your have to differenciate with the use square brackets.
    4) If your alias name conflict with any sql keyword then also you have to differenciate with square brackets
    ex:

    select * from emp_detail as [left] left outer join
    emp_addr as [right] on [left].id = [right].id

    Govindaraj P,Bangalore,India.

    Like

  16. Answer 1) The table name conflicts with a keyword

    Explanation :
    Square brackets are Delimited identifiers. SQL Query analyzer will skip delimited identifiers from checking for keywords and query with reserved keyword table name will execute successfully.

    Country : INDIA

    Like

  17. Correct option is 1

    we have to use square brackets around tha names in query when the column name or table name is a Reserved Keywords in sql server.

    Like

  18. Correct answer is number 1 since state is a future keyword.

    Option 2: you would need to prefix it but no need of the brackets.
    Option 3: the same name for a table and the database is allowed. Also no need for brackets.
    Option 4: usually you would use “as” to alias a table (although you could write just the alias name without it). But also no brackets.

    Germany

    Greetings,

    Michael Mikic

    Like

  19. Correct Ans. is 1

    Reasone:

    Use sqare brackets when field name or table name same as Keyword.
    it differentiat database keyword and field name or table name

    Like

  20. Square brackets are required when the table name conflicts with a keyword.
    :- option 1 is the answer.
    bcoz, table should cannot use a keyword if we put the keyword in square brackets than the key words will act as a normal word.

    Like

  21. Answer is Option 1:
    You need square brackets when table name conflicts with keyword.

    This is not a good idea of table design, but u need to have square brackets compulsary when ur having table name with space.
    Example :

    Create table [new test]
    (
    test nvarchar(30)
    )

    insert into [new test] values(‘testing’)
    select * from [jankhana shah]

    drop table [new test]

    Jankhana,
    India

    Like

  22. Hello,

    Answer is: 1. The table name conflicts with a keyword

    For resolve this problem we use square brackets with keyword

    Like

  23. Hello,

    Answer is: 1. The table name conflicts with a keyword

    For resolve this problem we use square brackets with keyword
    Country: India

    Thanks

    Like

  24. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  25. 4) Square brackets are required when…

    Answer:
    1.The table name conflicts with a keyword

    Explanation:
    this is the rule for SQL Server identifiers, that any names that are the same as SQL Server keywords or contain any spaces must be enclosed in double square brackets or doubel quotes (“”)

    Country: India

    -Loknath

    Like

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

    Malay Shah
    Ahmedabad, India

    Like

  27. Hi Pinal,

    The answer is – option 1

    Q : quare brackets are required when…

    1. The table name conflicts with a keyword

    The brackets can be used when column names are reserved words.

    If you are programatically generating the SQL statement from a collection of column names you don’t control, then you can avoid problems by always using the brackets.

    Like

  28. While there can be many scenarios to use square brackets (I use them as a standard practice in my production code for all columns), but out of the proivded choices only one scenario is when use of square braces is mandatory:

    Correct Answer: #1. The table name conflicts with a keyword

    Why?
    Database object names are identifiers (literal strings). The rules for an identifier clearly state that:

    The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words.

    When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets.

    Reference:http://msdn.microsoft.com/en-us/library/ms175874.aspx

    By the way, contrary to popular belief, one can have a table name same as the database name.

    Country of residence: India

    Like

  29. answer is 1) The table name conflicts with a keyword

    It works as a qualifier and considering them local to the DB names those are similar to keywords in SqlServer.
    another reason when we have spaces in the table/column names.

    Country of residence: INDIA
    Mincracker network.

    Like

  30. Devang Raval

    Correct Answer is 1) The table name conflicts with a keyword.

    As SQL Server has its own sets of keywords and if you choose to keep same name as these reserved key words you need to tell SQL Server with Square bracket that this is not SQLServer Reserved Key Word.

    INDIA

    Like

  31. Hi pinal sir,
    Concept of article is really simple but very useful in broader level.

    The correct answer for this question is option no. 1:

    “The table name conflicts with a keyword”

    Reason:
    The brackets are required if we use keywords in the column names.
    In our case state is column of location table and also state is sql keyword used reserved for future release of SQL server.

    Regards
    Chirag Satasiya(Mumbai – INDIA)

    Like

  32. Answer : 1

    1.The table name conflicts with a keyword

    Square brackets are mandatory whenever user provide a column name or table name which is actually the built in SQL Server function or Keyword or else the name have space in it.

    2 . The table name is the same as another table.
    we can not have duplicate table name in any case in SQL server in same database.

    3. The table uses the same name as the database.
    no requirement of square branckets.

    4.To alias the table.
    Only when alias is the default sql server keyword or it contains space in between than square required.

    Thanks & regards,
    Jigar Badgujar
    India

    Like

  33. Answer : option 1 is correct

    table name keyword conflict with keyword/ reserved word

    more

    you can use any unqualified / invalided character that are not allowed in identifier if you use []

    country : india, ahmedabad

    Like

  34. Correct answer is option 1

    In MSSQL Square brackets are used in order to remove the confusion for query execution. It may be due to confliction with reserved keywords, use of special character or space in names of DB objects etc.

    Country – INDIA (Gujarat)

    Like

  35. Correct Answer is 1 .

    1. The table name conflicts with a keyword

    One more point:
    If alias is conflict with a keyword then also square braket is required like:
    SELECT 1 AS [Merge] FROM sys.objects AS [Merge]

    Country of residence: INDIA

    Like

  36. Hi Pinal,

    Challenge:
    Square brackets are required when…

    Correct Answer:
    1.The table name conflicts with a keyword

    Brackets are also used for column names that are reserved words or have spaces or special characters in the column name (a period, for instance).

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Country:
    United States

    Like

  37. For the example you gave, the answer is

    #1 The table name conflicts with a keyword

    The brackets are also used for names that contain spaces ([my table]), to specify a range ([a-z]) or to search for certain characters like the % sign (like ‘%[%]%’ to get any results with a % sign in them)

    USA

    Like

  38. Q. 4) Square brackets are required when…

    Ans : 1- The table name conflicts with a keyword

    Following cases also includes

    When we use system column name as same as column name in our Table that time also we have to use Square brackets

    Select [date]—here Date is a system column name

    While giving alias Names if we need to mention space for alias name then—-
    Select Empid as [emp id] from EmployeeDetails

    Aditya
    Chennai, INDIA

    Like

  39. there may be some more scenarios as.. f you have spacings between table names and column names or if you have some special character in your table name or column name than you also need to have put them in to [] .

    For this question answer is

    Option-1 —The table name conflicts with a keyword

    Like

  40. Hi

    the answer is 1)The table name conflicts with a keyword

    Reason: Column names can contain special characters and reserved words that might confuse the query execution engine, so placing brackets around them at all times prevents this from happening

    Thanks
    Dhanu Dhanapalan

    Like

  41. The correct answer 1: The table name conflicts with a keyword.

    Square brackets are needed when a column or table name is the same name as a built in sql server function or keyword.

    Country of Residence: USA

    Like

  42. None of the above options.

    Square Brackets are required if the table name is a 2 letter word.

    e.g: Table Name is [Test 1]

    Country: USA

    Like

  43. 4) Square brackets are required when…

    “The table name conflicts with a keyword”

    Using keyword to differentiate column names similar to the keywords in tables.

    Chennai, TamilNadu, India.

    Like

  44. 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

    Like

  45. 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

    Like

  46. 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

    Like

  47. Q. 4) Square brackets are required when…

    a.The table name conflicts with a keyword
    b.The table name is the same as another table.
    c.The table uses the same name as the database.
    d.To alias the table.
    Answer : The table uses the same name as the database

    Like

  48. Question 4
    Q. 4) Square brackets are required when…

    Answer: 1.The table name conflicts with a keyword

    Explanation:

    It is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, you can do this only by using delimited identifiers.

    Delimited identifiers
    Are enclosed in double quotation marks (“) or brackets ([ ]). Identifiers that comply with the rules for the format of identifiers might not be delimited

    Answerer: Sivaprasad S

    Country: India

    Like

  49. The answer is
    1) The table name conflicts with a keyword.
    To allow the space in Alias name also Square brackets required.

    Like

  50. The answer is
    1) The table name conflicts with a keyword.
    To allow the space in Alias name also Square brackets required.

    Kolkata, West Bengal, India.

    Like

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

    If ‘The table name is the same as another table’ we must fully qualify the name to refer to the correct table but square brackets are not needed so (2) is not correct. If ‘The table uses the same name as the database’ then the table’s fully qualified name will be DBName.dbo.DBName but square brackets are not needed so (3) is also wrong. When ‘The table name conflicts with a keyword’ we can use square brackets to tell SQL Server we are referring to an object and not the keyword so (1) is the right choice.

    Winner from USA: Michael Mikic

    Winner from India: Jigar Badgujar

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Like

  52. Question 4
    Q. 4) Square brackets are required when…
    correct
    ans is 1 The table name conflicts with a keyword

    from INDIA

    Like

  53. Pingback: SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification Journey to SQLAuthority

  54. 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

    Like

  55. 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.

    Like

  56. 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. :)

    Like

  57. Pingback: SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5 « SQL Server Journey with SQL Authority

  58. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s