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.
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:
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.
- Winners and Solution – DELETE Qualified Rows From Multiple Tables
- SQL SERVER – SQL Agent Job and Backslash – Strange Behavior
- Puzzle – DELETE Qualified Rows From Multiple Tables – Win USD 1000 Worth Class
- SQL Puzzle – Correct the Incorrect Query – Win Price Worth USD 1000 – Aggregate and Subquery
- SQL SERVER – Relating Unrelated Tables – A Question from Reader
- Puzzle – Datatime to DateTime2 Conversation in SQL Server 2017
- SQL SERVER – SSMS 17.7 Observation – Two Miscellaneous Category in Database Property Options
Reference: Pinal Dave (https://blog.sqlauthority.com)
319 Comments. Leave new
When you created the table you were still in the same batch as the Schema creation so the table was created in MyScheMA, but the GO statement takes you to a new batch so it looks for the table in the default schema
Schema name was not used in the select statement
You need to add USE MyScheMA first to switch to the correct database/schema because you are now in TempDB which doesn’t have the table.
Table created in myschema…
When create table is executed with create schema in one block, the table is owned by the schema created in preceding statement and not dbo. Select * from MyScheMA.MyTable1 will need to be executed to query the table. Alternatively if we separate the create schema and create table statements with a batch separator “Go”; the table will be created under default schema dbo and the select statement “Select * from MyTable1” will work as it will default to dbo schema where the table would have been created..
you need to query the right schema, as you created one schema and a table on the schema, you con not point to a table on the database schema, but on the new created one…. hope it is clear.
Run instead “select * from myschema.mytable1”
When you CREATE SCHEMA MyScheMA with additional object creation (the create table statements) in the same batch (before the GO), the extra objects will be contained in the newly created schema.
When trying to select from the newly created table. The FROM clause in the SELECT statement did not specify the schema MyScheMA in the table_source reference. Since the currently connected user was not assigned a default schema of MyScheMA, SQL can not find the object name MyTable1 in the current users schema … The table MyTable1 exists in the MyScheMA schema.
If the select statement was written like this it would have worked:
SELECT * from MyScheMA.MyTable1
Because we created the table under schema “MyScheMA”. When we do a bare select, it is assumed we are using “dbo” schema by default.
We should use
SELECT * FROM MyScheMA.MyTable1
to get results.
The table was created by default in the dbo schema. In the select statement, the table must be qualified by “dbo.”.The creation of MyScheMA is a red herring.
Table was created without ‘;’ so statement ran fine but table didn’t created.
That’s why we are not able to run last select command
The table is created in the new schema as they are in the same batch and SSMS is being “helpful”. Since you are not including the schema name in the select statement, it is giving you the error as the table does not exist in the default schema.
Wrong database selected
Table belongs to schema
So
Select * from schemaname.tablename;
where put puzzle answers?
SELECT * FROM MyScheMa.MyTable1
without “go” separator, it creates table under MyScheMA and then select statement divided by “go” should use that schema name…
In between Go is missing
Hi, This one is easy, it is because you need to specify the schema name like SELECT * FROM MyScheMA.MyTable1, otherwise SQL Server will try to use dbo schema instead…
When locating an object, SQL Server will first check the user’s default schema. If the object is not found, SQL Server will then check the dbo schema. it is important to include schema names when referring to an object, in this case – MyScheMA.
You need to also specify the schema in the select like so SELECT * FROM MyScheMA.MyTable1