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

319 Comments. Leave new

  • Because the table is not assigned to the default schema, when selecting from the table, the schema needs to be specified. eg. select * from MyScheMA.Mytable1

    Reply
  • It’s because schema selection defaults to dbo. So SELECT * FROM MyTable1 == SELECT * FROM dbo.MyTable1. Do SELECT * FROM MyScheMA.MyTable1 instead.

    Reply
  • Oscar Espinosa
    October 22, 2019 7:29 pm

    When you create the schema and the table, the table is created in the new schema. When you execute the select and you don’t define the schema of the table, the execution use the default schema

    Reply
  • since table was not yet created under user defined schema

    Reply
  • Charles Gregory
    October 23, 2019 11:18 pm

    So, in looking at the puzzle I think the issue is related to the use of the go command in the tempdb. It has to start a new session, so when you create the schema, you’re fine, when you create the table your fine, when you run the go command the table gets dropped, so when you run the select there is no longer the table available. That’s my guess!

    Reply
  • CREATE SCHEMA and CREATE TABLE are in the same batch.
    Anything after a CREATE SCHEMA will be created under that SCEHMA.

    Reply
  • SELECT * FROM MyScheMA.MyTable1

    Reply
  • Answering Again: SELECT * FROM MyScheMA.MyTable1

    if we write SELECT * FROM MyTable1 then it takes the default schema so it will be SELECT * FROM dbo.MyTable1
    Since we created a schema called MyScheMA then the syntax should be schemaName.TableName.

    Reply
  • Dear Sir when we create the table with schema it is not directly access, if we select the query like it this it is working
    USE TempDB
    GO
    CREATE SCHEMA MyScheMA
    CREATE TABLE MyTable1 (ID INT)
    GO
    SELECT * FROM MyScheMA.MyTable1 — now the query is working

    and the created schema is either public or private and the log in user also having system admin and server admin roles also one cause to restrict the above query

    Reply
  • SELECT * FROM MyScheMA.MyTable1

    Reply
  • You get an error because you did not include the schema defined already ‘MyScheMA’. It should be SELECT * FROM MyScheMA.MyTable1;

    Reply
  • Because you didn’t qualify the request with the proper schema. If you simply try to query by table name the engine thinks you are looking in the default schema (dbo). Just qualify the proper schema.table relationship.

    Reply
  • While retreiving data from a table, we need to add a schema name also. The below query will work:
    SELECT * FROM MyScheMA.MyTable1

    By default, schema is dbo, but in our case we have created a schema with name as “MySchema”. So, Table “MyTable1” exists under schema “MySchema” which is required to be added befor executing the query

    Reply
  • Because its selecting the table in dbo schema

    Reply
  • Sorry a day delayed to check this puzzle and respond. The syntax presented above allows us to create new schema ‘MyScheMA’ and new table ‘MyTable1’ in same statement. Hence when the table needs to be queried, we must include schema name along with the table name to avoid the error.

    Reply
  • Hello Sir,

    The reason of it giving error is that now the table falls into “MyScheMA” schema and your default current schema is dbo or could be anything else. If you would have written “Select * from MyScheMA.MyTable1 ” it would have not thrown any error.

    Regards,
    Ashish Sharma

    Reply
  • Hi Pinal!

    Select t.name as TableName, s.name as Schemaname from sys.tables t
    inner join sys.schemas s
    on s.schema_id = t.schema_id
    Where t.name = N’MyTable1′

    Select * from MyScheMA.MyTable1

    Reply
  • Battepati Anantha Krishna
    October 28, 2019 2:57 pm

    What’s the answer, I have been checking this blog from past few, I still don’t see answer

    Reply
  • This is a very interesting behavior and I had noticed earlier.

    When we create schema without terminating the statement with GO, it is considered the same schema for the next table which you create. When we create new schema that schema is now used till the next GO. Some people do not like this as they think it is not intuitive.

    Reply
  • Yogesh Kumar Dadheech
    October 29, 2019 7:26 am

    because in Select command you not mention the schema name

    Reply

Leave a Reply

Menu