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
TempTable objects disappear once GO terminates the thread.
The table is auto-created on the MySchema schema, but the select is trying to retrieve from the dbo default schema
Need to reference table with proper schema ex: select * from MyScheMA.MyTable1
table is created in the Schema MyScheMA and that is not the default Schema, so the Schema needs to be added in front of the table name.
By default SQL server is looking for dbo schema which is not the case so it should be queried as SELECT * FROM MyScheMA.MyTable1
When create a schema and then create a table in the same batch ( no go in between), the table is create in that schema. So, in this case you need “SELECT * FROM MyScheMA.MyTable12”.
Table was created in the same transcation that created the MyScheMA shema, adding the new table to the schema. You need to fully qualify the from cluase for this to work query
SELECT * FROM MyScheMA.MyTable1
Hi Sir,
“Go” Statement is missing after #3 “Create Schema”. So table is being created under schema “MyScheMA”.
Since the table is created in the schema, for the select to work we neet to select from MyScheMA.MyTable1
Easy-peasy – gotta put the new schema name in front of the table name (Select * from MyScheMA.MyTable1)
The table is created using the new schema instead of dbo which is being used in the last statement because the schema is not specified. The last statement should be SELECT * FROM MyScheMA.MyTable1
The Create Schema command was in play when create table was run so it is not dbo.mytable1 it is MySchema.Mytable1 fully qualified. create schema temporarily leaves it as the default schema until the next ; go
You created the schema and table, but without specifying the schema when you try to select from it, so it used the user’s default schema, which was probably dbo.
Try: Select * from MyScheMA.Table1
You’ve created the Schema and the Table in one Go, so the Table is added to that Schema.. Since it is a new Schema, it can not be your default Schema which is most likely dbo,,, So you would have to use “SELECT * FROM MyScheMA.MyTable1” to get the results without errors…
because you need to add the schema name. select * from MyScheMA.MyTable1
SELECT * FROM MyTable1 should specify schema before table name: SELECT * FROM MyScheMA.MyTable1. That is because SELECT is in another batch. GO between CREATE and SELECT isolate its context in separate batches… so removing second GO should work or just adding schema in last select.
You need to provide schema name before table name:
SELECT * FROM [MyScheMA].[MyTable1]
you didn’t specify the schema when you did your select
Very Interesting question, here is the Answer :
CREATE SCHEMA MyScheMA CREATE TABLE MyTable1 (ID INT) <– is one statement,
and select should be :
SELECT * FROM MyScheMA.MyTable1
When the CREATE SCHEMA/CREATE TABLE is ran in the same batch, the create table will take the schema that was just created. The SELECT * fails because SQL Server is looking for the table within the default dbo schema, which doesn’t exist.
If the CREATE SCHEMA and CREATE TABLE was ran in its own batch (or separated by GO) then all the SQL will complete successfully, because the table created will take the dbo schema rather than the schema MyScheMA.