同一連接(Session)建的暫存物件都在 tempdb,故無法用 schema

Ellie 提了一個有趣的問題:請問Temporary table是不是沒有schema的概念?

在不同schema下,無法create 同一名稱的temp table

Use TempDB

GO

Create schema schema_aa

GO

Create schema schema_bb

GO

create table schema_aa.#TT (C1 int)

GO

create table schema_bb.#TT (C1 int)

GO

–Msg 2714, Level 16, State 6, Line 2

–There is already an object named ‘#TT’ in the database..

 

然後,四節名稱我亂打,居然也都能select出結果 …

四節名稱隨便打,卻不影響SQL解析temporary物件名稱 => 請問這是什麼原理呢?

image

 

我的想法是:同一連接(Session)建的暫存物件都在 tempdb,故無法用 schema

因為同一連接可以切換不同 Context DB,而 Schema 分別隸屬個別 DB,但暫存物件都實際建在 TempDB,則 TempDB 很難維護分屬其他 DB 和 TempDB 自己的 Schema。稍微添加一點 Ellie 的範例進一步來檢視:

Use TempDB
GO
 
Create schema schema_aa
GO
Create schema schema_bb
GO

–非暫存物件,在 tempdb 也可以用 schema
create table schema_aa.TT (C1 int)
GO
create table schema_aa.#TT (C1 int)
GO
create table schema_aa.##TT (C1 int)
go

create table schema_bb.#TT (C1 int)
GO
–Msg 2714, Level 16, State 6, Line 2
–There is already an object named ‘#TT’ in the database..
 
 
select schema_name(schema_id),* from sys.tables
go
create proc schema_aa.sp
as
print ‘schema_aa.sp’
go

–暫存 sp 的狀況與暫存 table 相同
create proc schema_aa.#sp
as
print ‘schema_aa.#sp’
go

select schema_name(schema_id),* from sys.procedures

use northwind
go

create schema s
go

create table s.#t(c1 int)
create table s.##t(c1 int)
go

–因為沒有 schema,且也是建在 tempdb,所以會跟先前建立的 sp 相衝
–訊息 2714,層級 16,狀態 3,程序 #sp,行 1
–資料庫中已經有一個名為 ‘#sp’ 的物件。
create proc s.#sp
as
print ‘s.#sp’
go

select schema_name(schema_id),* from sys.tables
select schema_name(schema_id),* from tempdb.sys.tables

 

 

Ellie:

所以暫存物件也就沒有所謂的四節名稱了嗎?

 

我:

照你的 Sample 看起來是 temp 物件的名稱解析不管物件名稱之前的前三節。

從另一個面向來想,Session 內的任和暫存物件都在 tempdb,那何必在乎後三節。而第一節的 Instance 名稱,我尚想不到如何在遠端 Instance,透過相同的 Session 針對 Linked Server 建立 temp 物件,然後再以四節名稱使用它。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: