SQL Puzzle – Schema and Table Creation – Answer Without Running Code

It has been a while we have a puzzle on this blog. Let us have a very simple and interesting puzzle today about Schema and Table Creation. Please answer this puzzle without running the code. If you totally can’t figure out why this is happening, you may run the code as well.

SQL Puzzle - Schema and Table Creation - Answer Without Running Code table-creation-800x535

Puzzle – Schema and Table Creation

Run the following code in the SQL Server Management Studio.

USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1

After creating a schema and table when you try to select the data from Table, you get the following error:

Solarwinds

Msg 208, Level 16, State 1, Line 6
Invalid object name ‘MyTable1’.

Answer the Question: When we created the schema and table, we got no error. Now, why do we get an error when we select data from the table?

Winning Prize Worth USD 1000

We will be announcing 3 winners. Each winner will get free entry to the SQL Server Performance Tuning Practical Workshop – Recorded Classes. You will get free access to the class for 30 days.

Rules to Win the Puzzle

Post your valid answer on or before October 25th, 2019 (Anywhere in the world) in the comments section.

You must be a subscriber of the newsletter on or before October 25th, 2019.

If you enjoyed this puzzle, here are a few puzzles that I had published earlier. I am sure you may find them interesting as well.

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

Solarwinds
, , ,
Previous Post
Winners and Solution – DELETE Qualified Rows From Multiple Tables

Related Posts

317 Comments. Leave new

  • Hi,
    I knew time is over. But I saw it today. first thought of mind error due to schema. Then i execute it also, and find yes my first thought is correct.

    correct statement will be for select query : SELECT * FROM MyScheMA.MyTable1

    it is just issue of scope of “MyTable1”.

    Reply
  • Battepati Anantha Krishna
    November 2, 2019 10:16 am

    Thank you 😊, I hope you can see me my answer as well. Because it’s showing me the message, “your comment is awaiting moderation” message on top of the comment.

    Reply
  • Kuldeep Kumawat
    November 3, 2019 12:11 pm

    Because MyTable1 is created under MyScheMA.
    So correct select statement would be select * from MyScheMA.MyTable1

    Reply
  • Hi there, I can’t see my answer in the comments.

    Reply
  • This was one such contest, where pretty much everyone got the correct answer and it was impossible to mention everyone’s name. There are over 300 correct answer. The judges has selected 3 different winners and each will get personalized email from the team.

    Reply
  • Dear Sir
    I have conclusion about this question sir.
    1. As per SQL Server Standards we cannot possible to execute the Create & Select Statement at time.
    2. By Defect SQL Server behavior is, If we creating Or selecting any table we need to mention below
    following four parts in Statement
    a. Server Name
    b. Database Name
    c. Schema (Table Owner)
    d. Table Name
    3. By Default SQL Server it will assigned Based on Login below Following Parts
    a. Server Name
    b. Database Name
    c. Schema Name
    Above is SQL Server Standards may I correct sir.

    Now my question is.

    USE TempDB
    GO
    CREATE SCHEMA MyScheMA
    CREATE TABLE MyTable1 (ID INT)
    GO
    SELECT * FROM MyTable1

    This above syntax we have not mentioned any Schema Name while creating table, why it was taken automatically previous created schema. This conclusion we need solve.
    Because any select statement we need to mention four parts if any missing it will give error this commonly understood,
    Why created new table recently created new Schema because we have not mention any schema while creating table this is my question.
    Sir this purely my clarification purpose sir.

    Reply
  • Whoo Hoo! Good luck to everyone.
    Keeping my fnkrs crsddtes (hard to type with crossed fingers :)

    Reply
  • Since we did not used “GO” after creation of schema, the table is created under MySchema scope level.
    so we have to write MySchema.MyTable1 in “SELECT” query.

    if we try in different way we can eliminate above error from your question
    CREATE SCHEMA MyScheMA
    go
    CREATE TABLE MyTable1 (ID INT)
    go
    SELECT * FROM MyTable1

    Reply

Leave a Reply

Menu