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 objects were created in TempDB.
Since the User defined Schema is created, we cannot “Select * from MyTable1”. We need to tell the Schema also.
So the correct statement will be “SELECT * FROM MyScheMA.MyTable1”.
The MyTable1 gets created within the MyScheMA.
To select data from the MyTable1, below code should be run:
SELECT * FROM MyScheMA.MyTable1
The reason why we get the error is due to the table bound to the newly created schema . by default when we run select command against any table without schema prefixed before the table name , then SQL assumes it to be a dbo schema which is a default schema for the SQL objects . In this scenario we created the schema and table in a single batch hence table gets created with the newly created schema name like Myschema.Mytable1 . After this command we have a batch seperator ( “GO” command) which makes the following query independent hence when we run the select command against a table ( Mytable1) we get the error as its not associated with schema name( Myschema).
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
Reason to get an error is the schema is not not defined in select statement. By default select statement refers “[dbo]” schema.
Also the first two create statement shows that it creates first schema named “MyScheMA” and with table definition “MyTable1”.
So it creates table with “MyScheMA.MyTable1”.
By default it looks for default schema and as in above script table is part of user defined schema it requires to specify with table name.
In this query table “MyTable1” is created under new schema “MyScheMA”. However when executing the data from table, this schema is not mentioned. Sql server used default schema “dbo” to run the queries but in this case dbo schema does not have this table created and hence we do get an error when trying to select the data from the table.
While executing the statement if having more than one statement without “GO” Condition (Batch Command) considered while 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.
Hi Pinal,
This table is created under MyScheMA schema. In the select query we not using the schema name. So by default it will use dbo schema. Since the table is not associated with dbo schema it is throwing the error
TableA created is owned by schemaA. The current user has no permission to select from schemaA. Permission has to be granted first for the select to run.
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
After creation of schema GO statement not mentioned,so it can consider as single batch including create statement .
for selection of the table: SELECT * FROM MyScheMA.MyTable1. this case no need to mention the schema with table.
We created the table under spesific schema! So to select the data we need to enter:
SELECT * FROM MySchema.MyTable1
In the temp db, until and unless you are not specifying the fully qualified name of the table which should be
SchemaName.TableName, the query will throw the error
in the question
if i change the query as
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
So notice i just added the schema name in select and the query runs with no errors
Bottom line, the answer is you need to add schema name before the table name in the select query to make it work
Table dbo.MyTable1 (= MyTable1) does not exist.
MyTable1 is in schema MyScheMA.
SELECT * FROM MyScheMA.MyTable1
A schema is a collection of database objects including tables, views, triggers, stored procedures, indexes, etc. Any follow up statement for creation of these objects would get allocated to the newly created schema. An object within a schema is qualified using the schema name followed by the name of the object. In case it is not specified it will throw an error as Invalid object name ‘MyTable1’.
Hi,
I think we got this error because the table MyTable1 is mapping with the schema “MyScheMA”. The correct select query should be: SELECT * FROM MyScheMA.MyTable1
“why do we get an error when we select data from the table?” – because the table is created in schema MyScheMA.
So either we must use the schema when selecting from the table with “SELECT * FROM MyScheMA.MyTable1”, or put “GO” between creating the schema and table – “CREATE SCHEMA MyScheMA
GO
CREATE TABLE MyTable1 (ID INT)”
when you create the schema first
it forms part of the identity for the respective table
since you are selecting without specifying the schema the table is no longer accessible or visible for read
you need to fully qualify your object write select * from [MYscheMA].[MyTable1]
if you don’t create schema first and just create you table you will be able to select it. hoping i win this time.
Because after running the query, Intellisense doesn’t refresh it’s local cache. For fix this issue: when your Cursor in the query editor click Edit > Intellisense > Refresh Local Cache