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
The create schema command prefixes the created tables and views with the schema name.
Hence the table is MyscheMA.MyTable1 instead of dbo.MyTable1 or another schema.
This feature was added after SQL 2005 so that users and schema are logically different and separate.
It has nothing to do with being created in tempdb.
(Believes to be correct, crosses fingers, hopes to win)
SCHEMA & TABLE creation was part of same transaction and table was created under the created schema. When SELECT is being run from this table , by default it is looking under dbo schema and gives error.
You have to use SELECT * FROM MyScheMA.MyTable1.
When you create table; it gets created with that schema.
Default schema is dbo
I think it’s because, when we create a table with Create schema statement first it will create a table with that specific schema that we have crated in the earlier statement. When we run the query as Select * from Mytabel1 the it will execute with default schema “dbo”. But we don’t have object “dbo.Mytabel1” so it throws us error.
Tempdb is virtual storage and do not allowed to embeded temp schema to table link. So object is not found in schema table1.
because you didn’t specify the schema name when you select data from that table
The select needs to have the correct two part name for the object
MySchema.MyTable1
The select statement does not have the schema name specified for the table. So, by default it is gonna look for the table within the dbo schema. Since there was no “Go” specified after the create schema, it created the table under the “MyScheMA”. That’s why the error.
the Table would get created under the MyscheMA in this particular case and the select will not work unless the schema name is also specified
— Puzzle – Schema and Table Creation
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT); is one sql stmt, hence it creates schema MyScheMA and then table under that schema.
SELECT * FROM MyTable1 will look for table under default schema dbo. To make query work we need to mention MyScheMA.MyTable1
It’s because that you didn’t mention schema name in the select query. If we don’t mention the schema name it will look in dbo schema by default.
And you created schema and table in same batch so the table related to same schema.
Use the below query to avoid the error:
Select * from MyScheMa.MyTable1.
My quess is that you would need to preface the table call with MyScheMA because its defaulting back to dbo schema. SELECT * FROM MyScheMA.MyTable1 should return a valid result without error.
If the schema name is not specified ,then By default “dbo” schema is attached to the table and SQL server searches for that table in the dbo schema but the table is created in My schema. Hence we get an error.
It is because GO will end the batch, and we are not in the same database anymore, hence table is not found.
You have to mention schema name.table name. otherwise it will take it as a default schema dbo and we will get this error
You need to mention schema name.table name or else system by default will take dbo.table name which doesn’t exist.
select * from MyScheMA.MyTable1
not a valid use for that schema