网站开发工程师招聘信息,wordpress怎么置顶文章,常州app网站,个人网站备案怎么写首先让我们来看看什么是表变量和临时表。sql server 表变量1.初识表变量表变量在sql server 2000中首次被引用。表变量的定义和创建一个表大致相同#xff0c;只不过是使用DECLARE variable而不是CREATE Table#xff0c;表变量定义包括列定义#xff0c;列名#xff0c;数…首先让我们来看看什么是表变量和临时表。sql server 表变量1.初识表变量表变量在sql server 2000中首次被引用。表变量的定义和创建一个表大致相同只不过是使用DECLARE variable而不是CREATE Table表变量定义包括列定义列名数据类型和约束(可用的约束有主键约束、唯一约束、null约束、和CHECK约束。注外键约束不可以哦)。表变量和其他变量不同它不止存在于内存中还存在于tempdb中。2.表变量优点1).表变量拥有特定的作用域(在当前批处理中)表变量在批处理结束后自动被清除。2).在存储过程中使用表变量较临时表会减少存储过程重新编译的发生。3).表变量需要更少的锁请求和日志资源。4).可以在表变量上使用UDF,UDDT,XML3.表变量的缺点1).在表变量上没有统计信息查询优化器根据固定的预估值来选择执行计划在数据量很大的情况下会导致查询优化器选择很差的执行计划。2).不能在表变量上创建索引但是可以通过创建约束(主键、唯一)来建立索引3).在 DECLARE 之后不能再对表变量进行更改4).不能对表变量使用 SELECT INTOINSERT EXEC 语句5).不能通过EXEC或sp_executeSQL 来执行牵涉表变量的动态SQL语句但如果表变量是在动态SQL语句内定义的则可以。4.何时使用表变量(可以根据以下规则来判断何时使用表变量)1).表的行数2).使用表变量能够减少的重新编译次数3).查询的类型和对索引或者统计信息的依赖程度4).是否需要使用UDF,UDDF,XML5.表变量不能做的事1).虽然表变量是一个变量但是其不能赋值给另一个变量。2).check约束、默认值、计算列不能引用自定义函数。3).不能为约束命名4).不能truncate 表变量5).不能向标识列中插入显示值(也就是说表变量不支持SET IDENTITY_INSERT ON)sql server 临时表临时表和Create Table语句创建的表的不同之处在于1).临时表的名称不能超过116个字符这是由于数据库引擎为了辨别不同会话建立的临时表会自动在临时表名字后附加一串。2).局部临时表作用域仅仅在当前的连接内从存储过程中建立局部临时表的角度来看局部临时表会在以下情况下别Dropa.显示调用DROP TABLE语句b.当局部临时表在存储过程内被创建时存储过程结束就意味着局部临时表被Dropc.当前会话结束在会话内创建的所有局部临时表都会被DROP。3).全局临时表在所有的会话内可见但是在其创建的会话结束后即被dropdrop后其它会话也将不能对其进行引用。4).不能对临时表进行分区。5).不能对临时表加外键约束。6).临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象而临时表属于TempDb)。由于TempDb在每次SQL Server重启后会被自动创建所以你必须使用startup stored procedure来为TempDb创建自定义数据类型。你也可以通过修改Model数据库来达到这一目标。7).XML列不能定义成XML集合的形式除非这个集合已经在TempDb中定义。至于表变量和临时表的区别在介绍表变量时所提到的优缺点中基本概括了另外还有一个区别是体现在排序规则上表变量使用当前数据库的排序规则临时表使用TempDb的排序规则。如果它们不兼容你还需要在查询或者表定义中进行指定。下面是看到的别人整理的一张表格看着清晰一点。总结特性表变量临时表作用域当前批处理当前会话嵌套存储过程全局所有会话使用场景自定义函数存储过程批处理自定义函数存储过程批处理创建方式DECLARE statement only.只能通过DECLEARE语句创建CREATE TABLE 语句SELECT INTO 语句.表名长度最多128字节最多116字节列类型可以使用自定义数据类型可以使用XML集合自定义数据类型和XML集合必须在TempDb内定义Collation字符串排序规则继承自当前数据库字符串排序规则继承自TempDb数据库索引索引必须在表定义时建立索引可以在表创建后建立约束PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用但必须在表建立时声明PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用可以在任何时后添加但不能有外键约束表建立后使用DDL (索引列)不允许允许.数据插入方式INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC).INSERT 语句, 包括 INSERT/EXEC.SELECT INTO 语句.Insert explicit values into identity columns (SET IDENTITY_INSERT).不支持SET IDENTITY_INSERT语句支持SET IDENTITY_INSERT语句Truncate table不允许允许析构方式批处理结束后自动析构显式调用 DROP TABLE 语句.当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)事务只会在更新表的时候有事务持续时间比临时表短正常的事务长度比表变量长存储过程重编译否会导致重编译回滚不会被回滚影响会被回滚影响统计数据不创建统计数据所以所有的估计行数都为1,所以生成执行计划会不精准创建统计数据通过实际的行数生成执行计划。作为参数传入存储过程仅仅在SQL Server2008, 并且必须预定义 user-defined table type.不允许显式命名对象 (索引, 约束).不允许允许但是要注意多用户的问题动态SQL必须在动态SQL中定义表变量可以在调用动态SQL之前定义临时表
