This Table Creation puzzle asks for an explanation before you run its code. I suggest examining the batch separators carefully.
USE tempdb;
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID int);
GO
SELECT * FROM MyTable1;Creating the schema and table succeeds in the original scenario. The final SELECT reports error 208: Invalid object name MyTable1. Why does that happen? State the default-schema and pre-existing-object assumptions.
Use a disposable database without those names if you later test an answer. The code remains the challenge. Readers and comments can supply the explanation.
The original promotion offered three workshop entries with 30-day access and newsletter-subscription eligibility. Its deadline was October 25, 2019. That promotion has ended. The related puzzles provide further challenges.
Related reading
- Winners and Solution – DELETE Qualified Rows From Multiple Tables
- SQL SERVER – SQL Agent Job and Backslash – Strange Behavior
- SQL Puzzle – DELETE Qualified Rows From Multiple Tables
- Spotting Unusually Small Backups Against Each Database’s Average
- 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
A puzzle result is not independent of connection context, it is behavior to explain under explicit assumptions.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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.