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
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
It’s because schema selection defaults to dbo. So SELECT * FROM MyTable1 == SELECT * FROM dbo.MyTable1. Do SELECT * FROM MyScheMA.MyTable1 instead.
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
since table was not yet created under user defined schema
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!
CREATE SCHEMA and CREATE TABLE are in the same batch.
Anything after a CREATE SCHEMA will be created under that SCEHMA.
SELECT * FROM MyScheMA.MyTable1
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.
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
SELECT * FROM MyScheMA.MyTable1
You get an error because you did not include the schema defined already ‘MyScheMA’. It should be SELECT * FROM MyScheMA.MyTable1;
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.
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
Because its selecting the table in dbo schema
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.
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
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
What’s the answer, I have been checking this blog from past few, I still don’t see answer
There are over 300 entries so working through them.
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.
because in Select command you not mention the schema name