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
Creating the Schema causes the table to be created within the context of the schema. But, the SELECT statement runs in the default context of dbo, therefore the table [MyTable1] was not found within the dbo context. You would have to SELECT FROM MyScheMA.MyTable1 to get the contents of the table.
Because there is no separator or ‘go’ statement after the create schema statement, mytable1 is created in that schema. The subsequent “SELECT” statement does not qualify the schema name on MyTable1 so the default schema for the user is searched for the table, not MySchema.
When creating the scheme the instruction to create the table is in the same block of instructions so the table is created in the “MyScheMA” scheme; and it fails because the select statement does not specify the “MyScheMA” scheme.
Hello,
We get the error because the table is not under the standard schema dbo, but under the schema MyScheMA.
The query should read correctly: SELECT * FROM MyScheMA.MyTable1;.
I hope that it is correct.
Many greetings
Uwe
when we Created SCHEMA MyScheMA and created Table MyTable1 in the session it is created Automatically on the SCHEMA MyScheMA at this time wen we need to do select from the table we should write the SCHEMA before the table name here is the correct T-SQL :
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
1.GO statement is used as a Batch separator in Sql Server. Batch is nothing but one or more Sql Server statements sent to the Sql Server engine as one set of statements
2.In the above Script Schema and Table created in one batch.Hence the table created with Schema.TableName
3.But data selection Query you are running without mentioning schema name (SELECT * FROM MyTable1)
4.If you execute the Query like below it will fetch the value
SELECT * FROM [MyScheMA].MyTable1
The select statement is looking for the table in the (default) schema dbo and not the MyScheMA schema.
It’s not able to run select query because schema name is not defined in select query, it will start working if you execute select * from [schema created].MyTable1
you created the table within the context of MyScheMA schema and did not specify this non-default schema in the select
creates schema MyScheMA and then creates a table MyScheMA.MyTable1 in that schema and for that we have use schema name along with the table like SELECT * FROM MyScheMA.MyTable1. but in your case it looks for default schema witch is dbo and that table associated with MyScheMA schema that’s why it is throwing an error
its giving error because we created table under Schema (MyScheMA)
syntax would have been SELECT * FROM MyScheMA.MyTable1
Create Schema and Create Table statement are considered as one command due to just one GO statement. Without the GO statement after Create Schema, the new table created is using the newly created schema instead of the default schema. In order for it to be separated and not tied to each other, a GO statement must be in placed after the Create Schema… So since the new table is using the newly created schema, then the SELECT statement must include the proper . format by including the MySchema schema. Should be SELECT * FROM MySchema.MyTable1
Remove GO statement above select query
After creates schema MyScheMA and then creates a table MyTable1 in that schema.that mean this table was created with MySchema and you have to use schema name along with table name for example :-
SELECT * FROM MyScheMA.MyTable1 but when you run SELECT * FROM MyTable1 it looks for table under default schema which is dbo and that table doesn’t exist with that schema that’s why it is throwing an error
Table will be gone after GO because we are still am TempDB.
SELECT * FROM MyTable1 defaults to the dbo default schema you needed to specify the schema name in the query
Because if we create a Schema with comand “CREATE SCHEMA MyScheMA” then we must call “SELECT * FROM MyScheMA.MyTable1”
Puzzle – Schema and Table Creation
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1
For this puzzle table is created with schema but while selecting from table schema.tablename (SELECT * FROM MyScheMA.MyTable1) not used.
After creating a new schema, it automatically gets set to use that schema and thus created the [MyTable1] in [MyScheMA]. The GO ended the batch and reset the default schema to [dbo] and the table doesn’t exist in the [dbo] schema.
Without putting a statement separator between the table and the schema the table is being created under the Myschema schema context. Since you just created the myschema schema it can’t be your default schema thus we need to explicitly state the schema when querying the table. I’m now going back to look at more of these they are fun.