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
Every user/DB has it own default schema, While querying from a DB it adds that default schema if no schema is defined in the query, So the table created in MyScheMA but queried without schema. So it tries to bring the data from a table in default schema which is ‘dbo’ and the table does not exist in ‘dbo’.
Because the table was created under the MyScheMA schema. You would need to do SELECT * FROM MyScheMA.MyTable1.
Schema name “myschecma” should be specified while selecting the table,else by default query tries to read in dbo schema.
Hi Pinal
we need to add AUTHORIZATION in CREATE SCHEMA MyScheMA and then execute.
Regards
Mandar Patil
Since the query execution took place in the same batch (without “GO” statement between “Create Schema” and “Create Table” statements), the Schema context remained “MyScheMA” as oppose to default “dbo”.
this is true for any database, and not restricted to the “Tempdb” only.
Since the table was not created in the default schema, you must reference the schema in the select statement.
SELECT * FROM [MyScheMA].MyTable1
The table was created under the new schema MyScheMA not under default schema dbo.
I will work if you modify the query as:
SELECT * FROM MyScheMA.MyTable1
When you try to run the following in SQL Server:
SELECT * FROM MyTable1
SQL Server is looking for MyTable1 in the default schema (viz., dbo).
To select data from the table (and not get that error), you need to include the schema with the table name:
SELECT *
FROM MyScheMA.MyTable1
Nice puzzle I couldn’t solve it without running the script , after running it and found the error I figured out that my MyTable1 really exists but inside MyScheMA so to make the select work without error we need to qualify the name like that
Select * from MyScheMA.MyTable1 , then it will not get any errors.
Hi Pinal, I’ll try to explain :).
Since table MyTable1 is created after schema MyScheMA is created ( under schema syntax creation definition) in the same batch
that means that CREATE TABLE statement creates a table MyTable1 in that schema – MyScheMA.
And after go statement when we select: SELECT * FROM MyTable1 , this slect expect that table is in default schema, but this table not created under default schema, so it couldn’t be found in default schema. (correct select will be SELECT * FROM MyScheMA.MyTable1 )
Goca
Because the “CREATE TABLE …” is just a clause within the CREATE SCHEMA command (the lack of semi-colons makes it so), the new table is created with the MyScheMA schema.
The failure occurs because the newly created MyScheMA is not anybody’s default schema, so the query only executes within the context of the dbo schema, never looking for MyScheMA.MyTable1.
Also, the “USE TempDB” is a nice red herring. You should get the same result executing that script in any database, not just in TempDB.
Is it because we restarted the SQL Server?
Hi Pinal,
This table is creating under schema and we are fetching the data without using that schema.
That’s why it prompt an error message “Invalid object Name”.
It is because you have to refresh the cache in Ssms (intellisrnse).
It is because the table is created withing the schema. To create the table outside of schema, we should have had “go” between create schema and create table statement to make them two separate statements.
Right now, that is single statement which creates the schema MySchema with table Mytable1. So to access Mytable1, you must specify the schemaname with it.
We created schema as MyScheMA and then created table in newly created schema. Below we are trying to select from new table without schema….that’s why we are getting error. If we write a query like this SELECT * FROM MyScheMA.MyTable1 then we don’t get any error
it should be like this
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
This would work: SELECT * FROM MyScheMA.MyTable1. The table is created on the new schema by default, based upon how you wrote the code. Leaving out the schema in the original select statement defaults to the logged in users schema, typically “dbo”.
The reason for error is table is created in schema ‘MyScheMA’ and we are querying table in ‘dbo’ schema.
The table schema is “MyScheMA”. The GO statement after create table terminated the previous statements. The last select statement uses the default schema “dbo” and returns error.
its because we haven’t specified Go statement after creating schema and according to Microsoft document “Objects created by the CREATE SCHEMA statement are created inside the schema that is being created.
“. So since its created under schema MyScheMA we have to use select * from MyScheMA.MyTable1