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
Because we are using TempDB, it is used for holding temporary objects, thats why error is coming while querying
The answer is simple, – if a CREATE TABLE follows CREATE SCHEMA withoout a GO, and CREATE TABLE is not specifying a schema name, then the schema is assumed from CREATE SCHEMA statement. In your case the full table name is [MyScheMA].[MyTable1]. Therefore, when you issue SELECT * FROM MyTable1 omitting the schema name, then your default schema name is assumed (by default it’s “dbo”). Thus – an error, because [dbo].MyTable1 does not exist. However, if you places a GO between CREATE SCHEMA and CREATE TABLE statements, then your script would have executed without errors, since you woulld have created [dbo].[MyTable1] table with [dbo] schema name being assumed as thee default schema name.
Hi
The table MyTable1 belongs to MyScheMA and therefore needs to be fully qualified in order to select from it. i.e. SELECT * FROM MyScheMA.MyTable1.
The reason it belongs to MyScheMA is that the CREATE TABLE was run in the same batch as CREATE SCHEMA statement.
If the CREATE SCHEMA statement had a batch terminator (“GO”), then the CREATE TABLE will be created under the default schema, presumably “DBO”.
Hope that is along the right lines. :-)
Because it will query with default schema dbo.mytable1 and not with myschema.mytable1
As table created under schema query should be
SELECT * FROM MyScheMA.MyTable1
The name of the temporary table starts with a hash symbol (#)
This happens because when you run SELECT * FROM MyTable1 it is looking for that table name in the default schema (which doesn’t exist). You created that table in the MyScheMA schema (you actually created the schema and the table in the same batch).
You create a schema then you create a table under that schema but you don’t specify the table in the schema and doesn’t exist in the default dbo schema
You need to have the schema name added to the Select statement, the SQL select statement needs to be:
SELECT * FROM MyScheMA.MyTable1
Basically you need to qualify the Schema name.
you need to use the schema
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) GO is not a Transact-SQL statement, instead it is a command recognized by the Sql Server Management Studio (i.e. SSMS), SQLCMD and OSQL utilities. These utilities send all statements after the previous GO statement and before the current GO statement as one Batch to the Sql Server engine for execution. So, it means everything in that batch is local to that batch
3) It means that Table create in current batch is not visible to Next batch
Select * from myscema.mytable1
(Above command is the proper way to select that table.)
It should be SELECT * FROM MyScheMA.MyTable1, else it is looking for default dbo.MyTable1.
You will need to use 2 part naming for accessing above table. If schema is not provided it will try to find in default “dbo” schema hence invalid object error
You are creating the schema and the table in the same transaction. So SQL Server assumes the table belongs to that schema. When you run SELECT after GO, you closed the transaction, and now SQL needs you to inform the schema.
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.
GO is not a Transact-SQL statement, instead it is a command recognized by the Sql Server Management Studio (i.e. SSMS), SQLCMD and OSQL utilities. These utilities send all statements after the previous GO statement and before the current GO statement as one Batch to the Sql Server engine for execution. So, it means everything in that batch is local to that batch.
So It means we have create table in the current batch will not be visible in the next batch
Because table is created under the MyScheMA. And when we use select * from MyTable1 then Sql server use the default Schema i.e. dbo. so it means Sql is searching the table under dbo schema whereas we have created it under MyScheme so that’s why we are getting this error.
When we created the schema and table, the table will be created within that schema.
Now, to select the data from table we have to use schema name with the table. (SELECT * FROM MyScheMA.MyTable1)
When creating tables in tempdb you must either create a temp table with (#) or variable table with (@) in the beginning of the name.
USE TempDB
GO
CREATE SCHEMA MyScheMA; –> Insert Semicolon here; if not the table is created in the newly created schema?
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1 –> After the previous GO the default schema is active again
At least that’s what I think