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
Here it is looking for schema, so we need to give schema name before that table
Hi,
You must qualify MyTable1 with the schema name : MyScheMA.MyTable1. The select will work.
Best Regards,
Pedro Entrezede
You used an explicitly named schema, versus using default dbo. The query has to use a fully qualified reference in the namespace (the schema). select * from schema_name.object_name.
Thanks for leaving comments.
Since we have created a user defined schema ‘MyScheMA’
we have to use the schema name as well in the select schema
ex: SELECT* FROM MYScheMA.MyTable1
During creation, the schema is the default, however, after the creation, without specifying the schema, it will throw error.
The CREATE SCHEMA and CREATE TABLE statements are processed in the same batch, followed by the GO statement. As a result, the new table is created within the new schema. The SELECT statement is processed within a separate batch, within the schema that was current before CREATE SCHEMA was executed. The new table doesn’t exist in the old schema and an error is thrown.
Because GO statement is used as a Batch separator in Sql Server. In given script if we execute all sql statements at one time then SQL treated it as a single batch and table “mytable1” get created in schema “myschema”. If we execute each statement separately then we’re able to run the select after GO statement everything script as a batch.
create schema myschema
create table mytable1 (id int);
go
The table created went under the last schema created in the current session, MyScheMA. When querying a table without specifying the schema, sql looks for the table in your default schema which is normally dbo. Your default schema may be changed however so that the statement would work.
The error is caused because without specifying the schema name then the default schema of dbo is assumed by sql server
There is go missing between 2 create statements. And only schem statememt executed…
When you run this select, SELECT * FROM MyTable1, it assumes you are using the dbo schema. If you add the schema name to the table then it will work. SELECT * FROM myschema.mytable1 produces results.
Maybe this is how TempDB´s supposed to work?
You should not openly manipulate a table inside of TempDB and also you shouldn´t create there (not a best practice, I believe)
I dont see my comment/answer – are they turned off?
They will be all visible soon.
Gotcha thanks Pinal
The table and schema creation are happening as a single batch ie. Both statement are in betwen go statement so the table is created under MyScheMA. But when we selecting table outside the go statement the scope will be lost. We need to specify schema name
As the create schema will create the schema and the following create table statement will create table in that scheama so after go the select * from table without qualifying schemaname will flash error of invalid object. The same statement can be executed successfully by adding schemaname to the tablename.
The table is created under MySchema. By default the tables are created under dbo. If you want to find the table you need can find it under MySchema.
SELECT * FROM MyScheMA.MyTable1
Thanks Pinal, I thought my comments weren’t submitted.
I would expect this code to run successfully without that error, but since you say that it does generate an error, I will hazard a guess before I try running the code.
When you create a schema and a table in the same batch, the table is assumed to be a member of the schema. Thus, when you attempt to query MyTable1 rather than MySchMA.MyTable1, the table is not found and you receive an error.
If this is indeed true, I was not aware of it and I learned something today. If not, I was wrong and there is something else going on. I’ll let you know shortly when I run the code!
The MyTable1 was not created under the context of MyScheMA?
That was it!
SELECT * FROM MyScheMA.MyTable1
Here is my reason : Problem is GO after the create statement,
Remove go and run will work as
Temp tables are scope specific
Because the correct way to do the select is:
SELECT * FROM MyScheMA.MyTable1
The table now belongs to the “MyScheMA” schema. And must be called using their full name (schema name included).
First of all we are creating the table and schema in Temp DB, the Go statement is terminating the session.
And after the Go statement it is considering as a new session and there is no table and schema in the new session.
hence the error is.
fyi..i didn’t execute the code.
TempDB for temporary processing. Create scheme and table with grant on a non tempdb.