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
Hi,
I knew time is over. But I saw it today. first thought of mind error due to schema. Then i execute it also, and find yes my first thought is correct.
correct statement will be for select query : SELECT * FROM MyScheMA.MyTable1
it is just issue of scope of “MyTable1”.
Thank you ?, I hope you can see me my answer as well. Because it’s showing me the message, “your comment is awaiting moderation” message on top of the comment.
Because MyTable1 is created under MyScheMA.
So correct select statement would be select * from MyScheMA.MyTable1
Hi there, I can’t see my answer in the comments.
Soon.
This was one such contest, where pretty much everyone got the correct answer and it was impossible to mention everyone’s name. There are over 300 correct answer. The judges has selected 3 different winners and each will get personalized email from the team.
Dear Sir
I have conclusion about this question sir.
1. As per SQL Server Standards we cannot possible to execute the Create & Select Statement at time.
2. By Defect SQL Server behavior is, If we creating Or selecting any table we need to mention below
following four parts in Statement
a. Server Name
b. Database Name
c. Schema (Table Owner)
d. Table Name
3. By Default SQL Server it will assigned Based on Login below Following Parts
a. Server Name
b. Database Name
c. Schema Name
Above is SQL Server Standards may I correct sir.
Now my question is.
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1
This above syntax we have not mentioned any Schema Name while creating table, why it was taken automatically previous created schema. This conclusion we need solve.
Because any select statement we need to mention four parts if any missing it will give error this commonly understood,
Why created new table recently created new Schema because we have not mention any schema while creating table this is my question.
Sir this purely my clarification purpose sir.
Whoo Hoo! Good luck to everyone.
Keeping my fnkrs crsddtes (hard to type with crossed fingers :)
Since we did not used “GO” after creation of schema, the table is created under MySchema scope level.
so we have to write MySchema.MyTable1 in “SELECT” query.
if we try in different way we can eliminate above error from your question
CREATE SCHEMA MyScheMA
go
CREATE TABLE MyTable1 (ID INT)
go
SELECT * FROM MyTable1
tempdb works differently. when sql server is restarted, tempdb gets created using the template which is the model. Once recreated, we end up losing our work done before restarting the tempdb.
Anshika, Yes. This is why putting TempDb on RAM drive. A drive that lives in memory only.
Helps SSD drives live longer. They seem to be faster but I do not have any numbers.