SQL Server中临时表是在什么schema下的(转载)
Specifying schema for temporary tables
问:
I'm used to seeing temporary tables created with just the hash/number symbol, like this:
CREATE TABLE #Test
(
[Id] INT
)
However, I've recently come across stored procedure code that specifies the schema name when creating temporary tables, for example:
CREATE TABLE [dbo].[#Test]
(
[Id] INT
)
Is there any reason why you would want to do this? If you're only specifying the user's default schema, does it make any difference? Does this refer to the [dbo] schema in the local database or the tempdb database?
答:
It won't make any difference if you are specifying the users default schema, but if the users default schema changes then it will try to keep the temporary table in the dbo schema.
Temp tables are created in tempdb so it means you'd need to maintain the schema in tempdb, and offers no benefit.
跟帖:
If you create a schema on tempdb, and map a user to tempdb with that default schema, running CREATE TABLE #test (v INT) will create the table as "dbo.#test". Trying to create a temp table under a schema other than dbo will ignore the schema you speciy and create it in the dbo schema regardless. So CREATE TABLE dummy.#test (v INT) will also create "dbo.#test". --Jim
从上面最后Jim的跟帖中我们可以得知,临时表的schema默认都是位于tempdb数据库下的dbo,如果我们尝试给临时表声明非dbo的schema,例如:CREATE TABLE dummy.#test (v INT),SQL Server会忽略在临时表#test前声明的schema名dummy,创建的还是"dbo.#test",因此给临时表主动声明schema是没有意义的,所有的临时表都会创建在dbo这个系统schema下。
最新文章
- 利用poi导出Excel
- 代码-->;发呆
- 【Linux】Linux中常用操作命令
- UDP SOCKET网络通信 C#
- JQuery学习笔记——JQuery基础
- struct stat结构体的详解和用法
- javascript 获取父页面中元素对象方法
- Lua for Windows入门01
- Cocos2D:塔防游戏制作之旅(一)
- ColorUtil【Color工具类(color整型、rgb数组、16进制互相转换)】
- YASnippet - emacs 的代码片段管理工具
- kodexplorer免费网盘安装教程
- window注册鼠标右键菜单,及子菜单
- [20181109]12c sqlplus rowprefetch参数5
- 自媒体运营排版利器----Markdown here
- eclipse git 分享项目到GitHub上
- mapreduce的输入格式 --- InputFormat
- VUX
- * args 和 **kwargs
- sql语句-6-高级查询