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, after the schema is created the table is created using that schema, as if a pointer was directed to it, so that it is MyScheMA.MyTable1. The select-statement uses the default schema (and not the newly created schema) when selecting from the table, which is why the error message appears. Best regards, thanks for the great work.
Hi Sir,
I have one question.
I am using stored procedure to update records in table.
Suppose I want to update employee data for 500 employees. In between error occured at employeeId=275. Now I want to return that updatation is failed at 275. How can we do this?
Batches rules doesn’t accept create table with create schema in the same batch.
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT) — This table will get created under MyscheMA schema
GO
SELECT * FROM MyScheMA.MyTable1 — this works if you use like this
You get an error because you did not specify the schema or you did not put GO after you created the schema.
USE TempDB
GO
CREATE SCHEMA MyScheMA
GO
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1
OR
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
temp tables require the prefix # for local temp tables or ## for global temp tables. since there are neither when the table was created, it cannot be queried.
This error is because when you declare the CREATE SCHEMA MyScheMA sentence and then use the sentence
CREATE MyTable TABLE (INT ID)
This is created with the MySheMa scheme, so when you try to SELECT * FROM Mytable, try searching the table in the schema for a default value that is [dbo], but the reason it is because everything is declared within the same sentence and then the word GO is put
For example if you try with this sentence
USE TempDB
GO
CREATE SCHEMA MyScheMA2
GO
CREATE TABLE MyTable2 (ID INT)
GO
SELECT * FROM MyTable2
note that it does not mark error, because we declare GO after create a SCHEMA
so the table was created in the schema for default [dbo]
When the create table statement was executed the table was created with under the ‘MyScheMA’ schema however the Select statement defaults to the dbo schema, if you change the select to ‘SELECT * FROM MyScheMA.MyTable1’ it runs successfully.
The query gives an error because it is going to find the table in default schema. We need to specify the schema name along with table selection and it is necessary that user has permission to access that schema.
The table created was not owned by DBO schema and the table needs to be fully qualified with schema it was created with.
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyTable1
——-
Solution :
Table created under myschema only. Without mensiton schema name SQL engine by default featch from dbo schema only.
Select * from myschema.mytable // valid
I’m assuming that after creating the schema that the current schema is set to that schema so that MyTable1 is created under that schema. Then after it’s committed with the GO, the next select of MyTable1 goes back to DBO, where that table does not exist.
schema creation script must be the only statement in a batch .
when we create schema with a table in same batch then table get created inside this schema as well.
we are getting this error cause table exists in schema ‘MyScheMA’ Now
so to see the table we should use below script .
SELECT * FROM MyScheMA.MyTable1
If we try to create more tables inside this batch they all will be created under this schema only.
Can I put my answer really anywhere? Any Blog or under any online newspaper article?
Answer is that MyTable1 is object which belongs to schema MyScheMA and without declaration of schema I can`t view content of table.
Correct code:
USE TempDB
GO
CREATE SCHEMA MyScheMA
CREATE TABLE MyTable1 (ID INT)
GO
SELECT * FROM MyScheMA.MyTable1
Because you created a Table inside the TEMPDB Database, this is exactly how it´s supposed to be.
You need to specify the schema too as otherwise it tries to look for the table with the default schema dbo
when a create schema precedes a create table, SQL automatically places table in above created schema if both statements are in same batch and terminated with a GO. After the go SQL defaults to dbo. schema when non is specified explicitly
You need to specify the schema from where you are selecting
SELECT * FROM MyScheMA.MyTable1
Because create schema and table is in same batch, so new table is created under new schema, after go statement, select is executed and table name in select is written without schema name so sql server is trying to select data from table under default schema (dbo), where new table doesn’t exist