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
missing a GO after the schema create
If any schema already exists on a server and wwe create another one, how will the server know from which schema we want to select data?
because the table is created for the “MyScheMA” schema. And when we use “dbo” as the default schema, then we must specify the non-default schema name for the table name.
Hi, We need to use name as [Schema].[Table]. We’re avoiding adding [DatabaseName] by adding USE TempDB Go, but we do not have synonym for MySchema.MyTable1 as MyTable1.
Temp table is created at the scope of exec method hence its not available after the function returns.we can insert into instead of select.
The schema MyScheMA not included on the select causing the error. Should be select * from MyScheMA.MyTable1
The issue is with this puzzle is that the create table statement is part of the create schema statement, which in turns adds it to that schema by default. The end of the batch signified by GO is necessary since CREATE schema is the only statement allowed in that batch (irrelevant i guess). The select statement errors due to the default schema which certainly isn’t MyScheMa. So it makes sense that [default schema].MyTable1 fails because in this context it doesnt exist.
We need to use the schema name before the table as its not the default schema and it will work.
MyTable1 was created inside the CREATE SCHEMA statement so it belongs to the schema MyScheMA.
The SELECT statement fails because the object name is incorrect, the correct statement is
SELECT * FROM MyScheMA.MyTable1
The intellisense cache does not automatically update, you must invoke a refresh to resolve.
MyTable1 is created with MyScheMA but not with dbo. Reason for this is beacause Schema and table creation are part of same batch. so when you try to select from table, it tried to look for dbo(default) and table is under MyScheMA.
you can write query in either way mentioned below.
USE TempDB
GO
CREATE SCHEMA MyScheMA
GO — Added.
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1 — Picking from dbo now
— OR
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1 — picking from same schema
The Intellisense cache does not automatically update;refresh it to resolve.
Between create schema and create table, there is no ‘GO’, meaning MyTable1 is created in schema MyScheMA. The SELECT is trying to select on the (default if not specified) DBO schema.
Kind regards,
Kris
Invalid object name ‘MyTable1’, because ‘MyTable1’ is under the ‘MyScheMA’
To remove the error, in the FROM clause, table name has to be along with the schema name like ‘MyScheMA.MyTable1’
The schema and table are created in the same transaction. You need to preface the table name with the schema for the query to work.
MyTable1 is created in MyScheMA as they were run together between the GO statements; but the schema was not declared when selecting from the table and MyTable1 is not in the default(dbo) schema.
The SELECT statement does not specify the schema (myschema)
Invalid object name ‘MyTable1’ error, because ‘MyTable1’ is under the ‘MyScheMA’
To remove the error, in the FROM clause, table name has to be along with the schema name like ‘MyScheMA.MyTable1’
CREATE SCHEMA is a transaction within the created schema until the transaction commits. After the command GO the previous transaction gets commited and the default schema is in effect. Without specifying the schema on the SELECT command, you won’t find anything. One needs to specify the schema to be able to get results.
MyScheMA and MyTable1 are created in the same statement.
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1