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
Hi,
The table created in the tempdb database is only accessible for the same session and the “Go” command is closing the session.
Because, Table created in MyScheMA and not in default dbo schema. You need to specify MyScheMA.MyTable1.
The MyTable1 table was created under the tempdb duo schematic, not the new [MySchema].
Not having the GO statement after create schema would create the table with name such as MyScheMA.MyTable1. So actually there doesn’t exists any table with name just MyTable1,so getting an error.
As the schema is user defined we should call the table with two part name i.e. select * from [MyscheMA].[MyTable1]
schema ‘MyScheMA’ and table ‘MyTable1’ have been created at one ‘GO’ so table is created in ‘MyScheMA’ instead of default schema ‘dbo’.
&
“SELECT * FROM MyTable1” will try to find table in default ‘dbo’ schema so it will give error “Invalid object name ‘MyTable1’.”
MyTable1 will be created in MyScheMA schema and would require reference to the schema name in the select query
The table gets created under the schema MyScheMA but the select query is running on table MyTable1 which doesn’t exists in default dbo schema.
Because schema and table creation are in one batch, the table schema is MyScheMA.
Now, in the next batch, querying the MyTable1 , with out schema, is translated into dbo.MyTable1, which is not created/not existing
because when we created new schema after created that schema table created with same schema and we are selecting table without new schema that’s why have error. And I think, if we put ‘GO’ statement after ‘CREATE SCHEMA MyScheMA’ then it will not give any error.
With this example MyTable1 is created for Schema MyScheMA, so “SELECT * FROM MyTable1” is giving error.
Because by default it consider dbo schema for a table. You will need to use “MyScheMA.MyTable1” in select statement.
So following query will work fine:
Select * From MyScheMA.MyTable1
Regards
Naveen Gupta
Hi,
this is why this query create the table not in default schema (dbo) but in MyScheMA. So, when we run
SELECT * FROM MyTable1
SQL Server search MyTable1 in dbo schema and doean’s find it.
If you what to select this table the correct sentence is
“SELECT * FROM MyScheMA.MyTable1”
or remove:
CREATE SCHEMA MyScheMA
and use:
SELECT * FROM MyTable1
Becouse table created under schema like schmaname.tablename i.e SELECT * FROM MyScheMA.MyTable1
Schema MyScheMA was not visible to the next bach after GO.
Either removing GO or putting schema before table name would work fine and show the table.
SELECT * FROM MyTable1, Here we are trying to get the data from dbo.mytable. There is no table with default schema
The schema-name is missing in the select
When running SELECT * FROM TableName, the TableName has to be an explicit table name in sql server we should use select * from [Database].[Owner].[TableName]
Hi Pinal,
Because of one transaction by default the table created with MyScheMA
Select * From MyTable1 table hasn’t schema that’s why getting an error
Select * From MyScheMA.MyTable1 –is does execute
We actually created table MyTable1 with schema MyScheMA. The code ‘SELECT * FROM MyTable1’ tries to show data from table [dbo].[MyTable1]. Instead we should use ‘SELECT * FROM [MyScheMA].[MyTable1]’
To access the table Schema name must be define eg. Select * FROM Myschema.Mytable1
due to default schema is dbo , SQL server looks for the Mytable1 in dbo schema and not found there hence throwing an error.