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 table “MyTable1” has been created into the scheme “MyScheMA”. You should be querying MyscheMA.Mytable1
The error is due to not including the Schema name in the SELECT statement, if you used “Select * from MySchema.Mytable1” you would not get the error
TempDB has default schema “dbo”. This script first create schema “MyScheMA” and under this schema it will create table “MyTable1” in the TempDB. when we create custom schema and give reference to any table, then we need to execute select statement with SCHEMABINDING like SELECT * FROM MyScheMA.MyTable1
Maybe reason is a bug or maybe is syntax functionality :)
If CREATE SCHEMA is used in same line execution with CREATE TABLE then name of schema get value “MyScheMA.MyTable1” so only way to select would be “SELECT * FROM MyScheMA.MyTable1”. BUT the properties of table are showing something different…
In my opinion code (or sytnax) i wrong.
The proper code would be:
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyScheMA.MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
BR
Dear Sir.
While executing the statement if having more than one statement without “GO” Condition (Batch Command) considered Whole code is in one statement.
While executing the statement
First is Schema
Second is table name
The table has created newly created schema.
After you maintained “GO” Statement (Separated Statement)
By default SA owner is “dbo”
The table was not created against “dbo” schema.
Table was created against “Myschema”
Because of this error is occurred.
Regards
Dinesh
Hi, error appears because there is a different default schema and in your query you do not specify schema you want to use.
It is enough to add schema name to your select query.
It should be SELECT * FROM MySchema.MyTable1
The table is cachednot yet cached by Intellisense to provide typeahead support and pre-execution error detection
Respected Sir, It is because we have created schema and table in same batch, hence default table schema will be MyScheMA, correct query may look like below. Thanks and waiting for your amazing answer.
SELECT * FROM MyScheMA.MyTable1
MyTable1 table is created under MyScheMA. so we need to specify schema name in the query.
The table MyTable1 was created under the MyScheMA schema since it was part of the batch that created the schema. In order to reference it, you need to use the qualified name MyScheMA.MyTable1.
Hi Pinal,
In the given query we are getting – “Invalid object name ‘MyTable1’.” error because – “CREATE SCHEMA MyScheMA” is a DDL statement and after this statement there should be a batch separator “GO”. So when i specified batch separator in the query like this –
USE TempDB
GO
CREATE SCHEMA MyScheMA
GO
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1
then above query runs successfully and not throwing any error.
The table is created under “MyScheMA” schema the select query works as default schema named dbo. If you are rewrite the query as SELECT * FROM MyScheMA.MyTable1 you will not get any errors.
While executing the below code as at the same time the table is created under Myschema schema.
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
if you run this separately the table is created under default schema and you will not get any errors.
Table created with MyScheMA schema name because you have not use GO between two create statement, you can select like below statement
SELECT * FROM [MyScheMA].[MyTable1]
below statement work without any error.
USE TempDB
GO
CREATE SCHEMA MyScheMA
GO
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1
MyTable1 table was created in MyScheMA Schema.
For selecting data from MyTable1 we have to use Schema name.
eg: select * from MyScheMA.MyTable1
the newly created schema is not the default schema of the current user. that’s why.
Hi Pinal,
Since we have created a schema, you will need to specify the schema name before addressing the table name then SQL would be able to run your query without errors.
SELECT * FROM MyscheMA.MyTable1
Thanks,
Shraddha
When we create schema. It get selected by default. and when we create table it comes under schema. and when you query by default sql server engine look under database name. and your table is inside schema so we must add schema.table to get results.
To get results you have to specify schema and table, I mean SELECT * FROM MyScheMA.MyTable1
Error occurs because the select statement haven’t the schema name.
Logic: When we create a schema and table then the table should be use along with its schema.
i.e. select * from MyScheMA,MyTable1