sql server 2005数据修改的示例分析

这篇文章主要为大家展示了“sql server 2005数据修改的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“sql server 2005数据修改的示例分析”这篇文章吧。当插入新的记录到数据表中时，SQL Server必须决定在哪里插入记录。如果数据表没有聚集索引—也就是说，数据表还是堆表的话–新的记录总是插到表中任何有可用空间的地方。如果空间中没有可用的分页，SQL Server会尝试从已经分配给对象的现有的统一扩展中寻找未分配的分页。如果这样的分页也不存在，SQL Server就必须为数据表分配一个全新的扩展。聚集索引根据新记录的聚集索引键列的值将插入导向到特定的分页。当新的记录是插入语句的直接结果或者是由“删除+插入”策略所执行的更新语句的结果时就会发生插入。SQL Server将新的记录插入到聚集索引中的合适位置，如果当前分页没有可用空间的话，会通过页拆分(page split)在分页中进行接合。如果不将聚集索引 声明为唯一性并插入具有重复键值的记录时，SQL Server会为所有后续的具有相同键值的记录自动生成一个唯一标识符(uniqueifier)。于是，SQL Server在内部将所有的聚集索引键都看作是唯一的。页拆分(Splitting Pages)
SQL Server找到新的可用分页以后，原来的分页就要被拆分：行的一半留在原来的分页上，而另一半则被移到新的分页上。在某些情况下，由于变长字段的长度可能会变得远远大于分页上任何现有的记录，SQL Server会发现即使在页拆分以后仍然没有可用空间来存放新的记录。拆分后，一条或多条记录会被提升到父级分页中。如果只需要一次拆分，那么就提升一条记录。然而，如果在一次拆分后仍然无法容纳新的记录，那么就可能会潜在地产生多个新的分页并产生多次到父级分页的提升。举例来说，比如一个具有32行数据的分页。假设SQL Server尝试插入一条8000个字节长的新记录。SQL Server会对该分页进行一次拆分，但是无法丙容纳一条800个字节长的新记录。即使在第二次拆分后，仍然无法容纳新的记录。最终，SQL Server会意识到如果分页中有任何其他记录存在的话是无法容纳这条新记录的，因此它会分配一个新的分页来容纳这条唯一的新记录。于是，会发生几次页拆分，产生许多新的分页以及父级分页中的许多新的行。SQL Server总是从根节点向下搜索索引树的，因此在插入操作的过程中，分页是在下层中进行的。这意味着当插入操作搜索索引的时候，需要将索引保护起来防止可能的更新操作。这种保护机制使用闩锁。当分页被读取或者被写入磁盘的时候，SQL Server会获取闩锁来保护分页内容的物理完整性。父节点(非叶节点)被闩锁锁住直到得知子节点拥有自己的闩锁来保护时为止。然后，父级的闩锁就可以安全地被释放掉了。在父级节点上的闩锁被释放以前，SQL Server会判断分页是否能够容纳另两行数据。如果不能的话，就进行页拆分。最终目的是确保父级分页总是有足够的空间容纳记录或者由子分页拆分所得到的记录(有时候这会导致无需进行页拆分–至少尚未拆分。这将带来性能上的优化）。拆分的类型取决于被拆分分页的类型：索引的根分页，中间级的索引分页以及数据分页。拆分索引的根分页
如果索引的根分页需要为插入一条新的索引也被拆分的话，SQL Server会为此索引分配两个新的分页。根分页中的所有记录在这两个新分页中被拆分，而新的索引行也被插入到这些分页之中的某个合适的位置。原来的根分页仍然是根，但是现在上面只有两条记录，指向新分配的两个分页。根的页拆分会在索引中创建一个新的级别。由于索引通常只有几层的深度，这种类型的拆分并不会经常发生。拆分中间级索引分页
中间级索引分页的拆分是通过定位分页上索引键的中点，分配新的分页并将旧的索引分页中较低的一半复制到新的分页来实现的。同样地，尽管这比根分页的拆分要常见一些，但这种情况也并不会经常发生。拆分数据分页
数据分页的拆分是最值得关注也是最可能发生的情况，并且这也可能是开发者唯一应该关注的拆分类型。数据分页的拆分只有在表上存在聚集索引且进行插入操作时才会发生。如果不存在聚集索引，SQL Server会根据PFS分页在任何可以容纳新记录的分页上完成插入。尽管只有插入行为才会引起拆分，但这种插入行为也可能是update语句产生的结果，而不仅仅针对insert语句。正如下面将要介绍的，如果行不能在原处被更新或者至少在同一个分页中被更新的话，更新操作实际上就被分解为先删除原始行再插入行的一个新版本。当然，插入新的记录还会造成页拆分。拆分数据分页是一个复杂的操作。与拆分中间级索引分页非常类似的是，SQL Server也是通过定位分页上的索引键的中点，分配新的分页并将旧的索引分页中较低的一半复制到新的分页来实现的。这需要索引管理器决定在哪个分页上放置新的记录并且能够处理原业的旧分页和新分页都不能容纳的大的行记录。当数据分页被拆分时，聚集索引键值不会改变，因而非聚集索引也不会受到影响。下面来看看当发生拆分时分页上的情况。下面的脚本新建了一个具有较大行记录的数据表bigrows—相当大，实际上一个分页上只能容纳五行数据。一旦表被创建并插入五行记录以后，我们可以通过如下步骤找到该表的第一个分页(在本例中也是唯一的分页):将dbcc ind的输出结果插入到sp_table_pages表中，寻找没有前一页的数据分页的信息然后使用dbcc page来查看分页的内容。由于没有必要查看分页上所有的8020个字节的数据，我们只需要查看分页末尾的行偏移数组并观察当插入第六条记录时分页上的情况。下面是从dbcc page输出结果中得到的行偏移数组:下面插入一行新的记录并再次查看行偏移数组:从m_nextPage = (1:20999)结果可知现在有两个数据分页，下一个分页号为20999。在拆分后检查原来的数据分页发现它既有可能包含原始分页的上半部分的行记录也可能是下半部分。SQL Server通常会移动记录因而新插入的记录会移动到新的分页上去。因为这些行记录无论如何都会移动，因此调整它们的位置来容纳新插入的记录就更具有意义了。在这个例子中，新的记录，其聚集键值为22，会被插入到分页的下半部分中去。因此当发生页拆分时，前三行仍然留在原始分页20996中。可以检查页头来找到下一个包含新记录的分页位置。其中,m_nextPage域指明了页号。该值以十进制的格式表示为文件号；分页号对，因此可以很容易地将其运用在dbcc page命令中。当我运行该查询时，得到的m_nextPage的值为1:20999，因此要执行如下命令:下面是插入以后第二个分页上的行偏移数组:注意，在页拆分以后，此分页上有三行记录:原始分页上的后两条记录(键值为20和25)，以及值为22的新记录。如果检查分页上的实际数据的话，会注意到新的记录在槽号(slot)为1的地方，即使该记录本身的确是分页上物理顺序中的最后条记录。槽1(键值为22)从偏移量3326处开始，而槽2(键值为25)则从偏移量1711处开始。行的聚集索免费主机域名引键序是由行的槽号指明的，而不是在分页上的物理位置。如果数据表包含聚集索引，那么在槽1处的记录的键值总是小于槽2处记录的键值而大于槽0处记录的键值。尽管典型的页拆分的代价并不十分高昂，但是用户还是希望能够尽可能降低生产系统中的页拆分频率，至少在使用高峰的时刻会如此。一次页拆分开锁很低，但成千上百的拆分就不是了。可以通过在现有数据上创建聚集索引时使用filefactor子句在分页上预留一些空间来避免高峰时刻可能的系统崩溃。可以在系统运营最不繁忙的时雄姿英发定期使用期望的填充因子重建索引对系统进行优化。按照这种方式，在高峰时刻依然有额外的空间可以使用，并且可以因此节省拆分带来的开锁。如果系统不存在“不活跃”的时间，可以使用alter index对索引进行重组并调整填充因子，而不必使整张表都陷入不可使用的状态。注意：带有reorganize的alter index语句只能通过压缩数据和转移分页来调整填充因子，它不会通过添加新的分页来重置填充因子。利用SQL Server代理可以很轻松地在系统不繁忙时安排索引的重建或重组。删除数据
当从一张数据表中删除记录时，必须同时考虑数据分页和索引分页上的变化。谨记聚集索引的叶级别实际上就是数据本身，而从带有聚集索引的表中删除数据的方式与从非聚集索引 的叶级别删除记录完全一样。从堆表中删除记录则是另一种处理方式，类似于从索引的结点分页中进行删除。从堆表中删除记录
SQL Server 2005不会在行被删除时自动压缩分页上的空间。考虑到性能优化，在分页需要额外的连续空间来插入新的记录以前都不会进行数据压缩。下面的盒子从分页中间删除一行数据然后使用dbcc page来查看分页。下面是dbcc page的输出结果现在删除中间一行数据(where a=3)并再次查看分页的内容下面是第二次执行dbcc page的输出结果注意在堆表中，这行数据就不会出现在分页中了。分页底部的行偏移数组显示第三行数据(槽2)现在位于偏移量0处(这意味着现在没有行使用槽2)，而占用槽3的行仍然在位于和删除前相同的偏移量处。分页上的数据并没有被压缩。除了分页上的空间不被回收之外，堆表中的空闲分页也经常无法被回收。即使从堆表中删除了全部记录。SQL Server不会将空闲分页标记成未分配状态，因此这部分空间无法供其他对象使用。目录视图sys.dm_db_partition_stats会显示这块空间仍然属于堆表。从B树中删除记录
在索引的叶级别(无论聚集索引还是非聚集索引)，当行记录被删除时，SQL Server会将其标记为虚影记录。这意味着这行记录仍然保留在分页上但是SQL Server会修改记录头中的一个比特位用来表示这行数据实际上是一个虚影。页头中也会反映出分页上的虚影记录数量。虚影记录有多种用途。它们可以使用回滚操作具有更高的效率；如果这行记录还没有被物理地移除掉，那么SQL Server要回滚删除操作的话就只需修改表示行为虚影的那个比特位即可。同样，虚影记录也是一种针对键范围锁定以及其他锁定模式的并发优化。虚影记录也被用来支持行版本控制。虚影记录迟早还是会被清除掉的，取决于系统的负荷情况，而且有时SQL Server会在你有机会查看到以前就将其清除掉了。在下面所示的代码中，如果执行delete操作并等待一会儿再运行dbcc page的话，虚影记录可能就真的消失了。这就是为什么要在运行delete以前察看表中的分页数量，这样就可以在查询窗口中一次点击执行delete和dbcc page命令。为了确保虚影不被清除掉，可以将delete放到一个用户事务中并且在检查分页之前不提交或回滚该事务。清理线程不会清除属于活动事务的虚影记录。另外，可以使用未记载的跟踪标记661禁用虚影清理以确保像这个脚本一样运行浿时能够有一致的结果。通常，请记住未被记载的跟踪标记并不能保证在任何后续版本或者service pack中继续工作，并且不被微软支持。还要明确在完成测试以后必须关闭跟踪标记。下面的例子新建了与前面delete例子中相同的数据表，但这次声明了一个主键，这意味着SQL Server会创建一个聚集索引。聚集索引的叶级别就是实际的数据，因此当记录被移除时，会被标记为虚影。下面是dbcc page的输出结果:注意，这行记录仍然会出现在分页中，因为数据表带有聚集索引。行的头信息表明这的确是一条虚影记录。分页末尾的行偏移数组显示槽2中的记录仍然在相同的偏移量处而且所有记录都和删除以前所在的位置相同。另外，页头还提供了分页中虚影记录的数量(m_ghostRecCnt = 1)。可以通过sys.dm_db_index_physical_stats看到数据表中虚影记录的总数删除索引结点级中的记录
当从表中删除一行数据时，SQL Server必须维护所有的非聚集索引，因为每个非聚集索引都有一个指针指向现在消失了的那条记录。索引结点分页中的记录在被删除以后并不是虚影记录，但是与堆表分页一样，直到新的索引行需要该分页中的空间时，SQL Server才会压缩空间。回收分页
当数据分页中的最后一条记录都被删除时，整个分页就被回收。例外发生在表是堆表结构的时候，正如之前讨论过的(如果此分页是表中剩下的唯一分页的话是不会被回收的。一张数据表总是包含至少一个分页，即使该分页是空的）。数据分页的回免费主机域名收将导致SQL Server删除索引分页中指向被回收的数据分页的那行记录。如果索引行被删除的话，那么索引分页就会被回收(同样，可能在删除/插入/更新策略时发生)，只在索引分页中保留一个条目。该条目会被移到相邻的分页中，然后SQL Server将回收这个空闲分页。到目前为止，所有的讨论都集中在删除一行记录所必需的分页操作上。如果一个删除操作删除掉多行记录，用户必须注意一些其他事项。由于在单个查询中修改多条记录对于插入，更新以及删除操作来说都是相同的。更新行
SQL Server有多种方式可以更新行记录，它会自动隐式地为特定的操作选择最快速的更新策略。在决定策略的过程中，SQL Server会估计受到影响的记录数，按照怎样的方式访问记录（通过扫描，或者一次索引和索引获取）以及是否会对索引键进行修改。更新可以由查询处理器或者存储引擎来控制。在本小节中，我们只研究更新究竟按照原位替换的方式进行还是SQL Server将其按照两个独立的操作来处理；删除旧行并插入新行。更新是由哪个组件控制的问题；查询处理器还是存储引擎，实际上与所有数据修改操作都有关联（并不只与更新相关）。移动行
如果表中的一条记录必须移到新的位置时又会怎么样呢？在SQL Server 2005中，当一条带有变长字段的记录被更新了一个新的较大的值，使得原始分页无法再容纳此记录时，会发生移动行的情况。这也可能在改变聚集索引的字段时发生，因为数据行是根据聚集键进行逻辑排序的。举例来说，如果在lastname字段上有一个聚集索引，那么一条lastname值为Abbot的记录会存储到接近表头的位置。如果接着将lastname值更新为Zappa，那么就必须将这行数据移到靠近表尾的地方。前面部分曾经了解了索引的结构并知道非聚集索引的叶级别的每个结点都包含一个行定位符（或者书签）指向数据表中的每行记录。如果表上有聚集索引，那么行定位符就是该行数据的聚集键。因此，当且仅当聚集索引键被更新时，才需要在每个非聚集索引中进行相应的修改。请在决定要在哪些字段上创建聚集索引的时候牢记这点。最好在非易失性的字段上创建聚集索引。如果行的移动是因为原来的分页不能再容纳这行数据。它依然会保持相同的行定位符（换而言之，该行数据的聚集键保持不变），并且没有非聚集索引需要被修改。在索引内部机理的讨论中也了解了如果数据表上没有聚集索引（换而言之，仍然是堆表结构），那么存在非聚集索引中的行定位符实际上就是行的物理位置。在SQL Server 2005中，如果堆中的一行记录要移到新的分页上去，该记录会在原来的位置留下一个“前转指针”(forwarding pointer)。SQL Server无需改变非聚集索引，它们依旧指向原来的位置，并从原位出发指向新的位置。下面来看一个例子。首先新建一张表，与之前演示插入操作的那张表十分相似，但是该表还有第三个变长字段。当向这张表插入五条数据以后，会填满这个分页，然后更新其中一行记录使第三个字段变得相当长。原始分页就无法再容纳这行数据，因此必须被移动。接着可以将dbcc ind的输出结果导入sp_table_pages数据表中，以得到该表所使用的分页数目。查看输出结果:输出结果如下:a=3的记录所在槽2的内容如下:第一个字节的值为4(04)意味着这只是一个前转部分。后三个字节005205是记录移动的目的分页号。由于只是一个十六进制的值，需要将其转化成十进制值20997。下一组四个字节表明分页在文件号为1，槽号为0的地方。如果接着使用dbcc page来查看分页20997，可以看到前转记录的模样。输出结果如下:前转指针
前转指针允许用户在堆中修改数据而无需担心对非聚集索引带来较严重的影响。如果一条已经被前转的记录必须再次移动，最初的前转指针就被更新并指向新的位置。如果还要继续移动，那么前转指针就会不断地指向另一个前转指针。另外，如果前转记录收缩到其原始位置足以能够容纳时，这条记录会被移回到原始位置，如果分页上还有可用空间的话，前转指针就会被删除。SQL Server的后续版本可能会包括一些机制对堆表中的数据进行物理的重组，这将消除前转指针的作用。注意前转指针只存在于堆表中，并且alter table的重组选项对于堆表来说是不起作用的。可以对堆表上的非聚集索引进行碎片整理但是不能对表本身。目前来说，当前转指针创建完以后就永远呆在原地—除了个别例外情况。第一种例外已经提到过，行会收缩并返回其初始位置。第二种例外是数据库收缩时。当文件收缩时，SQL Server实际上会为书签进行再分配。收缩进程不会产生前转指针。对于因为收缩进程而被移除的分页来说，它们包含的任何前转指针或者残余部分实际上都处于“未前转”的状态。前转指针被移除的其他情况十分明显，譬如前转记录被删除或者在表上创建聚集索引从而使其不再是堆表结构。以上是“sql server 2005数据修改的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云技术行业资讯频道！

相关推荐: 怎么使用RMAN

本篇内容介绍了“怎么使用RMAN”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！RMAN的备份脚本可以存储在恢复目录或者操作系统免费主机域名文本文件。1.创建RM…