精华内容
下载资源
问答
  • SQL临时表

    2014-03-18 22:34:14
    SQL临时表 临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表,SQL Server 将自动除去临时表。临时表不存储在当前数据库内,而是存储在系统数据库 tempdb 内。  临时表有两种类型...

    SQL临时表

    临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表,SQL Server 将自动除去临时表。临时表不存储在当前数据库内,而是存储在系统数据库 tempdb 内。 

    临时表有两种类型: 

    本地临时表 
    以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表,链接断开时临时表即被删除(本地临时表为创建它的该链接的会话所独享)或者这样说局部临时表是有当前用户创建的,并且只有当前用户的会话才可以访问

    如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建(其实可看作是不同的链接,不同的会话),则数据库引擎必须能够区分由不同用户创建的表。为此,数据库引擎在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 的 sysobjects 表中的临时表,其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀,为本地临时表指定的 table_name 不能超过 116 个字符。

    对于本地临时表来说,需要注意在不同情形下应用本地临时表其删除的实际。如假设数据库在执行一个存储过程的时候建立了本地临时表。那么此时这个本地临时表并不是在会话终止的时候自动删除,而是在这个存储过程执行完毕后就会删除。这是什意思呢?也就是说,用户发起的某个会话,为了执行一个特殊的作业(如用户的这个会话调用了某个存储过程)。此时其实就是会话再创建一个子会话的过程。在这种情况下需要注意的是,子会话创建的本地临时表只在子会话内部有效。当这个子会话终止的时候(存储过程执行完毕),此时这个临时表就会自动删除。即对于调用这个子会话的会话来说,这个其子会话的创建的临时表对于其也是无效的,因为临时表已经在子会话关闭的时候自动删除。做一个形象的比喻。即现在做父亲的去叫儿子造一座房子。当儿子死亡的时候,这座房子也会消失。对于这种情况,数据库管理员需要注意。父会话只能够引用子会话从临时表中传递出来的数据。也就是说,父会话要访问子会话创建的临时表的数据,只有一种手段。即先让子会话对临时表中的数据进行查询或者操作,然后把结构回传给父会话。父会话是不能够直接访问子会话所创建的临时表。当然这个限制是专门针对本地临时表而言的。对于全局临时表来说,本身就是所有用户都可以访问,为此就没有这个限制。

    全局临时表 
    以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表或者这样说只要这个全局临时表存在,那么用户创建会话后对所有的用户都是可见的。如果在创建全局临时表的连接断开前没有显式地除去这些表,那么只要所有其它任务停止引用它们,这些表即被除去。当创建全局临时表的连接断开后,新的任务不能再引用它们(换句话说旧的任务还何以引用)。当前的语句一执行完,任务与表之间的关联即被除去;因此通常情况下,只要创建全局临时表的连接断开,全局临时表即被除去。


    --失败的----
    declare 
    @sql varchar(100)
    set @sql='select id as a,name as b into #b from shopName'
    print @sql
    exec(@sql)
    select * from #b
    drop table #b

    消息 208,级别 16,状态 0,第 5 行
    对象名  '#b' 无效。


    ---成功的-----
    declare @sql varchar(100)
    set @sql='select id as a,name as b into ##b from shopName'
    print @sql
    exec(@sql)
    select * from ##b
    drop table ##b  //一定要DROP不然又要报错
     

     当然勿论是全局的还是本地表,只要是能访问的都能用DROP TABLE 来强制地删除临时表。

    当创建本地或全局临时表时,CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其他所有约束定义。如果临时表中指定了 FOREIGN KEY 约束,则该语句将返回一条表明已跳过此约束的警告消息。此表仍将创建,但不使用 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。

    以一个实际的例子来谈谈普通表、本地临时表、全局临时表三个表的差异。如现在有一个保存员工信息的表user。这个表是一个普通表,只要其建立就不会自动删除,任何好在数据库中有使用这个表(具有访问权限)的用户都可以访问这个表,除非这个表被所有者删除或者更改了权限。在用户A(具有访问权限)访问这个表的过程中,数据库可能会根据需要生成一张本地临时表#user。此时只有这个会话才可以访问这个本地临时表。当这个用户的会话中断之后,这个本地临时表也会被自动删除。不过根据需要,数据库也可能会建立全局临时表##user(在名字上与本地临时表不同)。此时数据库中的任何用户只要连接到了数据库就可以访问这个全局临时表(访问权限上的不同)。当这个创建临时表会话的用户中断数据库连接时,这个临时表是否会删除是一个未知数,这要看当时的实际情况(在可用性上不同)。如果此时还有其他用户连接在这个表上的话,那么这个全局临时表就不会被删除。只有在中断连接时,没有其他用户在访问这个表时,即某个用户(不一定是创建这张全局临时表的用户)断开连接并且所有其他的会话不再使用这个表时才会被删除。

      可见无论是全局临时表还是本地临时表,其跟普通表相比,最重要的一个差异就是其会根据需要自动创建。当不再需要时其又会自动删除。这也正是临时表的魅力所在,其可以在数据处理的过程中,减少很多中间表格。

    临时表对日志与锁的影响

      日志文件是数据库中很重要的一个工具。无论是SQL Server数据库还是Oracle数据库,都有日志这个工具。如凭借重做日志工具,数据库管理员可以在数据库故障的时候借此来恢复数据,将数据恢复到故障的那个点上。但是在使用临时表的时候,需要注意一点,就是临时表不会有日志文件。即对临时表进行的DML等操作不会形成日志文件。这个特性即有好处,也有坏处。好处是对于临时表的更改不会保存到日志文件中。也就是说,如果数据库发生了故障,则保存在临时表中的数据是不能够恢复的。为此数据库管理员不得不重新执行某些作业以重新生成临时表中的数据。好处就是对于临时表的DML操作速度会非常的块。除了其他的原因导致其性能的提升外,在更改其内容时不会生成日志信息也是一个重要的原因。为此对临时表的操作不生成日志信息,这是一个双刃剑。数据库管理员在日常工作中,要尽量发挥其优势,减少其负面作用的影响。

      另外,若采用临时表这种处理机制的话,还需要注意其对锁的影响。在介绍本地临时表与全局临时表差异的时候,笔者就介绍过,本地临时表只对当前的会话有效。即使当前会话又创建了另外一个子会话,也只对子会话有效。当某个会话终止的时候,这临时表就会自动被删除。而对于普通表或者全局临时表来说,可能同时多个会话都可以访问这个表。这两者有什么区别呢?若允许多个会话可以同时访问某个表的话,那么这个表就可能会遇到锁的情况。即某个用户会话在对表中地记录进行DML等操作时,为了保证数据的一致性,会对相关的记录进行加锁等措施。而采用本地临时表的话,由于只有一个会话可以访问临时表中的数据,所以即使这个会话更改临时表中的数据,也不会有锁冲突的问题。故其在更改本地临时表中的数据时,就不用为其加锁。所以,对于本地临时表的操作速度就要比其他表来的快。故在何时的情况下使用临时表无疑可以提高数据库的整体性能。如可以将一些操作在临时表中完成,然后再将最后的结果更新到基本表中。

     

     

    利用SQL的全局临时表防止用户重复登录

          在我们开发商务软件的时候,常常会遇到这样的一个问题:怎样防止用户重复登录我们的系统?特别是对于银行或是财务部门,更是要限制用户以其工号身份多次登入。
      
      可能会有人说在用户信息表中加一字段判断用户工号登录的状态,登录后写1,退出时写0,且登录时判断其标志位是否为1,如是则不让该用户工号登录。但是这样那势必会带来新的问题:如发生象断电之类不可预知的现象,系统是非正常退出,无法将标志位置为0,那么下次以该用户工号登录则不可登入.
        


    create 
    procedure gp_findtemptable 
          /* 寻找以操作员工号命名的全局临时表  
      * 如无则将out参数置为0并创建该表,如有则将out参数置为1  
      * 在connection断开连接后,全局临时表会被SQL Server自动回收   
      * 如发生断电之类的意外,全局临时表虽然还存在于tempdb中,但是已经失去活性  
      * 用object_id函数去判断时会认为其不存在.
      
    */
      @v_userid varchar(6), -- 操作员工号
      
      @i_out int out -- 输出参数 0:没有登录 1:已经登录
      
      as
      
      declare @v_sql varchar(100)
      
      if object_id('tempdb.dbo.##'+@v_useridis null
      
      begin
      
      set @v_sql = 'create table ##'+@v_userid+'(userid varchar(6))'
      
      exec (@v_sql)
      
      set @i_out = 0
      
      end
      
      else
      
      set @i_out = 1

      
      在这个过程中,我们看到如果以用户工号命名的全局临时表不存在时过程会去创建一张并把out参数置为0,如果已经存在则将out参数置为1。
      
      这样,我们在我们的应用程序中调用该过程时,如果取得的out参数为1时,我们可以毫不客气地跳出一个message告诉用户说”对不起,此工号正被使用!”

    上面还涉及到一个OBJECT_ID ()函数:

     

    Syntax:

    OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . object_name' [ ,'object_type' ] )

    一般语法:int object_id('objectname');

    此方法返回数据库对象标识号。

    其中,参数objectname 表示要使用的对象,其数据类型为nchar或char(如果为char,系统将其转换为nchar)

    object_type:为可选参数,其数据类型为nchar或char(如果为char,系统将其转换为nchar),指明架构范围的对象类型(object_name为字符串通过它,可以说明这个字符串究竟是说明对象,其列表见文章结尾)

     

    ps:使用 OBJECT_ID 不能查询非架构范围内的对象(如 DDL 触发器)。对于在 sys.objects 目录视图中找不到的对象,需要通过查询适当的目录视图来获取该对象的标识号。例如,若要返回 DDL 触发器的对象标识号,请使用 SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'

     

    返回类型为int,表示该对象在系统中的编号,如果找不到或发生错误一律返回NULL。

     

    例子:

    A.返回数据库AdventureWorks中Production.WorkOrder表的标识号

     

    USE master;
    GO
    SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder'AS 'Object ID';
    GO

     

    B.存在性检查

    下列会确认资料表有物件的标识码,藉此检查指定的资料表是否存在。如果存在就删除。

    USE AdventureWorks;
    GO
    IF OBJECT_ID (N'dbo.AWBuildVersion', N'U'IS NOT NULL
    DROP TABLE dbo.AWBuildVersion;
    GO

    此方法一般用来判断数据库中本来用没有此对象(procedures,views,functions等).

    注意:
    当该参数对系统函数可选时,则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。 
    如果指定一个临时表名,除非当前数据库为tempdb(废话),否则必须在临时表名前面加上数据库名,例如: 
    SELECT OBJECT_ID('tempdb..#mytemptable')

     

     

    Object_Type列表:

     

     

    AF = 聚合函数 (CLR)
    C = CHECK 约束
    D = DEFAULT(约束或独立)
    F = FOREIGN KEY 约束
    FN = SQL 标量函数
    FS = 程序集 (CLR) 标量函数
    FT = 程序集 (CLR) 表值函数
    IF = SQL 内联表值函数
    IT = 内部表
    P = SQL 存储过程
    PC = 程序集 (CLR) 存储过程
    PG = 计划指南
    PK = PRIMARY KEY 约束
    R = 规则(旧式,独立)
    RF = 复制筛选过程
    S = 系统基表
    SN = 同义词
    SQ = 服务队列
    TA = 程序集 (CLR) DML 触发器
    TF = SQL 表值函数
    TR = SQL DML 触发器
    U = 表(用户定义类型)
    UQ = UNIQUE 约束
    V = 视图
    X = 扩展存储过程
    展开全文
  • SQL 临时表

    2010-09-03 13:46:00
    来源:http://writeblog.csdn.net/PostEdit.aspx<br />SQL 临时表 文章分类:数据库 临时表与永久表相似,但临时表存储在 tempdb 中,当不再使用时会自动删除。 临时表有两种类型:本地和全局。...

    来源:http://writeblog.csdn.net/PostEdit.aspx

    SQL 临时表

    文章分类:数据库

    临时表与永久表相似,但临时表存储在 tempdb 中,当不再使用时会自动删除。
    临时表有两种类型:本地和全局。它们在名称、可见性以 及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头;它们仅对当前的用户连接是可见的;当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头,创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。
    例如,如果创建了 employees 表,则任何在数据库中有使用该表的安全权限的用户都可以使用该表,除非已将其删除。如果数据库会话创建了本地临时表 #employees,则仅会话可以使用该表,会话断开连接后就将该表删除。如果创建了 ##employees 全局临时表,则数据库中的任何用户均可使用该表。如果该表在您创建后没有其他用户使用,则当您断开连接时该表删除。如果您创建该表后另一个用户在使用该 表,则 SQL Server 将在您断开连接并且所有其他会话不再使用该表时将其删除。

    1、局部临时表(#开头)只对当前连接有效,当前连接断开时自动删除。   
    2、全局临时表(##开头)对其它连接也有效,在当前连接和其他访问过它的连接都断开时自动删除。   
    3、不管局部临时表还是全局临时表,只要连接有访问权限,都可以用drop table #Tmp(或者drop table ##Tmp)来显式删除临时表。   
    使用全局临时表需要加上   
    if object_id('tempdb..##临时表') is not null
    drop table ##临时表
    else
    creeate table ##临时表..

     

     

    SQL SERVER临时表的使用

    drop table #Tmp   --删除临时表#Tmp
    create table #Tmp --创建临时表#Tmp
    (
        ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
        WokNo                varchar(50),  
        primary key (ID)      --定义ID为临时表#Tmp的主键     
    );
    Select * from #Tmp    --查询临时表的数据
    truncate table #Tmp --清空临时表的所有数据和约束

    相关例子:

    Declare @Wokno Varchar(500) --用来记录职工号
    Declare @Str NVarchar(4000) --用来存放查询语句
    Declare @Count int --求出总记录数     
    Declare @i int
    Set @i = 0
    Select @Count = Count(Distinct(Wokno)) from #Tmp
    While @i < @Count
        Begin
           Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
           Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
           Select @WokNo,@i --一行一行把职工号显示出来
           Set @i = @i + 1
        End
    临时表
    可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。

    本地临时表的名称前面有一个编号符 (#table_name),而全局临时表的名称前面有两个编号符 (##table_name)。

    SQL 语句使用 CREATE TABLE 语句中为 table_name 指定的名称引用临时表:

    CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
    INSERT INTO #MyTempTable VALUES (1)

    如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建,则 SQL Server 必须能够区分由不同用户创建的表。为此,SQL Server 在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表,其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀,为本地临时表指定的表名 table_name 不能超过 116 个字符。

    除非使用 DROP TABLE 语句显式除去临时表,否则临时表将在退出其作用域时由系统自动除去:

    当存储过程完成时,将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程 的进程无法引用此表。


    所有其它本地临时表在当前会话结束时自动除去。


    全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之,当创建全局临时表的会话结束时,最后一条引用此表的 Transact-SQL 语句完成后,将自动除去此表。
    在 存储过程或触发器中创建的本地临时表与在调用存储过程或触发器之前创建的同名临时表不同。如果查询引用临时表,而同时有两个同名的临时表,则不定义针对哪 个表解析该查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引用都被解释为是针对该嵌套过程所 创建的表,例如:

    CREATE PROCEDURE Test2
    AS
    CREATE TABLE #t(x INT PRIMARY KEY)
    INSERT INTO #t VALUES (2)
    SELECT Test2Col = x FROM #t
    GO
    CREATE PROCEDURE Test1
    AS
    CREATE TABLE #t(x INT PRIMARY KEY)
    INSERT INTO #t VALUES (1)
    SELECT Test1Col = x FROM #t
    EXEC Test2
    GO
    CREATE TABLE #t(x INT PRIMARY KEY)
    INSERT INTO #t VALUES (99)
    GO
    EXEC Test1
    GO

    下面是结果集:

    (1 row(s) affected)

    Test1Col   
    -----------
    1          

    (1 row(s) affected)

    Test2Col   
    -----------
    2          

    当创建本地或全局临时表时,CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其它所有约束定义。如果在临时表中指定 FOREIGN KEY 约束,该语句将返回警告信息,指出此约束已被忽略,表仍会创建,但不具有 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。

    考虑使用表变量而不使用临时表。当需要在临时表上显式地创建索引时,或多个存储过程或函数需要使用表值时,临时表很有用。通常,表变量提供更有效的 查询处理。

    展开全文
  • sql临时表_SQL临时表

    2020-07-17 00:14:52
    sql临时表In real time scenario, consider that you have read-only access on a table and you have to manipulate some data in it. It is always useful to create a temp table when you want to use the table...

    sql临时表

    In real time scenario, consider that you have read-only access on a table and you have to manipulate some data in it. It is always useful to create a temp table when you want to use the table in the current session.

    在实时方案中,请考虑您对表具有只读访问权限,并且必须操纵其中的某些数据。 当您要在当前会话中使用临时表时,创建临时表总是很有用的。

    介绍 (Introduction)

    In this tutorial, we will discuss the temporary table in MySQL. Following are some features of the temporary table.

    在本教程中,我们将讨论MySQL中的临时表。 以下是临时表的一些功能。

    • MySQL removes a temporary table when we close the session or if we close the connection.

      当我们关闭会话或关闭连接时,MySQL会删除一个临时表。
    • A temporary table is only visible to the client who has created it.

      临时表仅对创建它的客户端可见。
    • A temporary table can share the same name as a normal table.

      临时表可以与普通表共享相同的名称。
    • Two temporary tables can also have the same name if there are in different sessions.

      如果两个临时表处于不同的会话中,则它们也可以具有相同的名称。
    • If the temporary table and the existing normal table shares the same name. The normal table becomes inaccessible until the temporary table is not removed.

      如果临时表和现有的普通表共享相同的名称。 在不删除临时表之前,普通表将无法访问。

    We will now consider creation, use and removal of a temporary table in MySQL.

    现在,我们将考虑在MySQL中创建,使用和删除临时表。

    创建一个临时表 (Create a Temporary Table)

    Syntax for Create Temporary table: –

    创建临时表的语法:–

    CREATE TEMPORARY TABLE table_name SELECT column(s) FROM existing_table;

    In the above syntax, due to the keyword Temporary, a temporary table gets created.

    在上述语法中,由于关键字Temporary,将创建一个临时表。

    Let us create a Library table and we will use the same table for the creation of a temporary table.

    让我们创建一个Library表,并将使用相同的表创建一个临时表。

    CREATE TABLE `library` (
    `idLibrary` int(11) NOT NULL,
    `BookTitle` varchar(45) DEFAULT NULL,
    `BookQuantity` int(11) DEFAULT NULL,
    `Author` varchar(45) DEFAULT NULL,
    `BookPrice` float DEFAULT NULL,
    PRIMARY KEY (`idLibrary`),
    UNIQUE KEY `idLibrary_UNIQUE` (`idLibrary`)
    )

    The below-mentioned query will be used for data insertion.

    以下查询将用于数据插入。

    INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(1,'The Chamber of Secrets',10,'J K Rowling',20.99);
    INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(2,'One night at the call center',13,'Chetan Bhagat',100.99);
    INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(3,'The God of Small things',11,'Arundhati Roy',120.99);
    INSERT INTO `test`.`library`(`idLibrary`,`BookTitle`,`BookQuantity`,`Author`,`BookPrice`)VALUES(4,'War and Peace',5,'Leo Tolstoy',80.00);
    SQL Table Before Copy

    SQL Library Table 

    SQL库表

    We will create a temporary table with book quantity of more than 10.

    我们将创建一个图书数量大于10的临时表。

    Create temporary table libary_10 select * from library where bookquantity>10;

    Now we will use Select statement for check if the table is created.

    现在,我们将使用Select语句检查表是否已创建。

    Select * from libary_10;
    Temp Table Created

    Temp Table Created

    临时表已创建

    临时表的用法 (Usage of Temporary Table)

    Now we have the table created, assume we would like to get the highest book price. We will use the below mentioned query to get the result set.

    现在我们已经创建了表格,假设我们想获得最高的书价。 我们将使用下面提到的查询来获取结果集。

    Select max(bookprice) from libary_10;
    Usage Of Temp Table

    Usage Of Temp Table

    临时表的用法

    Now we will understand how to remove a temporary table.

    现在,我们将了解如何删除临时表。

    删除临时表 (Remove Temporary Table)

    Syntax for Drop Temporary Table:

    删除临时表的语法:

    Drop Temporary Table temp_table_name;

    The Temporary keyword is used to make sure that by mistake the permanent table is not deleted. If we try to remove a permanent table using the above-mentioned syntax, we will get an error.

    Temporary关键字用于确保错误地删除了永久表。 如果我们尝试使用上述语法删除永久表,则会收到错误消息。

    Let us see how to drop the temporary table.

    让我们看看如何删除临时表。

    Drop Temporary Table libary_10;

    That’s all for SQL Temporary table;

    这就是SQL临时表的全部内容;

    翻译自: https://www.journaldev.com/28597/sql-temp-table

    sql临时表

    展开全文
  • sql 临时表

    2014-03-21 15:08:54
    如果当用户断开连接时没有除去临时表SQL Server 将自动除去临时表临时表不存储在当前数据库内,而是存储在系统数据库 tempdb 内。  临时表有两种类型:  本地临时表  以一个井号 (#) 开头的那些表名。...
    临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表,SQL Server 将自动除去临时表。临时表不存储在当前数据库内,而是存储在系统数据库 tempdb 内。 

    临时表有两种类型: 

    本地临时表 
    以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表,链接断开时临时表即被删除(本地临时表为创建它的该链接的会话所独享)或者这样说局部临时表是有当前用户创建的,并且只有当前用户的会话才可以访问

    如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建(其实可看作是不同的链接,不同的会话),则数据库引擎必须能够区分由不同用户创建的表。为此,数据库引擎在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 的 sysobjects 表中的临时表,其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀,为本地临时表指定的 table_name 不能超过 116 个字符。

    对于本地临时表来说,需要注意在不同情形下应用本地临时表其删除的实际。如假设数据库在执行一个存储过程的时候建立了本地临时表。那么此时这个本地临时表并不是在会话终止的时候自动删除,而是在这个存储过程执行完毕后就会删除。这是什意思呢?也就是说,用户发起的某个会话,为了执行一个特殊的作业(如用户的这个会话调用了某个存储过程)。此时其实就是会话再创建一个子会话的过程。在这种情况下需要注意的是,子会话创建的本地临时表只在子会话内部有效。当这个子会话终止的时候(存储过程执行完毕),此时这个临时表就会自动删除。即对于调用这个子会话的会话来说,这个其子会话的创建的临时表对于其也是无效的,因为临时表已经在子会话关闭的时候自动删除。做一个形象的比喻。即现在做父亲的去叫儿子造一座房子。当儿子死亡的时候,这座房子也会消失。对于这种情况,数据库管理员需要注意。父会话只能够引用子会话从临时表中传递出来的数据。也就是说,父会话要访问子会话创建的临时表的数据,只有一种手段。即先让子会话对临时表中的数据进行查询或者操作,然后把结构回传给父会话。父会话是不能够直接访问子会话所创建的临时表。当然这个限制是专门针对本地临时表而言的。对于全局临时表来说,本身就是所有用户都可以访问,为此就没有这个限制。

    全局临时表 
    以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表或者这样说只要这个全局临时表存在,那么用户创建会话后对所有的用户都是可见的。如果在创建全局临时表的连接断开前没有显式地除去这些表,那么只要所有其它任务停止引用它们,这些表即被除去。当创建全局临时表的连接断开后,新的任务不能再引用它们(换句话说旧的任务还何以引用)。当前的语句一执行完,任务与表之间的关联即被除去;因此通常情况下,只要创建全局临时表的连接断开,全局临时表即被除去。


    --失败的----
    declare 
    @sql varchar(100)
    set @sql='select id as a,name as b into #b from shopName'
    print @sql
    exec(@sql)
    select * from #b
    drop table #b

    消息 208,级别 16,状态 0,第 5 行
    对象名  '#b' 无效。


    ---成功的-----
    declare @sql varchar(100)
    set @sql='select id as a,name as b into ##b from shopName'
    print @sql
    exec(@sql)
    select * from ##b
    drop table ##b  //一定要DROP不然又要报错
     

     当然勿论是全局的还是本地表,只要是能访问的都能用DROP TABLE 来强制地删除临时表。

    当创建本地或全局临时表时,CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其他所有约束定义。如果临时表中指定了 FOREIGN KEY 约束,则该语句将返回一条表明已跳过此约束的警告消息。此表仍将创建,但不使用 FOREIGN KEY 约束。在 FOREIGN KEY 约束中不能引用临时表。

    以一个实际的例子来谈谈普通表、本地临时表、全局临时表三个表的差异。如现在有一个保存员工信息的表user。这个表是一个普通表,只要其建立就不会自动删除,任何好在数据库中有使用这个表(具有访问权限)的用户都可以访问这个表,除非这个表被所有者删除或者更改了权限。在用户A(具有访问权限)访问这个表的过程中,数据库可能会根据需要生成一张本地临时表#user。此时只有这个会话才可以访问这个本地临时表。当这个用户的会话中断之后,这个本地临时表也会被自动删除。不过根据需要,数据库也可能会建立全局临时表##user(在名字上与本地临时表不同)。此时数据库中的任何用户只要连接到了数据库就可以访问这个全局临时表(访问权限上的不同)。当这个创建临时表会话的用户中断数据库连接时,这个临时表是否会删除是一个未知数,这要看当时的实际情况(在可用性上不同)。如果此时还有其他用户连接在这个表上的话,那么这个全局临时表就不会被删除。只有在中断连接时,没有其他用户在访问这个表时,即某个用户(不一定是创建这张全局临时表的用户)断开连接并且所有其他的会话不再使用这个表时才会被删除。

      可见无论是全局临时表还是本地临时表,其跟普通表相比,最重要的一个差异就是其会根据需要自动创建。当不再需要时其又会自动删除。这也正是临时表的魅力所在,其可以在数据处理的过程中,减少很多中间表格。

    临时表对日志与锁的影响

      日志文件是数据库中很重要的一个工具。无论是SQL Server数据库还是Oracle数据库,都有日志这个工具。如凭借重做日志工具,数据库管理员可以在数据库故障的时候借此来恢复数据,将数据恢复到故障的那个点上。但是在使用临时表的时候,需要注意一点,就是临时表不会有日志文件。即对临时表进行的DML等操作不会形成日志文件。这个特性即有好处,也有坏处。好处是对于临时表的更改不会保存到日志文件中。也就是说,如果数据库发生了故障,则保存在临时表中的数据是不能够恢复的。为此数据库管理员不得不重新执行某些作业以重新生成临时表中的数据。好处就是对于临时表的DML操作速度会非常的块。除了其他的原因导致其性能的提升外,在更改其内容时不会生成日志信息也是一个重要的原因。为此对临时表的操作不生成日志信息,这是一个双刃剑。数据库管理员在日常工作中,要尽量发挥其优势,减少其负面作用的影响。

      另外,若采用临时表这种处理机制的话,还需要注意其对锁的影响。在介绍本地临时表与全局临时表差异的时候,笔者就介绍过,本地临时表只对当前的会话有效。即使当前会话又创建了另外一个子会话,也只对子会话有效。当某个会话终止的时候,这临时表就会自动被删除。而对于普通表或者全局临时表来说,可能同时多个会话都可以访问这个表。这两者有什么区别呢?若允许多个会话可以同时访问某个表的话,那么这个表就可能会遇到锁的情况。即某个用户会话在对表中地记录进行DML等操作时,为了保证数据的一致性,会对相关的记录进行加锁等措施。而采用本地临时表的话,由于只有一个会话可以访问临时表中的数据,所以即使这个会话更改临时表中的数据,也不会有锁冲突的问题。故其在更改本地临时表中的数据时,就不用为其加锁。所以,对于本地临时表的操作速度就要比其他表来的快。故在何时的情况下使用临时表无疑可以提高数据库的整体性能。如可以将一些操作在临时表中完成,然后再将最后的结果更新到基本表中。

     

     

    利用SQL的全局临时表防止用户重复登录

          在我们开发商务软件的时候,常常会遇到这样的一个问题:怎样防止用户重复登录我们的系统?特别是对于银行或是财务部门,更是要限制用户以其工号身份多次登入。
      
      可能会有人说在用户信息表中加一字段判断用户工号登录的状态,登录后写1,退出时写0,且登录时判断其标志位是否为1,如是则不让该用户工号登录。但是这样那势必会带来新的问题:如发生象断电之类不可预知的现象,系统是非正常退出,无法将标志位置为0,那么下次以该用户工号登录则不可登入.
        


    create 
    procedure gp_findtemptable 
          /* 寻找以操作员工号命名的全局临时表  
      * 如无则将out参数置为0并创建该表,如有则将out参数置为1  
      * 在connection断开连接后,全局临时表会被SQL Server自动回收   
      * 如发生断电之类的意外,全局临时表虽然还存在于tempdb中,但是已经失去活性  
      * 用object_id函数去判断时会认为其不存在.
      
    */
      @v_userid varchar(6), -- 操作员工号
      
      @i_out int out -- 输出参数 0:没有登录 1:已经登录
      
      as
      
      declare @v_sql varchar(100)
      
      if object_id('tempdb.dbo.##'+@v_useridis null
      
      begin
      
      set @v_sql = 'create table ##'+@v_userid+'(userid varchar(6))'
      
      exec (@v_sql)
      
      set @i_out = 0
      
      end
      
      else
      
      set @i_out = 1

      
      在这个过程中,我们看到如果以用户工号命名的全局临时表不存在时过程会去创建一张并把out参数置为0,如果已经存在则将out参数置为1。
      
      这样,我们在我们的应用程序中调用该过程时,如果取得的out参数为1时,我们可以毫不客气地跳出一个message告诉用户说”对不起,此工号正被使用!”

    上面还涉及到一个OBJECT_ID ()函数:

     

    Syntax:

    OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . object_name' [ ,'object_type' ] )

    一般语法:int object_id('objectname');

    此方法返回数据库对象标识号。

    其中,参数objectname 表示要使用的对象,其数据类型为nchar或char(如果为char,系统将其转换为nchar)

    object_type:为可选参数,其数据类型为nchar或char(如果为char,系统将其转换为nchar),指明架构范围的对象类型(object_name为字符串通过它,可以说明这个字符串究竟是说明对象,其列表见文章结尾)

     

    ps:使用 OBJECT_ID 不能查询非架构范围内的对象(如 DDL 触发器)。对于在 sys.objects 目录视图中找不到的对象,需要通过查询适当的目录视图来获取该对象的标识号。例如,若要返回 DDL 触发器的对象标识号,请使用 SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'

     

    返回类型为int,表示该对象在系统中的编号,如果找不到或发生错误一律返回NULL。

     

    例子:

    A.返回数据库AdventureWorks中Production.WorkOrder表的标识号

     

    USE master;
    GO
    SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder'AS 'Object ID';
    GO

     

    B.存在性检查

    下列会确认资料表有物件的标识码,藉此检查指定的资料表是否存在。如果存在就删除。

    USE AdventureWorks;
    GO
    IF OBJECT_ID (N'dbo.AWBuildVersion', N'U'IS NOT NULL
    DROP TABLE dbo.AWBuildVersion;
    GO

    此方法一般用来判断数据库中本来用没有此对象(procedures,views,functions等).

    注意:
    当该参数对系统函数可选时,则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。 
    如果指定一个临时表名,除非当前数据库为tempdb(废话),否则必须在临时表名前面加上数据库名,例如: 
    SELECT OBJECT_ID('tempdb..#mytemptable')

     

     

    Object_Type列表:

     

     

    AF = 聚合函数 (CLR)
    C = CHECK 约束
    D = DEFAULT(约束或独立)
    F = FOREIGN KEY 约束
    FN = SQL 标量函数
    FS = 程序集 (CLR) 标量函数
    FT = 程序集 (CLR) 表值函数
    IF = SQL 内联表值函数
    IT = 内部表
    P = SQL 存储过程
    PC = 程序集 (CLR) 存储过程
    PG = 计划指南
    PK = PRIMARY KEY 约束
    R = 规则(旧式,独立)
    RF = 复制筛选过程
    S = 系统基表
    SN = 同义词
    SQ = 服务队列
    TA = 程序集 (CLR) DML 触发器
    TF = SQL 表值函数
    TR = SQL DML 触发器
    U = 表(用户定义类型)
    UQ = UNIQUE 约束
    V = 视图
    X = 扩展存储过程
     
    展开全文
  • sql临时表

    2011-12-03 17:26:10
    SQL中系统临时表的使用和禁忌 及 Exec(SQLScript) 的使用 以前在SQL 中写SP 时,如比较复杂时,喜欢中间使用临时表来暂存相关记录,这样的好处有很多,提高效率,提高程序的可读性等。当然后临时表的使用,一般均...
  • sql 临时表

    2010-03-09 09:32:00
    sql server 临时表的使用 以及给临时表插入主键列--某时间段内某组服务器的消费排行CREATE PROCEDURE biling_order_consume @begin_date datetime, @end_date datetimeAS select identity(int,1,1) as ID,charge_...
  • sql临时表相关介绍

    2013-05-21 11:12:57
    sql临时表相关介绍,可以创建本地和全局临时表。本地临时表仅在当前会话中可见;全局临时表在所有会话中都可见。 本地临时表的名称前面有一个编号符 (#table_name),而全局临时表的名称前面有两个编号符 (##table_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,572
精华内容 4,228
关键字:

sql临时表