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
I believe the “GO” command plays a role here. Since there is no “GO” command between create schema and create table, they will be sent sent to SQL Server as a single batch of statement for execution, hence, the table will be created under MyScheMA, that’s why when we select the table with default schema “dbo”, it says it doesn’t exists. The table should be actually selected by :
Select * FROM MyScheMA.MyTable1
Because by default table was created with dbo schema. In order to create table with desired schema, we need to specify it like create table [schema]. [tablename]
You have to include the full schema name in the select statement
SELECT * FROM [MyScheMA].[MyTable1]
line 6: SELECT * FROM MyScheMA.MyTable1
Nice Question:
Here, when we are creating schema without GO, SQL Server will create table under new schema only. That is another a way to create schema which contains table.
Problem starts when we are referring new table without referring to any schema.
As we have created table MyTable1 under schema MyScheMA, we should refer that table as MyScheMA.MyTable1. because default schema of SQL server is DBO (unless changed).
Right query should be:
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
GO
The CREATE SCHEMA/CREATE TABLE is one statement, not two. Therefore, the table is created in the MyScheMA schema. See here for reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver15
Because it is created without a ‘GO’ between, the table is created as a MyScheMa table, so to access it you would need to reference it as MyScheMa.MyTable.
The create schema and create table commands are executed in the same batch, hence the table is created within the new schema, MyScheMA. The table can be queried with this: SELECT * FROM MyScheMA.MyTable1
The table was created under the MyScheMA schema. select * from MyScheMA.Mytable1 will return the result.
You need to address the table with the schema name.
Mytable1 is created under myschema. so the select query should be select * from myschema.mytable1. That is the reason the error is coming.If you put a Go in between two create , there will be no error.
The New Table is created under the new schema. This it seems becoms the Default once created in this script.
The Select will work if the Schema is added:
SELECT * FROM MyScheMA.MyTable1
The SELECT fails because the SCHEMA was not named in the FROM statement. Not using the schemaonly works if the entity is in the default (dbo) schema.
you didn’t specify the schema before the table name, the query use default schema dbo. so it can’t find any table named Mytable1.
Additional to my previous answer, The CREATE SCHEMA and CREATE Table statements are both executed in the same batch. So the SCHEMA becomes the default for that batch.
If we add a GO after line 3 we get the expected behavior , thus creating dbo.MyTable
if you don’t specify the schema, it defaults to dbo. So you need to run it as below:
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
There is no “GO” in between. Hence, the table has been created in the schema created. A “SELECT * FROM MyScheMA.MyTable1” would have given the correct result.
There is no GO statement between the create schema statement and the create table statement so the table is created using the MyScheMA schema. If the select statement would have used the new schema it would have worked. as in SELECT * FROM MyScheMA.MyTable1
Answer: I assume it created the table under the MyScheMA schema, because it was in the same batch as the CREATE SCHEMA statement. When the GO statement was executed, it ended that batch and defaulted back to the dbo schema and it couldn’t find the table under the dbo schema and error-ed out.
Solutions:
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
USE TempDB
GO
CREATE SCHEMA MyScheMA
GO
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1
When creating the table , it will be created under the schema i.e., Myschema
But when selecting if you don’t mention the schema , it will select from dbo. and that’s why it fails.