索引、游标学习过程中遇到的一些摘抄

聚集索引和非聚集索引

SQL索引一步到位http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

SQL SERVER中有多种索引类型。

按存储结构区分：“聚集索引（又称聚类索引，簇集索引）”，“分聚集索引（非聚类索引，非簇集索引）”

按数据唯一性区分：“唯一索引”，“非唯一索引”

按键列个数区分：“单列索引”，“多列索引”。

……

聚集索引是一种对磁盘上实际数据重新组织以按指定的一列或多列值排序。……这里用到微软的平衡二叉树算法，……

SqlServer索引的原理与应用http://www.cnblogs.com/knowledgesea/p/3672099.html

那么怎么理解索引是从大量数据中寻找少量数据呢？下面我们举个例子来说明。

     如果一个数据表的记录平均长度为400字节，则100万条记录需要5万个数据页，其计算公式如下：

　　1000000/（8060/400）=50000

　　如果该数据表建立聚集索引，键值为4个字节长度，而ID的数据长度为13个字节，因此索引结构每条记录为20个字节。

　　4（聚集索引键值）+13（ID键值）+3（管理信息）=20

　　以ID字段所建立的索引，100%填充率，则总分页数约为2482页，其计算方式如下：

　　1000000/(8060/20)

　　即使是使用80%的填充率来计算也只有3106页。其计算方式如下：

　　1000000/((8060*0.8)/20)

　　从上面可以看出如果是第一种情况，则索引页只占到总数据页的5%：

　　2482/50000=0.04964 

　　即使考虑取每页只填充80%的索引数据，第二种情况，索引页也只是占总数据页的6%：

　　3106/50000=0.06212 

　　再说如果查询条件中的字段建立索引，则由于索引键值数据都是以B-Tree有顺序的摆放，所以可采用二分查找找数据。也就是2的N次方大于记录数，就可以找到该条数据。而2的20次方大于100万，因此最多找寻20次就可以找到该条记录。由于比较次数少，数据结构也小，节省访问硬盘与内在的资源，索引将大幅提升找寻数据的效率。SQL SERVER为提高访问与查找对比的效率，用来作索引的数据域键值愈小愈好，也就是要让分页尽量存更多的键值记录。

    ……

    当堆或聚集表具有多个分区时，每个分区都有一个堆或 B 树结构，其中包含该指定分区的行组。例如，如果一个聚集表有 4 个分区，那么将有 4 个 B 树，每个分区一个。

    ……

    ……在索引子叶层级中的每个数据页都有一个指针指向索引分页的前一页与后一页，形成双向链接串行，在内部的系统数据表包含了各索引子叶层第一个分页的地址，为了保证数据在逻辑上是依照聚集索引的顺序存放的，……

    ……

    当非聚集索引从结构中找到符合的记录时，虽然在子叶层该键值是由小到大排序，因此可能在一个分页上就有全部符合查询条件的键值，但因为数据表中数据行的摆放是没有按顺序的（或是说没有按照该非聚集索引的键值顺序摆放），所以真正符合记录的数据是散布在文档各处的，而SQL SERVER每次读取数据都是以数据页为单位，因此，找到一条记录所在位置后，要先将存放该条记录的分页读到内存中，再从该页读出记录。

　　因为BOOKMARK LOOKUP是进行随机的I/O操作，当符合查询的记录很多时，通过非聚集索引访问将导致数据页读取非常频繁，就算两条记录在同一个分页，该分页也会被重复读两次，因此或符合的记录有N条，就需要读取数据表内的分页N页，虽然大部分的读取操作都是针对内存中的高速缓存，但记录数过多时一样没有效率，还不如数据表扫描，全部扫描一遍，把符合条件数据找出来。

　　虽然 SQL 2005 以后的版本中已经不在提 BOOKMARK LOOKUP了(但实际上却是换汤不换药)，我们的很多搜索都是使用如下的搜索过程：先在非聚集中找，然后再在聚集索引中找。

    

SQL Server 查询性能优化——创建索引原则（一）http://www.cnblogs.com/chillsrc/archive/2012/09/19/2694313.html

需要注意的是，在一个表中只允许存在一个聚集索引。

SQL Server创建和使用索引https://www.2cto.com/database/201301/186302.html

聚集索引
    ……
    这个是一种特别的方法，因为在定义主键的时候，会自动添加索引，好在加的是聚集索引还是非聚集索引是我们人为可以控制的。
               通过sp_helpindex 可以查看表中的索引
               execute sp_helpindex @objname = 'Employee';
               go
复合索引
    ……
    可以发现它错了，varbinary是不可以建索引的
覆盖索引（哈哈，没看懂）
唯一索引
筛选索引
    ……
总结：
      BTree 索引有聚集与非聚集之分。就查看上到聚集索引性能比非聚集索引性能要好。
      非聚集索引分覆盖索引，唯一索引，复合索引（当然聚集索引也有复合的，复合二字，只是说明索引，引用了多列），一般非聚集索引就查看上到非聚集索引中覆盖索引的性能比别的非聚集索引性能要好，它的性能和聚集索引差不多，可是它也不是’银弹‘ 它会用更多的磁盘空间。（哈哈，还是没看懂）
    ……

http://www.cnblogs.com/JiangLe/p/4007091.html

语法（嗯，太长不看）
FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ] 
参数
    NEXT 
    紧跟当前行返回结果行，并且当前行递增为返回行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。（简单点讲取游标中下一行数据赋值给变量）
    PRIOR 
    返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。
    FIRST 
    返回游标中的第一行并将其作为当前行。
    LAST 
    返回游标中的最后一行并将其作为当前行。

ABSOLUTE { n | @nvar} 
    如果 n 或 @nvar 为正数，则返回从游标头开始的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为负数，则返回从游标末尾开始的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为 0，则不返回行。n 必须是整数常量，并且 @nvar 的数据类型必须为 smallint、tinyint 或 int。
RELATIVE { n | @nvar} 
    如果 n 或 @nvar 为正数，则返回从当前行开始的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为负数，则返回当前行之前第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为 0，则返回当前行。在对游标完成第一次提取时，如果在将 n 或 @nvar 设置为负数或 0 的情况下指定 FETCH RELATIVE，则不返回行。n 必须是整数常量，@nvar 的数据类型必须为 smallint、tinyint 或 int。
GLOBAL 
    指定 cursor_name 是指全局游标。
cursor_name 
    要从中进行提取的打开的游标的名称。如果同时具有以 cursor_name 作为名称的全局和局部游标存在，则如果指定为 GLOBAL，则 cursor_name 是指全局游标，如果未指定 GLOBAL，则指局部游标。
@cursor_variable_name 
    游标变量名，引用要从中进行提取操作的打开的游标。
INTO @variable_name[,...n] 
    允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果集列的数据类型匹配，或是结果集列数据类型所支持的隐式转换。变量的数目必须与游标选择列表中的列数一致。
备注
如果 SCROLL 选项未在 SQL-92 样式的 DECLARE CURSOR 语句中指定，则 NEXT 是唯一受支持的 FETCH 选项。如果在 SQL-92 样式的 DECLARE CURSOR 语句中指定了 SCROLL 选项，则支持所有 FETCH 选项。


如果使用 Transact-SQL DECLARE 游标扩展插件，则应用下列规则： 
如果指定了 FORWARD_ONLY 或 FAST_FORWARD，则 NEXT 是唯一受支持的 FETCH 选项。
如果未指定 DYNAMIC、FORWARD_ONLY 或 FAST_FORWARD 选项，并且指定了 KEYSET、STATIC 或 SCROLL 中的某一个，则支持所有 FETCH 选项。
DYNAMIC SCROLL 游标支持除 ABSOLUTE 以外的所有 FETCH 选项。
@@FETCH_STATUS 函数报告上一个 FETCH 语句的状态。相同的信息记录在由 sp_describe_cursor 返回的游标中的 fetch_status 列中。这些状态信息应该用于在对由 FETCH 语句返回的数据进行任何操作之前，以确定这些数据的有效性。有关详细信息，请参阅 @@FETCH_STATUS。
权限
FETCH 权限默认授予任何有效的用户。

示例
A. 在简单的游标中使用 FETCH 
以下示例为 Person.Contact 表中姓氏以字母 B 开头的行声明了一个简单的游标，并使用 FETCH NEXT 逐个提取这些行。FETCH 语句以单行结果集形式返回在 DECLARE CURSOR 中指定的列的值。

 复制代码 
USE AdventureWorks
GO
DECLARE contact_cursor CURSOR FOR
SELECT LastName FROM Person.Contact
WHERE LastName LIKE 'B%'
ORDER BY LastName

OPEN contact_cursor

-- Perform the first fetch.
FETCH NEXT FROM contact_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM contact_cursor
END

CLOSE contact_cursor
DEALLOCATE contact_cursor
GO
 
B. 使用 FETCH 将值存入变量 
以下示例与上面的示例相似，但 FETCH 语句的输出存储于局部变量而不是直接返回到客户端。PRINT 语句将变量组合成单一字符串并将其返回到客户端。
 复制代码 
USE AdventureWorks
GO
-- Declare the variables to store the values returned by FETCH.
DECLARE @LastName varchar(50), @FirstName varchar(50)

DECLARE contact_cursor CURSOR FOR
SELECT LastName, FirstName FROM Person.Contact
WHERE LastName LIKE 'B%'
ORDER BY LastName, FirstName

OPEN contact_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM contact_cursor
INTO @LastName, @FirstName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
   PRINT 'Contact Name: ' + @FirstName + ' ' +  @LastName

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM contact_cursor
   INTO @LastName, @FirstName
END
CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

C. 声明 SCROLL 游标并使用其他 FETCH 选项 
以下示例创建一个 SCROLL 游标，使其通过 LAST、PRIOR、RELATIVE 和 ABSOLUTE 选项支持全部滚动功能。
 复制代码 
USE AdventureWorks
GO
-- Execute the SELECT statement alone to show the 
-- full result set that is used by the cursor.
SELECT LastName, FirstName FROM Person.Contact
ORDER BY LastName, FirstName

-- Declare the cursor.
DECLARE contact_cursor SCROLL CURSOR FOR
SELECT LastName, FirstName FROM Person.Contact
ORDER BY LastName, FirstName

OPEN contact_cursor
-- Fetch the last row in the cursor.
FETCH LAST FROM contact_cursor



-- Fetch the row immediately prior to the current row in the cursor.
FETCH PRIOR FROM contact_cursor


-- Fetch the second row in the cursor.
FETCH ABSOLUTE 2 FROM contact_cursor
-- Fetch the row that is three rows after the current row.
FETCH RELATIVE 3 FROM contact_cursor

-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM contact_cursor

CLOSE contact_cursor
DEALLOCATE contact_cursor
GO

请参阅
参考
CLOSE (Transact-SQL)
DEALLOCATE (Transact-SQL)
DECLARE CURSOR (Transact-SQL)
OPEN (Transact-SQL)
其他资源
Transact-SQL 游标

http://bbs.csdn.net/topics/360194933

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

DYNAMIC

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Static 选项
Static选项相当于从tempdb里面完全缓存一个结果集。外部修改数据，并不影响到游标本身(修改游标结果集任意一列都不影响)。使用Static选项的话，不能执行更新游标的 Current of 操作
PS：就是说你在执行这段代码的时候，另外一个窗口即时插入新数据，修改数据，删除数据也不会影响到当前游标

Keyset 选项
Keyset 选项也是从tempdb里面缓存一个结果集，只缓存一个主键。外部修改数据，不能修改主键，修改其它列是有效的。如果基表该行被删除了，@@Fetch_State返回值为-2
PS：就是说你在执行这段代码的时候，另外一个窗口即时插入新数据没有影响。修改非主键数据可以获取到，如果数据不存在就88啦

DYNAMIC 选项
每次获取都即时更新，新增，修改，删除都可以支持。动态游标不支持 ABSOLUTE 提取选项

FAST_FORWARD
指定启用了性能优化的 FORWARD_ONLY、READ_ONLY 游标。如果指定了 SCROLL 或 FOR_UPDATE，则不能也指定 FAST_FORWARD。(嗯，看不懂)

http://www.cnblogs.com/Gin-23333/p/4595515.html