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
You will require to prefix schema name with table name in your select statement
SELECT * FROM MyScheMA.MyTable1
Hello,
First of all thank you so much for posting this puzzle. It’s always great to have challenges..
The short answer for this is: MyTable1 was created for the schema MyScheMA and schema was not specified for this would have worked if we ran [ SELECT * FROM MyScheMA.MyTable1 ]
The thing is that once a schema has been created and after that the creation of objects (proc, functions, tables) are added to that schema. We know that by default SQL Server adds everything to the dbo schema but once a new schema (in this case MyScheMA) is created and after that some object creations delimited by a batch between GO statements SQL Server will add them to it.
Of course It’s always a good practice to create a schema in batch and then specify the different schema object creation in another batch. In this example, an schema MyScheMA is created and then object object creation happens letting know SQL Server that we need to work on that schema and is broken by the GO statement.
I hope this is the answer you are looking.
Thank you
Since the create schema and create table are part of the same batch, the new table is created in the MyScheMA schema. The select statement does not specify the schema name so it will look in default “dbo” schema and therefore return an error message.
at least a two part naming convention is needed. the schema name is also needed, so use “MySchema.MyTable1” after the FROM clause
create schema/table in tempdb puzzle:
The ‘invalid object’ error on the select statement – SELECT * FROM MyTable1, is due to the referenced table not
being fully qualified. The default schema in tempdb is dbo, ergo in this example, the table does not exist in that schema. (add the schema name to the query: select * from MyScheMA.MyTable1)
You need to include the schema name with the table name
SELECT * FROM MyScheMA.MyTable1
As we are using a tempdb over here and after using the creation of schema and table we use GO command which limits scope of the table object to that specific batch. That is why it is getting an error while selecting the table out of its scope.
Table is been created under ‘MySchema’ and when we crossed the last ‘GO’ statement to execute ‘Select’ statement, which will trying to fetch data from default schema ‘dbo’, so table created under ‘MySchema’ and we have not given any schema name in ‘Select’ statement so it is giving error of Not found table.
The table won’t create with default schema dbo it’s created with new schema MyScheMA that’s why couldn’t find out the table. We should call it as select * from MyScheMA.Mytable1 then it will return result.
We are getting error because in this script we created the Schema- “MyScheMA” and the Table- “MyTable1” in one batch, and when we are selecting from it we did not mention the fully qualified name in our select statement.
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1 —We did not mention MyScheMA here. SQL Engine is looking for “MyTable1” under “dbo” schema (default schema) under which tempdb tables are created. Hence we are getting this error.
SELECT * FROM [MyScheMA].[MyTable1]; –No error
OR
USE TempDB
GO
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1 — No Error as this table is created under “dbo” schema.
When we create schama and then if we have a table create table without a GO in between. new table will be created inside the schema we have created in the previous line.
Since its in a different schema we need to select schema.table
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyScheMA.MyTable (ID INT)
GO
SELECT * FROM MyScheMA.MyTable
GO
I am guessing because by default it is using schema dbo. Change the select statement to the following
SELECT * FROM MyScheMA.MyTable1
MyTable1 was created in the same batch as MySchema, so it exists in that schema. The table reference in the select did not specify a schema, so it tried to find a dbo.MyTable1.
CREATE TABLE MySchema.MyTable1 (ID INT) — this should be the right create table command
SELECT * FROM MySchema.MyTable1 — this should be the right select command
table name followed by schema, example: select * from MyScheMA..MyTable1
When we created the schema and the table, the table gets created within the schema created and in select statement the schema is not defined thus default schema dbo is used and table doesn’t exists in dbo schema hence the error
Since we have created the schema and then created the table “MyTable1”, the schema for the new table is set automatically to “MyScheMA”. So when we select the data from the table, if we give “select * from myTable1”, then the SQL server will automatically go to default schema which is “dbo”, and the default query will be “select * from dbo.myTable1”. But for “MyTable1″, the schema is”MyScheMA”. So in order to get the values, the query will be “select * from MyScheMA.myTable1”.
Problem is with Go Statement immediately after SELECT * FROM MyTable1 . Here GO is called MyTable1 is getting cleaned up.
Table is created in Schema MyScheMA. Hence if we give schema name and table name then it will run correctly