数据库的所有资料、对象和数据库操作日志均存储在这些操作中,默认的数据库文件是.mdf后缀

Server在内部使用该名称引用该文件,第二个是存储日志的文件的位置,数据库的所有资料、对象和数据库操作日志均存储在这些操作中,存储数据库的事务日志信息,SQL Server的逻辑存储结构为文件组(file group)、区(extent)、数据页(data,将数据库映射为一组操作系统文件,创建数据表是创建数据库的一项基本操作,表是包含数据库中所有数据的数据库对象,ON指明事务日志文件的明确定义,2.数据库的名字最长为128个字符

图片 41

四.使用管理界面来创建(推荐)

 

一、数据库存储结构          SQL Server
7.0中的每个数据库有多个操作组成,数据库的所有资料、对象和数据库操作日志均存储在这些操作中。根据这些的作用不同,可以将它们划分为以下三类:
?       
主数据文件:每个数据库有且只有一个主数据文件,它是数据库和其它数据文件的起点。主数据文件的扩展名一般为.mdf; 
     
?       
辅数据文件:用于存储主数据文件中未存储的剩余资料和数据库对象,一个数据库可以没有辅数据文件,但也可以同时拥有多个辅数据文件。辅数据文件的多少主要根据数据库的大小、磁盘存储情况和存储性能要求而设置。辅数据文件的扩展名一般为.ndf;
?       
日志文件:存储数据库的事务日志信息,当数据库损坏时,管理员使用事务日志恢复数据库。日志文件的扩展名一般为.ldf。
每个数据库中至少两个文件:主数据文件和日志文件。
SQL
Server数据库文件除操作系统所赋予的物理文件名称外,还有一个逻辑名称。数据库的逻辑名称应用于Transact-SQL语句中。例如,对于
master系统数据库,master为其逻辑名称,使用Transact-SQL语句操作数据库时,均使用该名称。而对应的物理文件名称为
master.mdf、其日志文件名称为master.ldf。
为了管理方便,可将多个数据库文件组织为一组,称作数据库文件组。文件组能够控制
各个文件的存放位置,其中的每个文件常建立在不同的驱动器上,这样可以减轻每个磁盘驱动器的存储压力,提高数据库的存储效率,从而达到提高系统性能的目
的。SQL Server采用比例填充策略使用文件组中的每个文件提供的存储空间。

区(extent)

extent是给表或索引分配存储空间的单位,也是管理空间的基本单位。
  在SQL Server中,extent的大小是固定的8个连续的数据页,64KB,这意味着
SQL Server 数据库中每 MB 有 16
个区。在创建文件组时,不能指定类似Oracle中的autoallocate或uniform
size子句定义extent的大小,在这方面,SQL Server的灵活性稍差一些。

图片 1

  SQL Server对表的分配extent的方式与Oracle不同。为了使空间分配有效,SQL
Server 不会将所有区分配给包含少量数据的表,所以SQL
Server不会对空表分配extent,extend的分配会延迟到对表添加记录时。
  SQL Server 有两种类型的区:

  • 混合区(mixed
    extent):混合区由多个表或索引共用,最多可由八个对象共享。
    区中八页的每页可由不同的对象所有。
  • 统一区(uniform extent):统一区由由单个对象所有。区中的所有 8
    页只能由一个表或索引专用。

通常对表或索引分配的前8个数据页会在混合区内分配,以后的数据页则在统一区内分配,这种方式与Oracle不同,Oracle的一个区只能分配给一个表或索引,不能多个对象共用,或者也可以说,Oracle只有SQL
Server中的统一区一种类型。

5.2  创建游标

用户在创建和使用游标时,需遵循游标的生命周期。游标的生命周期包含五个阶段:

1.声明游标

为游标指定获取数据时所使用的select语句。声明游标并不会检索任何数据;它只是为游标指定了相应的select语句。并且,在declare后面指定游标的名字的时候,不需要使用@号:

DECLARE CursorName CURSOR CursorOptions

FOR Select Statement

2.打开游标

检索数据并填充游标:

OPEN CursorName

3.fetch操作

fetch操作会使游标移动到下一条记录,并将游标返回的每个列的数据分别赋值给本地变量,这些本地变量必须预先予以声明。

FETCH CursorName INTO @Variable1, @Variable2

也可以使用fetch命令移动到结果中一个绝对位置,或者,从当前位置向前或者向后移动n行。但是,我建议最好不要使用游标完成如此之多的工作。

通常在批处理中,会使用while循环来反复从游标中获取记录行,直到游标不再返回任何行为止。对于这样一个针对游标的循环,应当在其循环条件中检查@@Fetch_Status全局变量以确定是否还能够从游标中获取行:

WHILE @@Fetch_Status = 0

4.关闭游标

关闭游标,释放数据,但保留select语句。游标关闭以后,还可以使用open命令再次打开它。(Close命令是与open命令相对的)。

Close CursorName

5.释放游标

释放相关的内存,并删除游标的定义。(Deallocate命令是与declare命令相对的)。

DEALLOCATE CursorName

通常,用户使用DECLARE语句声明一个游标,声明一个游标主要包括以下内容:

l 游标名字

l 数据来源(表和列)

l 选取条件

l 属性(仅读或可修改)

在SQL Server中,DECLARE CURSOR语句同时支持SQL-92和Transact-SQL两种标准。

(1)SQL-92语法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,…n ] ] } ]

(2)Transact-SQL 扩展语法

DECLARE cursor_name CURSOR

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

 

其中SQL-92语法的参数如下:

cursor_name,是所定义的 Transact-SQL
服务器游标名称。cursor_name 必须遵从标识符规则。

l INSENSITIVE,定义一个游标,以创建将由该游标使用的数据的临时复本。对游标的所有请求都从
tempdb
中的该临时表中得到应答;因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改。使用
SQL-92 语法时,如果省略
INSENSITIVE,(任何用户)对基表提交的删除和更新都反映在后面的提取中。

l SCROLL,指定所有的提取选项(FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE)均可用。如果在
SQL-92 DECLARE CURSOR 中未指定 SCROLL,则 NEXT
是唯一支持的提取选项。如果指定 SCROLL,则不能也指定 FAST_FORWARD。

select_statement,是定义游标结果集的标准 SELECT 语句。在游标声明的
select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE
和 INTO。如果
select_statement 中的子句与所请求的游标类型的功能发生冲突,则
Microsoft SQL Server 隐性地将游标转换为另一种类型。

l READ ONLY,防止游标被更新。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF
子句中不能引用游标。该选项替代要更新的游标的默认功能。

l UPDATE [OF
column_name [,n]],定义游标内可更新的列。如果指定 OF
column_name [,n] 参数,则只允许修改所列出的列。如果在
UPDATE 中未指定列的列表,则可以更新所有列。

Transact-SQL 扩展参数如下:

cursor_name,是所定义的 Transact-SQL
服务器游标名称。cursor_name 必须遵从标识符规则。

l LOCAL,指定该游标的作用域对在其中创建它的批处理、存储过程或触发器是局部的。该游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程
OUTPUT 参数中,该游标可由局部游标变量引用。OUTPUT
参数用于将局部游标传递回调用批处理、存储过程或触发器,它们可在存储过程终止后给游标变量指派参数使其引用游标。除非
OUTPUT
参数将游标传递回来,否则游标将在批处理、存储过程或触发器终止时隐性释放。如果
OUTPUT
参数将游标传递回来,游标在最后引用它的变量释放或离开作用域时释放。

l GLOBAL,指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中,都可以引用该游标名称。该游标仅在脱接时隐性释放。

l FORWARD_ONLY,指定游标只能从第一行滚动到最后一行。FETCH NEXT
是唯一受支持的提取选项。如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET
和 DYNAMIC 关键字,则游标作为 DYNAMIC 游标进行操作。如果 FORWARD_ONLY
和 SCROLL 均未指定,除非指定 STATIC、KEYSET 或 DYNAMIC
关键字,否则默认为 FORWARD_ONLY。STATIC、KEYSET 和 DYNAMIC 游标默认为
SCROLL。与 ODBC 和 ADO这类数据库 API 不同,STATIC、KEYSET 和 DYNAMIC
Transact-SQL 游标支持 FORWARD_ONLY。FAST_FORWARD 和 FORWARD_ONLY
是互斥的;如果指定一个,则不能指定另一个。

l STATIC,定义一个游标,以创建将由该游标使用的数据的临时复本。对游标的所有请求都从
tempdb
中的该临时表中得到应答;因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改。

l KEYSET,指定当游标打开时,游标中行的成员资格和顺序已经固定。对行进行唯一标识的键集内置在
tempdb 内一个称为 keyset
的表中。对基表中的非键值所做的更改(由游标所有者更改或由其它用户提交)在用户滚动游标时是可视的。其他用户进行的插入是不可视的(不能通过
Transact-SQL
服务器游标进行插入)。如果某行已删除,则对该行的提取操作将返回
@@FETCH_STATUS 值
-2。从游标外更新键值类似于删除旧行后接着插入新行的操作。含有新值的行不可视,对含有旧值的行的提取操作将返回
@@FETCH_STATUS 值 -2。如果通过指定 WHERE CURRENT OF
子句用游标完成更新,则新值可视。

l DYNAMIC,定义一个游标,以反映在滚动游标时对结果集内的行所做的所有数据更改。行的数据值、顺序和成员在每次提取时都会更改。动态游标不支持
ABSOLUTE 提取选项。

l FAST_FORWARD,指定启用了性能优化的 FORWARD_ONLY、READ_ONLY
游标。如果指定 FAST_FORWARD,则不能也指定 SCROLL 或
FOR_UPDATE。FAST_FORWARD 和 FORWARD_ONLY
是互斥的;如果指定一个,则不能指定另一个。

l READ_ONLY,禁止通过该游标进行更新。在 UPDATE 或 DELETE 语句的 WHERE
CURRENT OF 子句中不能引用游标。该选项替代要更新的游标的默认功能。

l SCROLL_LOCKS,指定确保通过游标完成的定位更新或定位删除可以成功。当将行读入游标以确保它们可用于以后的修改时,Microsoft  SQL
Server会锁定这些行。如果还指定了 FAST_FORWARD,则不能指定
SCROLL_LOCKS。

l OPTIMISTIC,指定如果行自从被读入游标以来已得到更新,则通过游标进行的定位更新或定位删除不成功。当将行读入游标时
SQL Server 不锁定行。相反,SQL Server 使用 timestamp
列值的比较,或者如果表没有
timestamp 列则使用校验值,以确定将行读入游标后是否已修改该行。如果已修改该行,尝试进行的定位更新或定位删除将失败。如果还指定了
FAST_FORWARD,则不能指定 OPTIMISTIC。

l TYPE_WARNING,指定如果游标从所请求的类型隐性转换为另一种类型,则给客户端发送警告消息。

select_statement,是定义游标结果集的标准 SELECT 语句。在游标声明的
select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE
和 INTO。

l UPDATE [OF
column_name [,n]],定义游标内可更新的列。如果提供了 OF
column_name [,n],则只允许修改列出的列。如果在 UPDATE
中未指定列的列表,除非指定了 READ_ONLY 并发选项,否则所有列均可更新。

下面我们给出一个具体完整的例子,显示游标的使用方法,读者需要注意DECLARE、OPEN、FETCH、CLOSE和DEALLOCATE语句的使用,分别表示游标使用的整个过程。

USE [EAMS]

GO

/*声明并打开一个全局游标,在批处理以外该游标依然可见*/

declare emp_cur cursor global scroll for

SELECT * FROM mrBaseInfo

Open emp_cur

GO

/*用游标变量引用已声明的游标*/

declare @cur_tal cursor

set @cur_tal = emp_cur

 

/*现在释放对游标的引用*/

deallocate @cur_tal

 

/*游标emp_cur 依旧存在*/

fetch next from emp_cur

go

 

/*再引用游标*/

declare @cur_ta2 cursor

set !cur_ta2 = emp_cur

 

/*释放emp_cur游标*/

deallocate emp_cur

 

/*由于游标被@cur_ta2引用,所以依旧存在*/

fetch next from @cur_ta2

 

/*当最有一个游标变量超出游标作用域时,游标将被释放*/

 

go

declare @cur_ta cursor

set @cur_ta = cursor local scroll for

select * from mrBaseInf

 

/*由于没有其他变量对其进行引用,所以游标被释放*/

deallocate @cur_ta

go

 

                              2016年1月6日22:13:36

5.SIZE

  数据库大小,如果没写,默认与用图形创建的大小相同。

用途:个人收藏学习

页(data page)

SQL Server 中数据存储的基本单位是页。 为数据库中的数据文件(.mdf 或
.ndf)分配的磁盘空间可以从逻辑上划分成页(从 0 到 n 连续编号)。 磁盘
I/O 操作在页级执行。 也就是说,页也是也是读写数据的单位。
  页是区段的分配单元。每一个区段包含8个页,每个页的大小固定为8KB,不能修改,这与Oracle数据库在创建表空间时可以指定数据库大小不同。

图片 2

  上图展示了数据是如何存放在页中的。对于插入的每一行,为了表明特定行的数据开始于页中的何处,每一页的末尾都用一小块空间记录的每一行相对于页头位置的偏移量。
  SQL Server 数据文件中的页按顺序编号,文件的首页以 0
开始。数据库中的每个文件都有一个唯一的文件 ID
号。若要唯一标识数据库中的页,需要同时使用文件 ID 和页码。

3.3  使用视图修改数据

建立视图不但可以用于查询数据库中的数据,也可以通过视图修改视图基表的数据,其行为类似于修改表中数据。为此,可以使用UPDATE、INSERT和DELETE语句修改数据,或者使用bcp工具和BULK
INSERT语句。但是,在更新视图的时候,有一些限制:

l 任何修改(包括UPDATE、INSERT、DELETE语句)只能引用来自一个基表的列。

l 视图中被修改的列必须直接引用表列的数据,不能从其他方式派生而来,如通过AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR、VARP聚合函数,或者通过使用集合操作符(UNION,
UNION ALL, CROSSJOIN, EXCEPT和INTERSECT)计算得到。

l 被修改的列不能受GROUP BY、HAVING或者DISTINCT子句的影响。

如果视图是可更新的,就可以使用INSERT向视图基表插入数据,使用UPDATE修改视图基表的数据,使用DELETE语句删除视图基表的数据。下面我们分别给出INSERT、UPDATE和DELETE语句的语法和一些示例。

(1)INSERT语句

INSERT语句的语法如下:

[ WITH <common_table_expression> [ ,…n ] ]

INSERT

    [ TOP ( expression ) [ PERCENT ] ]

    [ INTO]

    {

    <object> | rowset_function_limited

      [ WITH ( <Table_Hint_Limited> [ …n ] ) ]

    }

{

    [ ( column_list ) ]

    [ <OUTPUT Clause> ]

    { VALUES ( { DEFAULT | NULL | expression } [ ,…n ] )

    | derived_table

    | execute_statement

    }

}

    | DEFAULT VALUES

[; ]

 

<object> ::=

{

    [ server_name . database_name . schema_name .

      | database_name .[ schema_name ] .

      | schema_name .

    ]

    table_or_view_name

}

其中:

l WITH
<common_table_expression>,定义临时命名结果集,也称为公共表表达式,定义在INSERT语句的范围内,结果集是执行SELECT语句得到的。

l TOP (*expression*) [ PERCENT
],定义将要删除的随机行的数量或者百分比,expression要么是数量,要么是百分比。

l INTO,一个可选的关键字,可以将它用在 INSERT 和目标表之间。

server_name,是表或者视图所在的服务器名。

database_name,数据库的名称。

schema_name,视图或者表所在的模式名。

table_or view_name,将要接收数据的表或视图的名称。

rowset_function_limited,是 OPENQUERY 或 OPENROWSET 函数。

l WITH ( <table_hint_limited> [… n ]
),指定目标表所允许的一个或多个表提示。需要有 WITH
关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。

( column_list ),要在其中插入数据的一列或多列的列表。必须用圆括号将
column_list 括起来,并且用逗号进行分隔。如果某列不在
column_list 中,则 SQL
Server必须能够基于该列的定义提供一个值;否则不能装载行。如果列满足下面的条件,那么
SQL Server 将自动为列提供值:1)具有 IDENTITY
属性。使用下一个增量标识值。2)有默认值。使用列的默认值。3)具有
timestamp
数据类型。使用当前的时间戳值。4)是可空的。使用空值。当向标识列中插入显式的值时,必须使用
column_list 及 VALUES 列表,并且对于表,SET IDENTITY_INSERT
选项必须是 ON。

l OUTPUT
Clause,返回插入的行作为INSERT操作的一部分。OUTPUT子句在任何DML语句模板视图或者远程表中不支持。

l VALUES,引入要插入的数据值的列表。对于
column_list(如果已指定)中或者表中的每个列,都必须有一个数据值。必须用圆括号将值列表括起来。如果
VALUES
列表中的值与表中列的顺序不相同,或者未包含表中所有列的值,那么必须使用
column_list 明确地指定存储每个传入值的列。

l DEFAULT,强制 SQL Server
装载为列定义的默认值。如果对于某列并不存在默认值,并且该列允许
NULL,那么就插入 NULL。对于使用
timestamp* *数据类型定义的列,插入下一个时间戳值。DEFAULT
对标识列无效。

Expression,一个常量、变量或表达式。表达式不能包含 SELECT 或 EXECUTE
语句。

derived_table,任何有效的 SELECT 语句,它返回将装载到表中的数据行。

execute_statement,任何有效的 SELECT
语句,它返回将装载到表中的数据行。如果将 execute_statement 与 INSERT
一起使用,那么每个结果集都必须与表中或 column_list
中的列兼容。execute_statement 可以用来执行同一服务器或远程服务器上的存储过程。执行远程服务器中的过程,然后将结果集返回到本地服务器,并装载到本地服务器的表中。如果
execute_statement 使用 READTEXT 语句返回数据,那么每个单独的 READTEXT
语句最多可以返回 1 MB (1024 KB) 的数据,也可以将
execute_statement 与扩展过程一起使用,并且插入扩展过程的主线程返回的数据。不会插入不是主线程的其它线程中的输出结果。

l DEFAULT VALUES,强制新行包含为每个列所定义的默认值。

下面提供了一个示例,定义了一个视图,然后使用视图将数据插入到视图的基表中。

USE AdventureWorks;

GO

IF OBJECT_ID (‘dbo.T1’, ‘U’) IS NOT NULL

    DROP TABLE dbo.T1;

GO

IF OBJECT_ID (‘dbo.V1’, ‘V’) IS NOT NULL

    DROP VIEW dbo.V1;

GO

 

CREATE TABLE T1 ( column_1 int, column_2 varchar(30));

GO

CREATE VIEW V1 AS

SELECT column_2, column_1

FROM T1;

GO

INSERT INTO V1

    VALUES (‘Row 1’,1);

GO

SELECT *

FROM T1;

GO

SELECT *

FROM V1;

GO

(2)UPDATE语句

UPDATE语句的语法如下:

[ WITH <common_table_expression> […n] ]

UPDATE

    [ TOP ( expression ) [ PERCENT ] ]

    { <object> | rowset_function_limited

     [ WITH ( <Table_Hint_Limited> [ …n ] ) ]

    }

    SET

        { column_name = { expression | DEFAULT | NULL }

          | { udt_column_name.{ { property_name = expression

                                | field_name = expression }

                               | method_name ( argument [ ,…n ] )

                              }

            }

          | column_name { .WRITE ( expression , @Offset , @Length ) }

          | @variable = expression

          | @variable = column = expression } [ ,…n ]

        } [ ,…n ]

    [ <OUTPUT Clause> ]

    [ FROM{ <table_source> } [ ,…n ] ]

    [ WHERE { <search_condition>

            | { [ CURRENT OF

                  { { [ GLOBAL ] cursor_name }

                      | cursor_variable_name

                  }

                ]

              }

            }

    ]

    [ OPTION ( <query_hint> [ ,…n ] ) ]

[ ; ]

 

<object> ::=

{

    [ server_name . database_name . schema_name .

    | database_name .[ schema_name ] .

    | schema_name .

    ]

    table_or_view_name

}

其中:

l WITH
<common_table_expression>,定义临时命名结果集,也称为公共表表达式,定义在INSERT语句的范围内,结果集是执行SELECT语句得到的。

l TOP (*expression*) [ PERCENT
],定义将要删除的随机行的数量或者百分比,expression要么是数量,要么是百分比。

server_name,是表或者视图所在的服务器名。

database_name,数据库的名称。

schema_name,视图或者表所在的模式名。

table_or view_name,将要接收数据的表或视图的名称。

rowset_function_limited,是 OPENQUERY 或 OPENROWSET 函数。

l WITH ( <table_hint_limited> [… n ]
),指定目标表所允许的一个或多个表提示。需要有 WITH
关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。

l SET,指定要更新的列或变量名称的列表。

column_name,含有要更改数据的列的名称。column_name 必须驻留于
UPDATE
子句中所指定的表或视图中。标识列不能进行更新。如果指定了限定的列名称,限定符必须同
UPDATE 子句中的表或视图的名称相匹配。

expression,变量、字面值、表达式或加上括弧的返回单个值的 subSELECT
语句。expression 返回的值将替换 column_name 或
@*variable* 中的现有值。

l DEFAULT,指定使用对列定义的默认值替换列中的现有值。如果该列没有默认值并且定义为允许空值,这也可用来将列更改为
NULL。

udt_column_name,已声明的变量,该变量将设置为
expression 所返回的值。SET @*variable = column =expression 将变量设置为与列相同的值。这与 SET @variable =column, column = expression* 不同,后者将变量设置为列更新前的值。

property_name | field_name,是用户自定义类型的公共属性或者公共数据成员。

method_name ( argument [ ,… n]
),是带一个或者多个参数的udt_column_name的非静态公共方法。

.WRITE
(*expression,@Offset,@Length*),定义需要修改的column_name的值。

@ variable,是用于存储表达式返回值的变量。

l <OUTPUT_Clause>,返回更新的数据或者表达式。

l FROM <table_source>,指定用表来为更新操作提供准则。

l WHERE,指定条件来限定所更新的行。

l <search_condition>,指定联接所基于的条件。

l CURRENT OF,指定更新在指定游标的当前位置进行。

l GLOBAL,指定 cursor_name 指的是全局游标。

cursor_name,要从中进行提取的开放游标的名称。如果同时存在名为
cursor_name 的全局游标和局部游标,则在指定了 GLOBAL
时,cursor_name 指的是全局游标。如果未指定 GLOBAL,则
cursor_name 指局部游标。游标必须允许更新。

cursor_variable_name,游标变量的名称。cursor_variable_name
必须引用允许更新的游标。

l OPTION ( <query_hint> [ ,n ]
),指定优化程序提示用于自定义 SQL Server 的语句处理。

(3)DELETE语句

DELETE语句的语法如下:

[ WITH <common_table_expression> [ ,…n ] ]

DELETE

    [ TOP ( expression ) [ PERCENT ] ]

    [ FROM ]

    { <object> | rowset_function_limited

      [ WITH ( <Table Hint> [ …n ] ) ]

    }

    [ <OUTPUT Clause> ]

    [ FROM <table_source> [ ,…n ] ]

    [ WHERE { <search_condition>

            | { [ CURRENT OF

                   { { [ GLOBAL ] cursor_name }

                       | cursor_variable_name

                   }

                ]

              }

          }

    ]

    [ OPTION ( <Query Hint> [ ,…n ] ) ]

[; ]

 

<object> ::=

{

    [ server_name.database_name.schema_name.

      | database_name. [ schema_name ] .

      | schema_name.

    ]

    table_or_view_name

}

其中:

l WITH
<common_table_expression>,定义临时命名结果集,也称为公共表表达式,定义在INSERT语句的范围内,结果集是执行SELECT语句得到的。

l TOP (*expression*) [ PERCENT
],定义将要删除的随机行的数量或者百分比,expression要么是数量,要么是百分比。

l FROM,是一个可选的关键字,用于在DELETE关键字和target
table_or_view_name或者rowset_function_limited之间。

server_name,是表或者视图所在的服务器名。

database_name,数据库的名称。

schema_name,视图或者表所在的模式名。

table_or view_name,将要接收数据的表或视图的名称。

rowset_function_limited,是 OPENQUERY 或 OPENROWSET 函数。

l WITH ( <table_hint_limited> [… n ]
),指定目标表所允许的一个或多个表提示。需要有 WITH
关键字和圆括号。不允许有 READPAST、NOLOCK 和 READUNCOMMITTED。

l OUTPUT_Clause,返回删除的行或者表达式。

l FROM <table_source>,指定附加的 FROM 子句。这个对 DELETE 的
Transact-SQL 扩展使您得以从 <table_sources> 指定数据,并从第一个
FROM 子句内的表中删除相应的行。

l WHERE,指定用于限制删除行数的条件。如果没有提供 WHERE 子句,则 DELETE
删除表中的所有行。基于 WHERE 子句中所指定的条件,有两种形式的删除操作。

l <search_condition>,指定删除行的限定条件。对搜索条件中可以包含的谓词数量没有限制。

l CURRENT OF,指定在指定游标的当前位置完成 DELETE。

l GLOBAL,定义cursor_name引用一个全局游标。

cursor_name,是从其中进行提取的打开游标的名称。当全局和局部游标都以
cursor_name 作为它们的名称存在时,如果指定 GLOBAL,则
cursor_name 引用全局游标,如果未指定 GLOBAL,则
cursor_name 引用局部游标。游标必须允许更新。

cursor_variable_name,是游标变量的名称。游标变量必须引用允许更新的游标。

l OPTION ( <query_hint> [ ,n]
),是表示使用优化程序提示自定义 SQL Server 的语句处理的关键字。

4.LOG
ON指明事务日志文件的明确定义。

9.COLLATE

  该选项处理排序,字母大小,以及是否对重音敏感的问题。

 

 

图片 3图片 4

 

图片 5CREATE TABLE TB_constraint
图片 6(
图片 7        name        char(20)        not null,
图片 8        country        varchar(30)
图片 9                CONSTRAINT        DF_country        DEFAULT ‘China’
图片 10)
图片 11GO
图片 12ALTER TABLE TB_countraint ADD CONSTRANT DF_name  DEFAULT ‘UNKNOWN’ FOR name
图片 13

SQL Server的逻辑存储结构为文件组(file group)、区(extent)、数据页(data
page)。
  SQL Server
将数据库映射为一组操作系统文件。数据和日志信息绝不混合在同一个文件中,而且一个文件只由一个数据库使用。文件组是文件的命名集合,用于简化数据存放和管理任务(例如,备份和还原操作)。

3.4  修改视图

视图创建完成之后,可以支持应用程序的开发。但是,应用往往是经常发生变化的,因此也要求视图也发生变化,才能适合特定需求。SQL
Server提供了修改视图的功能,完成对视图的修改操作。

通常情况下,完成对视图的修改可以采用两种方式:第一种是删除原有的视图,然后重新创建所需要的视图,另一种方式是使用ALTER
VIEW语句。其语法格式如下:

ALTER VIEW [ database_name . [ schema_name ] . | schema_name . ] view_name [ ( column [ ,…n ] ) ]

[ WITH < view_attribute > [ ,…n ] ]

AS

     select_statement

[ WITH CHECK OPTION ]

 

< view_attribute > ::=

{

    [ ENCRYPTION ]

    [ SCHEMABINDING ]

    [ VIEW_METADATA ]     

}

 

其中:

database_name,视图所在的数据库。

schema_name,视图所属于的模式名。

view_name,是要更改的视图

column,是一列或多列的名称,用逗号分开,将成为给定视图的一部分。

n,是表示 column 可重复 n 次的占位符。

l ENCRYPTION,加密 syscomments 表中包含 ALTER VIEW 语句文本的条目。使用
WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布。

l SCHEMABINDING,将视图绑定到架构上。指定 SCHEMABINDING
时,select_statement 必须包含由所引用的表、视图或用户定义函数组成的两部分名称
(owner.object)。不能除去参与到用架构绑定子句创建的视图中的视图或表,除非该视图已被除去或更改而不再具有架构绑定。否则,SQL
Server 会产生错误。另外,对于参与具有架构绑定的视图的表,如果其上的
ALTER TABLE 语句影响了该视图的定义,则这些语句将会失败。

l VIEW_METADATA,在为引用视图的查询请求浏览模式元数据时,指定 SQL
Server 将向 DBLIB、ODBC 和 OLE DB API
返回有关视图的元数据信息,而不是返回基表或表。浏览模式元数据是由 SQL
Server 向客户端 DB-LIB、ODBC 和 OLE DB API
返回的附加元数据,它允许客户端 API
实现可更新的客户端游标。浏览模式元数据包含有关结果集内的列所属基表的信息。对于用
VIEW_METADATA
选项创建的视图,当在结果集中描述视图内的列时,浏览模式元数据返回相对于基表名的视图名。当用
VIEW_METADATA 创建视图时,如果该视图具有 INSERT 或 UPDATE INSTEAD OF
触发器,则视图的所有列(timestamp 除外)都是可更新的。

l AS,是视图要执行的操作。

select_statement,是定义视图的 SELECT 语句。

l WITH CHECK
OPTION,强制视图上执行的所有数据修改语句都必须符合由定义视图的
select_statement 设置的准则。

      欢迎批评指正^_^

一.CREATE语句(创建)

2.        簇索引
在簇索引中,行的物理存储顺序与索引顺序完全相同,每个表只允许建立一个簇索引。由于在建立簇索引时要改变表中资料行的物理顺序,所以应在其它非簇索引建立之前建立簇索引,以免引起SQL
Server重新构造非簇索引。
默认情况下,SQL Server为PRIMARY
KEY约束所建立的索引为簇索引,但这一默认设置可以使用NONCLUSTERED关键词改变。在CREATE
INDEX语句中,使用CLUSTERED选项建立簇索引。
3.        非簇索引
非簇索引不改变行的物理存储顺序。在非簇索引的叶级页面包含一个索引值和一个指针,指针指向资料页中的资料行,该行具有与索引键值相同的列值。在CREATE
INDEX语句中,使用NONCLUSTERED选项建立非簇索引。
注意:一个表最多可以建立249个非簇索引,其中包括使用CREATE
INDEX语句显式建立的非簇索引,以及各种约束所建立的非簇索引。
4.        复合索引
复合索引是对一个表中的两列或多列的组合进行索引,复合索引的最大列数为16,且这些列必须位于同一个表中。复合索引值的最大长度为900字节,即复合索引列的定义长度之和不能超过900字节。在使用复合索引时,把被索引列(两列或多列)作为一个单位。
注意:复合索引中的列顺序可以与表中的列顺序不同,在复合索引中应首先定义最可能具有唯一性的列。
在SQL Server中,有两种方法建立索引:第一,调用CREATE
TABLE语句创建表或执行ALTER TABLE语句修改表时,建立PRIMARY
KEY约束和UNIQUE约束,使SQL
Server自动为这些约束建立索引;第二,使用CREATE
INDEX语句对一个已存在的表建立索引。
CREATE INDEX语句的语法格式为:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
        ON table ( column [,…n] )
        [ WITH
                [ PAD_INDEX ]
                [ [,] FILLFACTOR = fillfactor ]
                [ [,] IGNORE_DUP_KEY ]
                [ [,] DROP_EXISTING ]
                [ [,] STATISTICS_NORECOMPUTE ]
   ]
   [ ON filegroup ]

数据库文件

SQL Server 数据库具有三种类型的文件:

  • 主数据文件
    主数据文件是数据库的起点。除了存储系统以及用户数据以外,主数据文件还存储了数据库中的所有辅助数据文件以及重做日志文件的路径、名称、大小等信息。SQL
    Server通过读取主数据文件得到其他数据文件及重做日志文件的信息,这个功能与Oracle控制文件相似。每个数据库都有一个主数据文件。主数据文件的推荐文件扩展名是
    .mdf。
  • 次要数据文件
    除主数据文件以外的所有其他数据文件都是次要数据文件,次数据文件一般只存储用户数据。某些数据库可能不含有任何次要数据文件,而有些数据库则含有多个次要数据文件。次要数据文件的推荐文件扩展名是
    .ndf。
  • 日志文件
    日志文件包含着用于恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件,当然也可以有多个。日志文件的推荐文件扩展名是
    .ldf。

SQL Server 不强制使用 .mdf、.ndf 和 .ldf
文件扩展名,但使用它们有助于标识文件的各种类型和用途。
  在 SQL Server 中,数据库中所有文件的位置都记录在数据库的主文件和
master 数据库中。大多数情况下,SQL Server 数据库引擎使用 master
数据库中的文件位置信息。但是,在下列情况下,数据库引擎使用主文件的文件位置信息初始化
master 数据库中的文件位置项:

  • 使用带有 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 选项的 CREATE
    DATABASE 语句来附加数据库时。
  • 从 SQL Server 2000 版或 7.0 版升级时。
  • 还原 master 数据库时。

4.4  查看存储过程

SQL
Server提供了几个系统存储过程,可以用于获取存储过程的相关信息,使用这些存储过程,可以:

l 查看用于创建存储过程的 Transact-SQL 语句。这对于没有用于创建存储过程的
Transact-SQL 脚本文件的用户是很有用的。

l 获得有关存储过程的信息(如存储过程的所有者、创建时间及其参数)。

l 列出指定存储过程所使用的对象及使用指定存储过程的过程。此信息可用来识别那些受数据库中某个对象的更改或删除影响的过程。

当需要查看存储过程定义的时候,可以查询master系统数据库的sys.sql_modules视图。当需要查看存储过程相关信息时,可以查询master系统数据库的sys.procedures视图。当需要查看存储过程依赖关系时,可以查询master系统数据库的sys.sql_dependencies视图。当需要查看扩展存储过程定义的相关信息时,可以执行Transact-SQL语句sp_helpextendedproc,其语法规范如下:

sp_helpextendedproc [ [@funcname = ] ‘procedure’ ]

其中:

l [ @funcname =]
‘procedure’,表示对象的名称,将显示该对象的定义信息。对象必须在当前数据库中。

name,的数据类型为 nvarchar(776),没有默认值。

12.生成路径必须存在,不然报错(

1.创建DataBase

图片 14

(1)        DEFAULT约束
 
   使用默认值(DEFAULT)约束后,如果资料在插入新行时没有显示为列提供资料,系统将默认值赋给该列。默认值约束所提供的默认值可以为常量、函数、系统零进函数、空值(NULL)等。
         默认值约束的定义格式为:
         [ CONSTRAINT constraint_name ] DEFAULT
constraint_expression
 
   其中,constraint_name参数指出所建立的默认值约束名称。constraint_expression表达式为列提供默认值。
在使用DEFAULT约束时,还应注意以下两点:
?        每列只能有一个DEFAULT约束;
?        约束表达式不能参照表中的其它列和其它表、视图或存储过程。
     
   例如,先创建TB_constraint表,并使用默认值约束为country列设置默认值。之后,再执行ALTER
TABLE语句为TB_constraint表的name列添加一个默认值约束:

管理SQL Server文件组及文件组

ALTER DATABASE database_name   
{  
    <add_or_modify_files>  
  | <add_or_modify_filegroups>  
}  
[;]  

<add_or_modify_files>::=  
{  
    ADD FILE <filespec> [ ,...n ]   
        [ TO FILEGROUP { filegroup_name } ]  
  | ADD LOG FILE <filespec> [ ,...n ]   
  | REMOVE FILE logical_file_name   
  | MODIFY FILE <filespec>  
}  

<filespec>::=   
(  
  NAME = logical_file_name
  [ , NEWNAME = new_logical_name ]
  [ , FILENAME ={'os_file_name'|'filestream_path'|'memory_optimized_data_path'}]
  [ , SIZE = size [ KB | MB | GB | TB ] ]
  [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
  [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
  [ , OFFLINE ]
)

<add_or_modify_filegroups>::=  
{  
    | ADD FILEGROUP filegroup_name   
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]  
    | REMOVE FILEGROUP filegroup_name   
    | MODIFY FILEGROUP filegroup_name  
        { <filegroup_updatability_option>  
        | DEFAULT  
        | NAME = new_filegroup_name   
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }  
        }  
}  
<filegroup_updatability_option>::=  
{  
    { READONLY | READWRITE } | { READ_ONLY | READ_WRITE }  
}  
  • <add_or_modify_files>::=</add_or_modify_files>:指定要添加、删除或修改的文件。
  • database_name:要修改的数据库的名称。
  • ADD FILE:向数据库中添加文件。
  • TO FILEGROUP { filegroup_name }:指定要将指定文件添加到的文件组。
  • ADD LOG FILE:将要添加的日志文件添加到指定的数据库。
  • REMOVE FILE logical_file_name:从 SQL Server
    的实例中删除逻辑文件说明并删除物理文件。
    除非文件为空,否则无法删除文件。
  • logical_file_name:在 SQL Server 中引用文件时所用的逻辑名称。
  • MODIFY FILE:指定应修改的文件。 如果指定了
    SIZE,那么新大小必须比文件当前大小要大。
    若要修改数据文件或日志文件的逻辑名称,请在 NAME
    子句中指定要重命名的逻辑文件名称,并在 NEWNAME
    子句中指定文件的新逻辑名称。 例如:

MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 

若要将数据文件或日志文件移至新位置,请在 NAME
子句中指定当前的逻辑文件名称,并在 FILENAME
子句中指定新路径和操作系统(物理)文件名称。 例如:

MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ')
  • { ‘os_file_name’ | ‘filestream_path’ |
    ‘memory_optimized_data_path’}
  • os_file_name:对于标准 (ROWS)
    文件组,这是在创建文件时操作系统所使用的路径和文件名。
  • ‘ filestream_path ‘:对于 FILESTREAM 文件组,FILENAME 指向将存储
    FILESTREAM 数据的路径。
  • memory_optimized_data_path:对于内存优化文件组,FILENAME
    会引用将存储内存优化数据的路径。SIZE、MAXSIZE 和 FILEGROWTH
    属性不适用于内存优化文件组。
  • FILEGROWTH:用于指定每次文件增长大小,如果未指定确定的值,则默认为1MB,如果指定为0,则数据文件不能自动增长。可以使用MB、KB、GB、TB或百分比(%)为单位,默认值为MB。如果指定%,则增量大小为发生增长时文件大小的指定百分比。指定的大小舍入为最接近64KB的倍数。
  • OFFLINE:将文件设置为脱机并使文件组中的所有对象都不可访问。
  • <add_or_modify_filegroups>::=</add_or_modify_filegroups>:在数据库中添加、修改或删除文件组。
  • CONTAINS FILESTREAM:指定文件组在文件系统中存储 FILESTREAM
    二进制大型对象 (BLOB)。
  • CONTAINS
    MEMORY_OPTIMIZED_DATA:指定文件组在文件系统中存储内存优化数据。每个数据库只能有一个
    MEMORY_OPTIMIZED_DATA 文件组。
    在创建内存优化表时,文件组不能为空,其中必须至少包含一个文件。
  • REMOVE FILEGROUP
    filegroup_name:删除文件组filegroup_name从数据库中删除文件组。
    除非文件组为空,否则无法将其删除。 首先从文件组中删除所有文件。
  • MODIFY FILEGROUP filegroup_name:修改文件组。
  • DEFAULT:更改默认的数据库文件组到filegroup_name。
    数据库中只能有一个文件组作为默认文件组。
  • AUTOGROW_SINGLE_FILE:在文件组中的文件符合自动增长阈值时,仅该文件是增长。
    这是默认设置。
  • AUTOGROW_ALL_FILES:如果文件组中的文件达到了自动增长阈值,文件组中的所有文件都增长。
  • <filegroup_updatability_option>:对文件组设置只读或读/写属性。
  • READ_ONLY | READONLY:指定文件组为只读。 不允许更新其中的对象。
    主文件组不能设置为只读。
    若要更改此状态,您必须对数据库有独占访问权限。
  • 因为只读数据库不允许数据修改,所以将发生以下情况:
    系统启动时,将跳过自动恢复。
    不能收缩数据库。
    在只读数据库中不会进行锁定。 这可以加快查询速度。

【示例】

A. 向数据库中添加由两个文件组成的文件组
  以下示例在 AdventureWorks2012 数据库中创建文件组 Test1FG1,然后将两个
5 MB 的文件添加到该文件组。

USE master  
ALTER DATABASE AdventureWorks2012  
ADD FILEGROUP Test1FG1;  
GO  
ALTER DATABASE AdventureWorks2012   
ADD FILE   
(  
    NAME = test1dat3,  
    FILENAME = 'D:\Microsoft SQL Server\MSSQL\DATA\t1dat3.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1dat4,  
    FILENAME = 'D:\Microsoft SQL Server\MSSQL\DATA\t1dat4.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
)  
TO FILEGROUP Test1FG1;  
GO  

B.向数据库中添加两个日志文件

USE master;  
ALTER DATABASE AdventureWorks2012   
ADD LOG FILE   
(  
    NAME = test1log2,  
    FILENAME = 'D:\Microsoft SQL Server\MSSQL\DATA\test2log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
),  
(  
    NAME = test1log3,  
    FILENAME = 'D:\Microsoft SQL Server\DATA\test3log.ldf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB  
);  
GO  

C.从数据库中删除文件

USE master;  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE test1dat4;  
GO  

D.修改文件
  以下示例添加的一个文件的大小。ALTER DATABASE MODIFY FILE
命令与可以使文件大小更大,因此如果你需要使文件大小更小你需要使用 DBCC
SHRINKFILE。

USE master;  
ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO  

此示例中收缩数据文件的大小为 100 MB,然后指定在该数量的大小。

USE AdventureWorks2012;
DBCC SHRINKFILE (AdventureWorks2012_data, 100);
GO

USE master;  
ALTER DATABASE AdventureWorks2012   
MODIFY FILE  
(NAME = test1dat3,  
SIZE = 200MB);  
GO

E.将文件移至新位置
  下面以把AdventureWorks数据中的数据文件E:\t1dat2.ndf移动到C:\t1dat2.ndf为例,说明移动数据文件的过程。
首先把数据库脱机:

alter database AdventureWorks set offline

在操作系统中把E:\t1dat2.ndf移动到C:\t1dat2.ndf:

!! move E:\t1dat2.ndf C:\t1dat2.ndf

修改数据库中对此文件路径的记载:

ALTER DATABASE AdventureWorks 
MODIFY FILE  
(  
    NAME = Test1dat2,  
    FILENAME = N'C:\t1dat2.ndf'  
);  
GO  

最后再把数据库重新联机:

alter database AdventureWorks set online

然后查询t1dat2的物理文件路径:

select name,physical_name from sys.database_files where name ='C:\t1dat2.ndf'

F.使文件组成为默认文件组
  下面的示例使Test1FG1成为默认文件组。 然后,默认文件组被重置为 PRIMARY
文件组。 请注意,必须使用括号或引号分隔 PRIMARY。

USE master;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP Test1FG1 DEFAULT;  
GO  
ALTER DATABASE AdventureWorks2012   
MODIFY FILEGROUP [PRIMARY] DEFAULT;  
GO  

2.1  索引基础

用户对数据库最频繁的操作是进行数据查询。一般情况下,数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时,搜索数据就需要很长的时间,这就造成了服务器的资源浪费。为了提高检索数据的能力,数据库引入了索引机制。

SQL
Server数据库中的索引与书籍中的索引类似,在一本书中,利用索引可以快速查找所需信息,无须阅读整本书。在数据库中,索引使数据库程序无须对整个表进行扫描,就可以在其中找到所需数据。书中的索引是一个词语列表,其中注明了包含各个词的页码。而数据库中的索引是一个表中所包含的值的列表,其中注明了表中包含各个值的行所在的存储位置。可以为表中的单个列建立索引,也可以为一组列建立索引。

通过定义索引,可以提高查询速率,节省响应时间。不过,索引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外,为了维护索引,对数据进行插入、更新、删除操作的命令所花费的时间会更长。在设计和创建索引时,应确保对性能的提高程度大于在存储空间和处理资源方面的代价。

索引是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引是依赖于表建立的,它提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的,一部分用来存放表的数据页面,另一部分存放索引页面。索引就存放在索引页面上,通常,索引页面相对于数据页面来说小得多。当进行数据检索时,系统先搜索索引页面,从中找到所需数据的指针,再直接通过指针从数据页面中读取数据。从某种程度上,可以把数据库看作一本书,把索引看作书的目录,通过目录查找书中的信息,显然较没有目录的书方便、快捷。

SQL Server
中的索引是以B-树结构来维护的,如图12所示。B-树是一个多层次、自维护的结构。一个B-树包括一个顶层,称为根节点(Root
Node);0 到多个中间层(Intermediate);一个底层(Level
0),底层中包括若干叶子节点(Leaf Node)。在图
12中,每个方框代表一个索引页,索引列的宽度越大,B-树的深度越深,即层次越多,读取记录所要访问的索引页就越多。也就是说,数据查询的性能将随索引列层次数目的增加而降低。

 

 图片 15

 

图 12  索引结构

SQL
Server使用两种基本类型的索引:聚集索引和非聚集索引。这两类索引都可以对多个列进行索引,在这种情况下也可以称它们为组合索引。依据查询使用索引的方式,还可以将其称为覆盖索引(covering
index)。在SQL Server中,还支持唯一索引、索引视图、全文索引和XML索引。

(1)非聚集索引

如图13所示,非聚集索引与课本中的索引类似。数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储,而表中的信息按另一种顺序存储(这可以由聚集索引规定)。如果在表中未创建聚集索引,则无法保证这些行具有任何特定的顺序。

典型的桌面数据库使用的是非聚集索引。在这类索引中,索引键值是有序的,而每个索引节点所指向的数据行是无序的。一个SQL
Server表最多可以拥有255个非聚集索引。

非聚集索引与聚集索引一样有 B-树结构,但是有两个重大差别:

l 数据行不按非聚集索引键的顺序排序和存储。

l 非聚集索引的叶层不包含数据页。

相反,叶节点包含索引行。每个索引行包含非聚集键值以及一个或多个行定位器,这些行定位器指向有该键值的数据行(如果索引不唯一,则可能是多行)。非聚集索引可以在有聚集索引的表、堆集或索引视图上定义。在
SQL Server中,非聚集索引中的行定位器有两种形式:

l 如果表是堆集(没有聚集索引),行定位器就是指向行的指针。该指针用文件标识符
(ID)、页码和页上的行数生成。整个指针称为行 ID。

l 如果表是堆集(没有聚集索引),行定位器就是指向行的指针。该指针用文件标识符
(ID)、页码和页上的行数生成。整个指针称为行 ID。

由于非聚集索引将聚集索引键作为其行指针存储,因此使聚集索引键尽可能小很重要。如果表还有非聚集索引,请不要选择大的列作为聚集索引的键。

 

 图片 16

 

 

图 13  非聚集索引结构

与使用书中索引的方式相似,SQL
Server在搜索数据值时,先对非聚集索引进行搜索,找到数据值在表中的位置,然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法,因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。如果基础表使用聚集索引排序,则该位置为聚集键值;否则,该位置为包含行的文件号、页号和槽号的行
ID (RID)。

在创建非聚集索引之前,应先了解您的数据是如何被访问的。可考虑将非聚集索引用于:

l 包含大量非重复值的列,如姓氏和名字的组合(如果聚集索引用于其它列)。如果只有很少的非重复值,如只有
1 和 0,则大多数查询将不使用索引,因为此时表扫描通常更有效。

l 不返回大型结果集的查询。

l 返回精确匹配的查询的搜索条件(WHERE 子句)中经常使用的列。

l 经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引,在任何外键列上创建一个聚集索引。

l 在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。

(2)聚集索引

如图14所示,聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。

聚集索引在系统数据库表sysindexes 内有一行,其 indid =
1。数据链内的页和其内的行按聚集索引键值排序。所有插入都在所插入行中的键值与排序顺序相匹配时执行。

SQL
Server将索引组织为B-树。索引内的每一页包含一个页首,页首后面跟着索引行。每个索引行都包含一个键值以及一个指向较低级页或数据行的指针。索引的每个页称为索引节点。B-树的顶端节点称为根节点。索引的底层节点称为叶节点。每级索引中的页链接在双向链接列表中。在聚集索引内数据页组成叶节点。根和叶之间的任何索引级统称为中间级。

对于聚集索引,sysindexes.root 指向它的顶端。SQL Server
沿着聚集索引浏览以找到聚集索引键对应的行。为找到键的范围,SQL Server
浏览索引以找到这个范围的起始键值,然后用向前或向后指针扫描数据页。为找到数据页链的首页,SQL
Server
从索引的根节点开始沿最左边的指针进行扫描,图14说明聚集索引的结构。

 

 图片 17

 

 

图14  聚集索引结构

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节省成本。

对于聚集索引,人们往往有一些错误的认识。其中,最常见的错误有:

l 聚集索引会降低insert操作的速度,因为必须要向后移动一半的数据来为新插入的行腾出空间。这种认识是错误的,因为可以利用填充因子控制填充的百分比,从而在索引页上为新插入的数据保留空间。如果索引页填满了,SQL
Server将会进行页拆分,在这种情况下只有第一个页才会受到影响。

l 在使用标识列的主键上创建聚集索引是一种好的设计方法,它可以使对表的操作达到最快速度。这种认识是错误的,它浪费了创建其它更有效的聚集索引的机会。并且,使用这种方法会把每个新插入的记录行都存储到表尾部的同一个的数据页中,这将导致数据库的热点和锁争用。笔者曾经见过采用这种方法设计的数据库,对于每一个新订单,客户服务人员都不得不等待数分钟来加以确认。

l 聚集索引是具有魔力的。如果哪个查询的速度不够快,那么就在该列上创建聚集索引,对于表的操作速度一定会得到提高。这种认识也是错误的,聚集索引只是比非聚集索引稍稍快了那么一点点。因为在每个表上只能创建一个聚集索引,所以它也是一种宝贵的性能资源,只有在那些经常作为条件查询一组记录行的列上才应该建立聚集索引。

在创建聚集索引之前,应先了解数据是如何被访问的。可考虑将聚集索引用于:

l 包含大量非重复值的列。

l 使用下列运算符返回一个范围值的查询:BETWEEN、>、>=、< 和
<=。

l 被连续访问的列。

l 返回大型结果集的查询。

l 经常被使用联接或 GROUP BY
子句的查询访问的列;一般来说,这些是外键列。对 ORDER BY 或 GROUP BY
子句中指定的列进行索引,可以使 SQL Server
不必对数据进行排序,因为这些行已经排序。这样可以提高查询性能。

l OLTP
类型的应用程序,这些程序要求进行非常快速的单行查找(一般通过主键)。应在主键上创建聚集索引。

注意,聚集索引不适用于:

l 频繁更改的列,这将导致整行移动(因为 SQL Server
必须按物理顺序保留行中的数据值)。这一点要特别注意,因为在大数据量事务处理系统中数据是易失的。

l 宽键,来自聚集索引的键值由所有非聚集索引作为查找键使用,因此存储在每个非聚集索引的叶条目内。

(3)唯一索引

唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下,该索引可以确保索引列中每个值组合都是唯一的。例如,如果在
last_name、first_name 和 middle_initial 列的组合上创建了唯一索引
full_name,则该表中任何两个人都不可以具有相同的全名。

聚集索引和非聚集索引都可以是唯一的。因此,只要列中的数据是唯一的,就可以在同一个表上创建一个唯一的聚集索引和多个唯一的非聚集索引。

只有当唯一性是数据本身的特征时,指定唯一索引才有意义。如果必须实施唯一性以确保数据的完整性,则应在列上创建
UNIQUE 或 PRIMARY KEY
约束,而不要创建唯一索引。例如,如果打算经常查询雇员表(主键为
emp_id)中的社会安全号码 (ssn) 列,并希望确保社会安全号码的唯一性,则在
ssn 列上创建 UNIQUE
约束。如果用户为一个以上的雇员输入了同一个社会安全号码,则会显示错误。

(4)索引视图

复杂报表的场景经常会在数据仓储应用程序中遇到,它在查询过程中会对数据库服务器产生大量请求。当这些查询访问视图时,因为数据库将建立视图结果集所需的逻辑合并到从基本表数据建立完整查询结果集所需的逻辑中,所以性能将会下降。这一操作的开销可能会比较大,尤其当视图涉及到复杂的大量行处理–如大量数据聚合或多表联结时。因为结果集并不永久存放在数据库(标准视图)中,以后对该视图的访问可能导致在每次执行查询时建立结果集的代价。

SQL
Server允许为视图创建独特的聚集索引,从而让访问此类视图的查询的性能得到极大地改善。在创建了这样一个索引后,视图将被执行,结果集将被存放在数据库中,存放的方式与带有聚集索引的表的存放方式相同。这就在数据库中有效地实现了查询结果。对于那些在FROM子句中不直接指定视图名的查询,SQL
Server查询优化器将使用视图索引。现有查询将受益于从索引视图检索数据而无需重新编写程序原码的高效率。对于某些特定类型的视图,甚至可以获得指数级的性能改善。

如果在视图上创建索引,那么视图中的数据会被立即存储在数据库中,对索引视图进行修改,那么这些修改会立即反映到基础表中。同理,对基础表所进行的数据修改也会反映到索引视图那里。索引的惟一性大大提高了SQL
Server 查找那些被修改的数据行。

维护索引视图比维护基础表的索引更为复杂。所以,如果认为值得以因数据修改而增加系统负担为代价来提高数据检索的速度,那么应该在视图上创建索引。

设计索引视图时,请考虑以下准则:

l 设计的索引视图必须能用于多个查询或多个计算。 例如,包含某列的 SUM
和某列的 COUNT_BIG 的索引视图可用于包含函数 SUM、COUNT、COUNT_BIG 或
AVG 的查询。由于只需检索视图中的少数几行,而不是基表中的所有行,且执行
AVG 函数要求的部分计算已经完成,所以查询将比较快。

l 使索引保持紧凑。
通过使用最少的列数和尽可能少的字节数,优化器在查找行数据时可获得最高的效率。相反,如果定义了大的群集索引关键字,则为视图定义的任何辅助性非群集索引都将明显增大,这是因为非群集索引项除包含索引定义的列之外,还将包含群集关键字。

l 考虑生成的索引视图的大小。
在单纯的聚合情况下,如果索引视图的大小类似于原表的大小,使用索引视图可能无法明显提高任何性能。

l 设计多个较小的索引视图来加快部分进程的速度。
有时可能无法设计出能满足整个查询需要的索引视图。此时即可考虑创建这样一些索引视图,每个索引视图执行一部分查询。

在为视图创建索引前,视图本身必须满足以下条件:

l 视图以及视图中引用的所有表都必须在同一数据库中,并具有同一个所有者。

l 索引视图无需包含要供优化器使用的查询中引用的所有表。

l 必须先为视图创建唯一群集索引,然后才可以创建其它索引。

l 创建基表、视图和索引以及修改基表和视图中的数据时,必须正确设置某些 SET
选项(在本文档的后文中讨论)。另外,如果这些 SET
选项正确,查询优化器将不考虑索引视图。

l 视图必须使用架构绑定创建,视图中引用的任何用户定义的函数必须使用
SCHEMABINDING 选项创建。

l 另外,还要求有一定的磁盘空间来存放由索引视图定义的数据。

在视图上创建了索引之后,如果打算修改视图数据,则应该保证修改时的选项设置与创建索引时的选项设置一样,否则SQL
Server 将产生错误信息,并回滚所做的INSERT、UPDATE 和DELETE 操作。

并非所有查询都会从索引视图中获益。与普通索引类似,如果未使用索引视图,就没有好处可言。在此情况下,不但不能提高性能,还会加大磁盘空间的占用、增加维护和优化的成本。但是,如果使用了索引视图,它们可以(成数量级地)明显地提高数据访问的性能。这是因为查询优化器使用存储在索引视图中的预先计算的结果,从而大大降低了执行查询的成本。

查询优化器只在查询的成本比较大时才考虑使用索引视图。这样可以避免在查询优化成本超出因使用索引视图而节省的成本时,试图使用各种索引视图。当查询成本低于
1 时,几乎不使用索引视图

使用索引视图可以受益的应用包括:

l 决定支持工作量

l 数据集市

l 联机分析处理 (OLAP) 库和源

l 数据挖掘工作量

从查询的类型和模式的角度来看,受益的应用可被归纳为包含以下内容的应用:

l 大表的联接和聚合

l 查询的重复模式

l 重复聚合相同或重叠的列集

l 针对相同关键字重复联接相同的表

l 上述的组合

相反,包含许多写入的联机事务处理 (OLTP)
系统或更新频繁的数据库,可能会因为要同时更新视图和根本基表而使维护成本增加,所以不能利用索引视图。

SQL Server
自动维护索引视图,这与维护任何其它索引的情况类似。对于普通索引而言,每个索引都直接连接到单个表。通过对基础表执行每个
INSERT、UPDATE 或 DELETE
操作,索引相应地进行了更新,以便使存储在该索引中的值始终与表一致。

索引视图的维护与此类似。不过,如果视图引用了多个表,则对这些表中的任何一个进行更新都需要更新索引视图。与普通索引不同的是,对任何一个参与的表执行一次行插入操作都可能导致在索引视图中进行多次行插入操作。更新和删除操作的情况也是如此。因此,较之于维护表的索引,维护索引视图的代价更为高昂。

在 SQL Server 中,某些视图可以更新。如果某个视图可以更新,则使用
INSERT、UPDATE 和 DELETE
语句可通过该视图直接修改根本基表。为某个视图创建索引并不会妨碍该视图的更新。

与 SQL Server 2000 相比,SQL Server
包含了许多索引视图的改进功能。可索引的视图组已扩展至包含基于下列各项的视图:

l 标量聚合,包括 SUM 和不带 GROUP BY 的 COUNT_BIG。

l 标量表达式和用户定义的功能 (UDFs)。例如,给定一个表 T(a int, b int, c
int) 和一个标量 UDF dbo.MyUDF(@x int),T
上定义的索引视图可包含一个计算列(比如:a+b 或 dbo.MyUDF(a))。

l 不精确的永久性列。不精确的列是一种浮型或实型的列,或者是一种派生自浮型或实型列的计算列。在
SQL Server 2000
中,如果不属于索引键的一部分,不精确的列就可用于索引视图的选择列表。不精确的列不能用于视图定义中的其他地方(比如:WHERE
或 FROM 子句)。如果不精确的列永久保存在基表中,那么 SQL Server
允许其加入键或视图定义。永久性列包含常规列和标记为 PERSISTED 的计算列。

l 不精确的非永久性列无法加入索引或索引视图的根本原因是:必须使数据库脱离原计算机,然后再附加到另一台计算机。完成转移之后,保存在索引或索引视图中的所有计算列值在新硬件上的派生方式必须与旧硬件完全相同,精确到每个位。否则,这些索引视图在新硬件上会遭到逻辑破坏。由于这种破坏,在新硬件上,针对索引视图的查询会根据计划是否使用了索引视图或基表来派生视图数据,返回不同的应答。此外,无法在新计算机上正常维护索引视图。可惜,不同计算机上的浮点硬件(即便采用相同制造商的相同处理器体系结构)在处理器的版本上并不总是完全相同。对于某些浮点值
a 和 b,固件升级可能导致新硬件上的 (a*b) 不同于旧硬件上的
(a*b)。例如,结果可能非常相近,但仍存在细微差别。在进行索引之前一直保留不精确的计算值可解决这种分离/附加的不一致性问题,因为在进行索引和索引视图的数据库更新和维护期间,在相同的计算机上评估了所有表达式。

l 通用语言运行时 (CLR) 类型。SQL Server 的一个主要的新功能是支持基于 CLR
的用户定义的类型 (UDT) 和
UDF。假如列或表达式具有确定性或是永久且精确的,或者二者兼具,那么就可在
CLR UDT 列或从这些列派生而来的表达式上定义索引视图。不能在索引视图上使用
CLR 用户定义的聚合。

优化器匹配查询和索引视图(使之可在查询计划中使用)的功能经扩展包含:

l 新的表达式类型,位于查询或视图的 SELECT
列表或条件中,涉及:标量表达式(比如 (a+b)/2)、标量聚合、标量 UDF。

l 间隔归入。优化器可检测索引视图定义中的间隔条件是否覆盖或“归入”查询中的间隔条件。例如,优化器可确定“a>10
and a<20”覆盖“a>12 and a<18”。

l  表达式等价。某些表达式虽然在语法上有所不同,但最终的结果却相同,那么可以将其视为等价。例如,“a=b
and c<>10”与“10<>c and b=a”等价。

(4)全文索引

全文索引可以对存储在SQL
Server数据库中的文本数据执行快速检索功能。同LIKE谓词不同,全文索引只对字符模式进行操作,对字和语句执行搜索功能。全文索引对于查询非结构化数据非常有效。一般情况下,可以对char、varchar和nvarchar数据类型的列创建全文索引,同时,还可以对二进制格式的列创建索引,如image和varbinary数据类型列。对于这些二进制数据,无法使用LIKE谓词。

为了对表创建全文索引,表必须包含单个、唯一、非空列。当执行全文检索的时候,SQL
Server搜索引擎返回匹配搜索条件的行的键值。一般情况,使用sql
server中的全文索引,经过大体4个步骤:

l 安装全文索引服务;

l 为数据表建立全文索引目录;

l 使全文索引与数据表内容同步;

l 使用全文索引进行查询。

(5)XML索引

Microsoft SQL Server 以 XML 数据类型的形式添加了内置的 XML 支持。XML
数据可以存储在 XML 数据类型列内部。另外,通过将一个 XML 方案集合与此 XML
数据类型列关联,还可以对其进行进一步的限制。存储在 XML 数据类型列中的
XML 值可以借助 XQuery 和 XML 数据修改语言 (DML) 进行处理。可以在 XML
数据上建立索引,以增强查询性能。此外,FOR XML 和 OPENXML
也已得到增强,能够支持新的 XML 数据类型。

SQL Server 中引入的存储和处理 XML 数据的新功能与 SQL Server
早期版本中提供的 XML 功能结合在一起,为开发人员提供了多种在 XML
应用程序中存储和处理 XML 数据的方法。由于使用 SQL Server
提供的方法,有多种方法可以生成 XML
应用程序,因此,了解各种不同技术的方案,以及如何在各种技术之间进行权衡和配合对于作出正确的选择是至关重要的。本文提供了如何选择适当的方法,使用
SQL Server 开发 XML 应用程序的指南。

针对XML数据类型,SQL
Server提供了XML索引类型。XML索引是在xml数据类型列上创建的索引,同其他索引类似,XML索引可以提高查询性能。

在使用xp_cmdshell之前,需要执行sp_configure以启用xp_cmdshell,代码如下:

7.FILEGROWTH

  提供一个值来说明文件每次增大多少字节或者多少百分比。

   MODIFY
FILEGROUP子句指出待修改的文件组,其中filegroup_name为文件组名称,file_property说明修改后的文件组属性,其取值包含以下几种:
?       
READONLY:将文件组设置为只读文件组,之后将禁止对其中的数据库对象进行修改。数据库中的主文件组不能设置为只读文件组;
说明:如果将主文件组被设置为只读文件组,所有用户不能在该数据库中再创建任何新的数据库对象和登录标识,或重新编译存储过程,因为这些工作都需要修改数据库中的系统表。
?       
READWRITE:删除文件中的只读属性,之后可对该文件组进行读、写操作;
?       
DEFAULT:将指定文件组设置为数据库的默认文件组,每个数据库中只能有一个默认文件组。
(1)向MYDB2中添加一个数据文件MYDB2_P3_dat,其大小为10MB,执行后此数据文件被添加到主文件组[PRIMARY]中:

数据库文件组

为便于分配和管理,可以将数据库对象和文件一起分成文件组。SQL
Server的文件组由若干个数据文件组成。
  SQL
Server的文件组分为primary文件组和用户文件组,分别对应Oracle数据库中的system表空间和用户表空间。

  • primary文件组
    主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。与Oracle数据库的system表空间相似,primary文件组不能删除,其名称primary也是固定不能修改的。
  • 用户定义文件组
    用户定义文件组是通过在 CREATE DATABASE 或 ALTER DATABASE 语句中使用
    FILEGROUP 关键字指定的任何文件组。

日志文件不包括在文件组内。日志空间与数据空间分开管理。
  SQL Server数据库中没有对应于Oracle临时表空间的文件组,SQL
Server的多版本数据(undo)以及排序或散列操作所产生的临时数据都存储于tempdb系统数据库中,多个数据库共用tempdb数据库。

一个文件不可以是多个文件组的成员。表、索引和大型对象数据可以与指定的文件组相关联。在这种情况下,它们的所有页将被分配到该文件组,或者对表和索引进行分区。已分区表和索引的数据被分割为单元,每个单元可以放置在数据库中的单独文件组中。
  在 SQL
Server数据库中,不允许删除包含表或索引的文件组,这与Oracle不同,在Oracle中,如果表空间中包含数据,使用drop
tablespace删除表空间时,可以附加including contents子句。
  每个数据库中均有一个文件组被指定为默认文件组。如果创建表或索引时未指定文件组,则将假定所有页都从默认文件组分配。一次只能有一个文件组作为默认文件组。如果没有指定默认文件组,则将主文件组作为默认文件组。db_owner
固定数据库角色成员可以将默认文件组从一个文件组切换到另一个。

文件和文件组的设计规则
下列规则适用于文件和文件组:

  • 一个文件或文件组不能由多个数据库使用。例如,任何其他数据库都不能使用包含
    sales 数据库中的数据和对象的文件 sales.mdf 和 sales.ndf。
  • 一个文件只能是一个文件组的成员。
  • 事务日志文件不能属于任何文件组。

4.6  删除存储过程

当某些存储过程无法支持应用时,可能需要将其删除。删除存储过程可以在SQL
Server Management
Studio中采用图形工具删除,也可以执行Transact-SQL语句删除。在后续我们将给出删除存储过程的实例,在此,提供删除存储过程的Transact-SQL语句的语法规范。

DROP PROCEDURE { [ schema_name. ] procedure } [ ,…n ]

其中:

schema_name,表示拥有存储过程的用户ID;

procedure,是要删除的存储过程或存储过程组的名称。过程名称必须符合标识符规则;

l n,是表示可以指定多个过程的占位符。

为了查看存储过程名列表,可以使用sys.objects视图。为了显示存储过程定义,可以使用sys.sql_modules视图、当删除存储过程之后,存储过程的信息也会从sysobjects和syscomments系统表中删除。

当需要删除某个存储过程的时候,直接在SQL Server Management
Studio中,通过图形工具删除,下面以删除EAMS数据库存储过程GetLogInfo为例,其步骤如下:

1. 打开SQL Server Management
Studio,在“对象资源管理器”视图中,展开“数据库”节点,选择需要删除存储过程的数据库EAMS,展开数据库,展开“可编程性”节点,选择“存储过程”节点,选择存储过程“GetLogInfo”,单击鼠标右键,选择“删除”菜单项,如图37所示。

 

 图片 18

 

 

图 37  选择“Delete”菜单项

2. 打开“删除对象”对话框,如图38所示,当确定删除时,单击“确定”按钮即可。

 

 图片 19

 

 

图 38  “删除对象”对话框

同样,可以执行Transact-SQL语句完成存储过程的删除操作,针对这个示例,其语句如下。

USE [EAMS]

GO

DROP PROCEDURE [dbo].[GetLogInfo]

GO

USE [master]

GO

 

使用SQL语句为指定的表添加约束的方法有两种:第一种是使用CREATE TABLE
语句在创建表结构的同时添加相关约束。第二种是使用ALTER
TABLE语句在已经创建的表中添加约束。通常建议使用第二种方式。

二.ALTER语句(修改)

   ALTER语句可以修改数据库和表大小,文件位置或其他特性。

  修改数据库的大小:

  图片 20

  修改表(增加一列)

  图片 21

MYDB2数据库文件
        主文件组        MYDB2_GROUP文件组        日志文件
逻辑名        MYDB2_P1_dat        MYDB2_P2_dat       
MYDB2_S1_dat        MYDB2_S2_dat        MYDB2_log
文件名        C:\mssql7\data\
MYDB2_P1.mdf        c:\mssql7\data\
MYDB2_P2.ndf        C:\mssql7\data\
MYDB2_S1.ndf        c:\mssql7\data\
MYDB2_S2.ndf        c:\mssql7\data\
MYDB2_log.ldf
初始长度        5MB        5MB        10MB        20MB        10MB
最大长度        10MB        10MB        50MB        100MB       
无限制
增    量        20%        1MB        10MB        20MB        10%
2.使用Transact-SQL语句修改数据库
ALTER DATABASE 语句的语法格式为:

查询指定表被分配的extent信息

在SQL Server可以使用dbcc extentinfo命令查询表被分配的extent信息。

dbcc extentinfo(数据库名,表名)

2.2  创建索引

确定了索引设计后,便可以在数据库的表上创建索引。创建索引时须考虑的其它事项包括:

l 只有表的所有者可以在同一个表中创建索引。

l 每个表中只能创建一个聚集索引。

l 每个表可以创建的非聚集索引最多为 249 个(包括 PRIMARY KEY 或 UNIQUE
约束创建的任何索引)。

l 包含索引的所有长度固定列的最大大小为 900 字节。例如,不可以在定义为
char(300)、char(300) 和 char (301)
的三个列上创建单个索引,因为总宽度超过了 900 字节。

l 包含同一索引的列的最大数目为 16。

在使用 CREATE INDEX
语句创建索引时,必须指定索引、表以及索引所应用的列的名称。作为 PRIMARY
KEY 或 UNIQUE 约束的一部分或使用 SQL Server
企业管理器创建的新索引,会根据数据库表的名称,自动获得系统定义的名称。如果在一个表上创建多个索引,这些索引的名称被追加
_1、_2 等。必要时可对索引重新命名。

当需要创建索引的时候,可以采用多种方式,可以在SQL Server Management
Studio中采用图形工具创建索引,也可以执行Transact-SQL语句创建索引,下面我们分别进行介绍。

(1)使用SQL Server Management Studio图形工具创建索引

使用SQL Server Management Studio图形工具创建索引的步骤如下:

1. 打开SQL Server Management
Studio,在“对象资源管理器”视图中双击需要创建索引的数据库,展开需要创建索引的表,选择“索引”节点,单击鼠标右键,如图15所示。

 

 图片 22

 

 

图 15   新建索引

2. 选择“新建索引”菜单项,打开“新建索引”对话框的“常规”视图,如图16所示。

 

 图片 23

 

 

图 16  “常规”视图

在该对话框中,定义:

l 索引的名称;

l 索引的类型;

l 索引列。

3. 当添加索引列的时候,单击“添加”按钮,将打开创建索引的表,如图17所示,用户可以指定索引列。

 

 图片 24

 

 

图 17  定义索引列

4. 在“选项”对话框中,定义索引的相关选项,如图18所示。

 

 图片 25

 

 

图 18  定义索引选项

选项说明:

l “删除现有索引”,指定在创建新索引之前删除任何现有的同名索引。“删除现有索引”只有当对话框处于重新创建状态才变为有效,在这种情况下,“重新生成索引”不可用。

l “重新生成索引”,重新创建索引。当对话框打开的时候,选项默认没有选中。

l “忽略重复的值”,指定忽略重复值。

l “自动重新计算统计信息”,指定不重新计算索引统计。不推荐使用本选项。

l “在访问索引时使用行锁”,支持行层锁。默认情况下,SQL
Server在页层、行层或者表层锁机制之间进行选择。当清除该选项,索引不使用行层锁机制。默认情况下,选中该选项。

l “在访问索引时使用页锁”,支持也层锁机制。默认情况下,SQL
Server在页层、行层或者表层锁机制之间进行选择。当清除该选项,索引不使用页层锁机制。默认情况下,选中该选项。

l “将中间排序结果存储在tempdb中”,将用于创建索引的中间排序结果存储在tempdb数据库中。默认情况下,没有选中该选项,该选项只有在对话框处于重新创建状态或者重新生成状态的时候才可用。

l “设置填充因子”, 指定 SQL Server
在创建索引过程中,对各索引页的叶级所进行填充的程度。

l “填充索引”,指定填充索引。填充索引在索引的每个内部节点上留出空格。

l “运行在创建索引时在线处理DML语句”:允许用户并发在索引操作过程中,访问底层表、聚集索引数据和任何相关非聚集索引。该选项默认情况下没有被选中,只有在对话框处于重新创建状态才可用。

l “设置最大并行度”,限制在并行计划执行过程中使用的处理器数量,默认值为0,使用实际可用的CPU,取值为1表示压缩并行计划生成;设置大于1的数字,表示在单个查询过程中使用的处理器的最大数量。

l “使用索引”,将索引可用。

5. 在“包含性列”对话框中,定义索引中包含的其他列,如图19所示。当选择聚集索引和XML索引时,无法添加列,当选择非聚集索引类型的时候,可以添加索引列。

 

 图片 26

 

 

图 19  定义索引所包含的其他列

6. 在“存储”对话框中,定义索引的存储选项,包括定义文件组、分区模式等参数,如图20所示。

 

 图片 27

 

 

图 20  定义索引的存储选项

7. 当完成定义之后,单击“确定”按钮,完成对索引的定义,SQL
Server数据库引擎将创建索引。

(2)使用Transact-SQL语句创建索引

SQL Server提供了CREATE INDEX
Transact-SQL语法,用于创建索引,其语法格式如下:

 

 

创建关系索引的语法:
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = number_of_processors
}

创建XML索引的语法:

CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH } ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
    table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = number_of_processors
}


向后兼容的关系索引创建语法:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | default } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
    table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

 

 

参数说明:

l UNIQUE,为表或视图创建唯一索引(不允许存在索引值相同的两行)。视图上的聚集索引必须是
UNIQUE 索引。在创建索引时,如果数据已存在,Microsoft SQL
Server会检查是否有重复值,并在每次使用 INSERT 或 UPDATE
语句添加数据时进行这种检查。如果存在重复的键值,将取消 CREATE INDEX
语句,并返回错误信息,给出第一个重复值。当创建 UNIQUE 索引时,有多个
NULL 值被看作副本。如果存在唯一索引,那么会产生重复键值的 UPDATE 或
INSERT 语句将回滚,SQL Server 将显示错误信息。即使 UPDATE 或 INSERT
语句更改了许多行但只产生了一个重复值,也会出现这种情况。如果在有唯一索引并且指定了
IGNORE_DUP_KEY 子句情况下输入数据,则只有违反 UNIQUE
索引的行才会失败。在处理 UPDATE 语句时,IGNORE_DUP_KEY 不起作用。SQL
Server 不允许为已经包含重复值的列创建唯一索引,无论是否设置了
IGNORE_DUP_KEY。如果尝试这样做,SQL Server
会显示错误信息;重复值必须先删除,才能为这些列创建唯一索引。

l CLUSTERED,创建一个对象,其中行的物理排序与索引排序相同,并且聚集索引的最低一级(叶级)包含实际的数据行。一个表或视图只允许同时有一个聚集索引。具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引,然后才能为该视图定义其它索引。在创建任何非聚集索引之前创建聚集索引。创建聚集索引时重建表上现有的非聚集索引。如果没有指定
CLUSTERED,则创建非聚集索引。

l NONCLUSTERED,创建一个指定表的逻辑排序的对象。对于非聚集索引,行的物理排序独立于索引排序。非聚集索引的叶级包含索引行。每个索引行均包含非聚集键值和一个或多个行定位器(指向包含该值的行)。如果表没有聚集索引,行定位器就是行的磁盘地址。如果表有聚集索引,行定位器就是该行的聚集索引键。每个表最多可以有
249 个非聚集索引(无论这些非聚集索引的创建方式如何:是使用 PRIMARY KEY
和 UNIQUE 约束隐式创建,还是使用 CREATE INDEX
显式创建)。每个索引均可以提供对数据的不同排序次序的访问。对于索引视图,只能为已经定义了聚集索引的视图创建非聚集索引。因此,索引视图中非聚集索引的行定位器一定是行的聚集键。

index_name,是索引名。索引名在表或视图中必须唯一,但在数据库中不必唯一。索引名必须遵循标识符规则。

database_name,索引所在的数据库名。

schema_name,索引所在的模式名。

table_or_view_name,包含要创建索引的列的表或者视图。可以选择指定数据库和表所有者。

column,应用索引的列。指定两个或多个列名,可为指定列的组合值创建组合索引。在
table 后的圆括号中列出组合索引中要包括的列(按排序优先级排列)。

l [ ASC | DESC ],确定具体某个索引列的升序或降序排序方向。默认设置为
ASC。

xml_column_name,表示索引创建所在的XML列。在单个XML索引定义中,只能定义在一个XML列上。但是,可以在单个XML列上创建多个辅助XML索引。主XML索引不能在计算XML列上创建。

l USING XML INDEX
xml_index_name,定义在创建副本XML索引中使用的主XML索引。

l FOR { VALUE | PATH },FOR VALUE用于在主XML索引的VALUE, HID, PK,
XID列上创建副本索引。FOR PATH用于在主XML索引的HID, VALUE, PK,
XID列上创建副本索引。

l INCLUDE (*column [ ,n* ]
),定义添加到非聚集索引页级的非键列。

l ON
partition_scheme_name**(column_name),**定义分区模式。分区模式用于定义分区索引映射的文件组。

l ON filegroup_name,在给定的
filegroup 上创建指定的索引。该文件组必须已经通过执行 CREATE DATABASE
或 ALTER DATABASE 创建。

l PAD_INDEX = { ON |
OFF },指定索引中间级中每个页(节点)上保持开放的空间。PAD_INDEX
选项只有在指定了 FILLFACTOR 时才有用,因为 PAD_INDEX 使用由 FILLFACTOR
所指定的百分比。默认情况下,给定中间级页上的键集,SQL Server
将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。如果为
FILLFACTOR 指定的百分比不够大,无法容纳一行,SQL Server
将在内部使用允许的最小值替代该百分比。

l FILLFACTOR =*fillfactor,*指定在 SQL Server
创建索引的过程中,各索引页叶级的填满程度。如果某个索引页填满,SQL Server
就必须花时间拆分该索引页,以便为新行腾出空间,这需要很大的开销。对于更新频繁的表,选择合适的
FILLFACTOR 值将比选择不合适的 FILLFACTOR
值获得更好的更新性能。FILLFACTOR 的原始值将在
sysindexes 中与索引一起存储。如果指定了 FILLFACTOR,SQL Server
会向上舍入每页要放置的行数。例如,发出 CREATE CLUSTERED INDEX
…FILLFACTOR = 33 将创建一个 FILLFACTOR 为 33% 的聚集索引。假设 SQL
Server 计算出每页空间的 33% 为 5.2 行。SQL Server
将其向上舍入,这样,每页就放置 6 行。用户指定的 FILLFACTOR 值可以从 1 到
100。如果没有指定值,默认值为 0。如果 FILLFACTOR 设置为
0,则只填满叶级页。可以通过执行 sp_configure 更改默认的 FILLFACTOR
设置。只有不会出现 INSERT 或 UPDATE 语句时(例如对只读表),才可以使用
FILLFACTOR 100。如果 FILLFACTOR 为 100,SQL Server 将创建叶级页 100%
填满的索引。如果在创建 FILLFACTOR 为 100% 的索引之后执行 INSERT 或
UPDATE,会对每次 INSERT 操作以及有可能每次 UPDATE 操作进行页拆分。如果
FILLFACTOR 值较小(0 除外),就会使 SQL Server
创建叶级页不完全填充的新索引。例如,如果已知某个表包含的数据只是该表最终要包含的数据的一小部分,那么为该表创建索引时,FILLFACTOR
为 10 会是合理的选择。FILLFACTOR
值较小还会使索引占用较多的存储空间。表2说明如何在已指定 FILLFACTOR
的情况下填充索引页。

表2  填充索引页说明

FILLFACTOR

中间级页

叶级页

0

一个可用项

100% 填满

1% -99

一个可用项

<= FILLFACTOR% 填满

100%

一个可用项

100% 填满

l SORT_IN_TEMPDB = { ON |
OFF },指定用于生成索引的中间排序结果将存储在 tempdb 数据库中。如果
tempdb 与用户数据库不在同一磁盘集,则此选项可能减少创建索引所需的时间,但会增加创建索引时使用的磁盘空间。

l IGNORE_DUP_KEY = { ON |
OFF },控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。如果为索引指定了
IGNORE_DUP_KEY,并且执行了创建重复键的 INSERT 语句,SQL Server
将发出警告消息并忽略重复的行。如果没有为索引指定 IGNORE_DUP_KEY,SQL
Server 会发出一条警告消息,并回滚整个 INSERT 语句。表3显示何时可使用
IGNORE_DUP_KEY。

表 3  IGNORE_DUP_KEY使用情况

索引类型

选项

聚集

不允许

唯一聚集

允许使用 IGNORE_DUP_KEY

非聚集

不允许

唯一非聚集

允许使用 IGNORE_DUP_KEY

l STATISTICS_NORECOMPUTE = { ON |
OFF},指定过期的索引统计不会自动重新计算。若要恢复自动更新统计,可执行没有
NORECOMPUTE 子句的 UPDATE STATISTICS。

l DROP_EXISTING = { ON |
OFF },指定应除去并重建已命名的先前存在的聚集索引或非聚集索引。指定的索引名必须与现有的索引名相同。因为非聚集索引包含聚集键,所以在除去聚集索引时,必须重建非聚集索引。如果重建聚集索引,则必须重建非聚集索引,以便使用新的键集。为已经具有非聚集索引的表重建聚集索引时(使用相同或不同的键集),DROP_EXISTING
子句可以提高性能。DROP_EXISTING 子句代替了先对旧的聚集索引执行 DROP
INDEX 语句,然后再对新的聚集索引执行 CREATE INDEX
语句的过程。非聚集索引只需重建一次,而且还只是在键不同的情况下才需要。如果键没有改变(提供的索引名和列与原索引相同),则
DROP_EXISTING
子句不会重新对数据进行排序。在必须压缩索引时,这样做会很有用。无法使用
DROP_EXISTING
子句将聚集索引转换成非聚集索引;不过,可以将唯一聚集索引更改为非唯一索引,反之亦然。

l ONLINE = { ON |
OFF },当设置为ON的时候,长项表锁不允许对底层表进行查询或者更新操作。当设置为OFF的时候,应用表锁机制。默认值为OFF。

l ALLOW_ROW_LOCKS = { ON | OFF
},当设置为ON,当访问索引时,支持行级锁。当设置为OFF时,不使用行级锁。默认为ON。

l ALLOW_PAGE_LOCKS = { ON | OFF
},当设置为ON,当访问索引时,支持页级锁。当设置为OFF时,不使用页级锁。默认为ON。

l MAXDOP = number_of_processors,覆盖“max degree of
parallelism”配置选项的值。使用“max degree of
parallelism”限制在并行计划执行过程中使用的处理器数量,默认值为0,使用实际可用的CPU,取值为1表示压缩并行计划生成;设置大于1的数字,表示在单个查询过程中使用的处理器的最大数量。

例如,下面给出创建索引的SQL语句。

USE [EAMS]

GO

CREATE NONCLUSTERED INDEX [ix_name] ON [dbo].[mrBaseInf]

(

[EmpName] ASC

)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]


8.LOG ON   

  LOG ON 选项允许指定哪些文件需要日志,以及这些日志位于什么位置。

 

3.1  视图基础

视图(也称虚表)是用户查看数据表中数据的一种方式,用户可以通过它来浏览表中感兴趣的部分或全部数据。而数据库的物理存储位置仍然在表中,这些表称为视图的基表。视图可以从一个或多个基表中派生,也可以从其他视图中派生。需要注意,视图不是数据表,它仅是一些SQL查询语句的集合,作用是按照不同的要求从数据表中提取不同的数据。图27显示了在两个表上所创建的一个视图。

 

 图片 28

 

 

图 27  视图示例

使用视图,最大的优势如下:

l 限定特定的数据,视图让用户能够着重于他们所感兴趣的特定数据和所负责的特定任务。不必要的数据可以不出现在视图中。这同时增强了数据的安全性,因为用户只能看到视图中所定义的数据,而不是基础表中的数据。

l 简化数据操作,视图可以简化用户操作数据的方式。可将经常使用的联接、投影、联合查询和选择查询定义为视图,这样,用户每次对特定的数据执行进一步操作时,不必指定所有条件和限定。例如,一个用于报表目的,并执行子查询、外联接及聚合以从一组表中检索数据的复合查询,就可以创建为一个视图。视图简化了对数据的访问,因为每次生成报表时无须写或提交基础查询,而是查询视图。

l 自定义数据,视图允许用户以不同的方式查看数据,即使他们同时使用相同的数据时也如此。这在具有不同目的和技术水平的用户共享同一个数据库时尤为有利。例如,可定义一个视图以仅检索由客户经理处理的客户数据。视图可以根据使用该视图的客户经理的登录
ID 决定检索哪些数据。

l 导出和导入数据,可使用视图将数据导出至其它应用程序。例如,希望将进销存数据库中的销售数据导出到Excel中执行分析操作,可以为销售表创建一个视图,然后从视图中使用数据导出服务,将数据复制到Excel表中。

l 组合分区数据,Transact-SQL UNION
集合运算符可在视图内使用,以将来自不同表的两个或多个查询结果组合成单一的结果集。这在用户看来是一个单独的表,称为分区视图。例如,如果一个表含有北京的销售数据,另一个表含有上海的销售数据,即可从
UNION
创建这两个表的视图。该视图代表了这两个区域的销售数据。用分区视图时,首先创建几个相同的表,指定一个约束以决定可在各个表中添加的数据范围。视图即使用这些基表创建。当查询该视图时,SQL
Server
自动决定查询所影响的表,并仅引用这些表。例如,如果一个查询指定只需要北京的销售数据,则
SQL Server
只读取含有北京销售数据的表,而并不访问其余的表。分区视图可基于来自多个异类源——如远程服务器——的数据,而不仅仅局限于同一数据库中的表。例如,要将分别存储组织中不同区域数据的几台远程服务器上的数据组合起来,可以创建分布式查询,从每个数据源中检索数据,然后基于这些分布式查询创建视图。所有查询都只从包含查询所请求数据的远程服务器上读取表中的数据,其它在视图中由分布式查询引用的服务器均不被访问。通过使用分区视图,数据的外观象是一个单一表,且能以单一表的方式进行查询,而无须手动引用真正的基础表。

到目前为止,视图所存在的最大问题是性能问题。那些不愿意使用视图的开发人员正确的指出了问题的所在:视图不是预编译的,所以它们会对性能产生较大的影响。当在SQL
select语句中引用视图时,SQL Server需要通过以下这些步骤来解析视图:

l 将调用视图的SQL
select语句和它引用的视图所包含的select语句合并为单个的查询。

l 必须对这个新的查询进行解析。如果在表名中没有指定所有者名字,就必须正确地做出应该引用哪个表的决定。例如,如果在数据库中存在着两个表,dbo.Client和bob.Client,那么,在Bob的查询中引用Client表的就应当是bob.Client,而在其它人的查询中引用的Client表就应当是dbo.Client。

l 必须进行安全性的处理和检查。如果从视图到基础表的所有权链是一致的,那么不管对视图有权限的用户是否对相应基础表拥有权限,他或她都可以通过视图来引用基础表(假定这个视图的所有者拥有对基础表的相应权限)。尽管如此,如果所有权链是断开的,或者在要通过视图对基础表进行更新的情况下,用户就必须拥有对所有权链中每个对象的权限才能够进行操作。所以检查安全性的工作会对性能产生较大的影响。

l 如果以前已经执行过具有同样类型的参数的查询,并且在现在处理的查询中包含了所有者名字,就可以利用SQL
Server的自动参数化功能(auto-parameterization),重用以前存储的查询执行计划。如果没有为查询或者视图所包含的表指定所有者名字,或者是第一次执行合并后的查询,查询优化器就必须求出一个查询执行计划。

l 执行合并后的查询。

所以,视图究竟会对性能产生多大的影响呢?根据经验,写的好的存储过程比写的好的视图大约要快10%-20%。当然,编写存储过程需要使用很多的参数。


6.MAXSIZE  

  允许数据库的最大大小。

2、        游标定义语法:
A:SQL-92游标定义语法
语法:DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
                FOR select_statement
      [FOR {READ ONLY | UPDATE [OF column_list]}]
说明:1)cursor_name为所定义的光标名称。
2)insensitive说明定义的光标使用SELECT语句结果集合的临时拷贝,
  禁止应用程序通过光标对其基表进行修改。
3)SCROLL选项指出所定义光标的数据操作可以使用以下所有选项:
 ?FIRST:读取光标中的第一行数据;
  ?NEXT:  读取光标当前位置中的下一行数据;
 ?PRIOR: 读取光标当前位置中的上一行数据;
 ?LAST:  读取光标中的最后一条数据;
 ?RELATIVE n
:读取光标当前位置之前(n为负数)或之后(n为正数)的第n行资料;
  ?ABSOLUTE n: 读取光标中的第n行数据。
          没有说明SCROLL选项时,DECLARE所定义的光标只能使用NEXT选项,即每次只能读取下一行资料。
         4)select_statement为SELECT查询语句,它定义光标的结果集合,但其中不能使用COUMPUTE、COMPUTE BY、FOR BORWSE和INTO等关键词。
         5)READ
ONLY选项说明所定义的光标为只读光标,它禁止UPDATE语句和DELETE语句通过光标修改基表中的数据。
         6)UPDATE [OF
column_list]:可以通过光标修改其基表数据,其中可修改的列用column_list参数列出。如果只指定UPDATE关键词,而省略OF
column_list参数时,说明非只读光标中的所有列均可以修改。
        注意:光标声明语句中,如果有下列条件之一时,无论是否指定INSENSITIVE选项,系统将自动把所建立的光标定义为INSENSITIVE光标:
1)       
SELECT语句中使用了DISTINCT、UNION、GROUP BY或HAVING等关键词。
2)        SELECT语句的选择列表中包含有集合表达式。
3)        所有光标基表均没有建立唯一索引,并且光标具有SCROLL属性时。
4)        SELECT语句中包含有ORDER BY子句,而ORDER
BY子句指定的列又不具备和唯一标识功能。
B:Transact-SQL游标定义语法
    语法:DECLARE cursor_name CURSOR
               [LOCAL | GLOBAL]
               [FORWARD_ONLY | SCROLL]
               [STATIC | KEYSET | DYNAMIC]
               [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
               FOR select_statement
               [FOR UPDATE [OF column_list]]
    说明:1)其中,cursor_name、SCROLL、UPDATE [OF
column_list]参数与SQL-92语法格式定义中同名参数的作用相同。READ_ONLY与SQL-92定义中的READ_ONLY选项的作用相同。
    2)LOCAL和GLOBAL选项分别说明所定义的游标为局部游标或全局游标。
      局部游标的作用域为定义游标的批、存储过程或触发器
      全局游标的作用域为当前连接,在作用域外游标是不可见的。 
    3)FORWARD_ONLY选项指所定义的游标的数据提取操作只能前滚,即FETCH语句只能使用NEXT选项。
    4)STATIC与SQL-92定义中的INSENSITIVE关键字的功能相同,它将游标定义为静态游标。
    5)KEYSET关键字定义一个键集驱动游标,键集游标中的数据行及其顺序是固定的。
    6)DYNAMIC将游标定义为动态游标,可以随时看到游标结果集中被修改的数据,不能使用ABSOLUTE提取选项定位游标。
        7) SCROLL_LOCKS选项要求SQL
Server在将数据读入游标时,锁定基表中的数据行,以确保以后能通过游标成功对基表进行定位删除和修改。
    8)OPTIMISTIC说明不锁定基表的数据行,当应用程序通过游标对基表进行修改时,首行检测游标填充之后表中的数据是否被修改,如果被修改则停止当前操作。
   
9)select_statement为定义游标集合的SELECT语句,其中不能包含COMPUTE、COMPUTE
BY、FOR BROWSE和不INTO关键字。
 注意:游标声明语句中,如果有下列条件之一时,无论是否指定STATIC选项,系统将自动把所建立的光标定义为静态光标:
1)        SELECT语句中使用了DISTINCT、UNION、GROUP BY或HAVING等       
    
    关键词。       
2)SELECT语句的选择列表中包含有集合表达式。
3)所有光标基表均没有建立唯一索引,而又要求建立键值游标。
4)SELECT语句中包含有ORDER BY子句,而ORDER
BY子句指定的列又不具备唯一标识功能,所定义的动态游标将被转换为键集游标,不能转换为键集游标时,将转换为静态游标。
3、        填充游标:
    定义游标后,使用OPEN语句可打开并填充游标。OPEN语句的语法格式:
    OPEN {{[GLOBAL] cursor_name}|cursor_variable_name}
    
   其中cursor_name参数或cursor_variable_name变量指所打开和填充的游标
名称。
@@CURSOR_ROWS全局变量
 1)-m说明游标以异方式填充,m为当前键集中已填充的行数;
 2)-1游标为动态游标,游标中的行数是动态变化的。
 3)0指定的游标未打开,或是所打开的游标已经关闭或释放。
 4)n游标被全部填充,返回值为游标中的和数。

3  视图

视图是关系数据库的一个重要概念,它为数据呈现提供了更多的表现形式,并提供了数据的访问控制能力,让用户只看到他们权限限制范围内的数据,保护企业的敏感数据。本节将结合企业信息平台数据库的实例,介绍视图的基本概念,以及如何使用多种方式创建、修改、删除视图。

CREATE TABLE Test            --默认在当前所在的数据库创建
(
    Test_id INT NOT NULL,                          --第一列
    Test_Name nvarchar(50) NOT NULL,     --第二列
    Test_Age nvarchar(50) null                   --第三列
)

3.NAME

  一个逻辑名称,即SQL
Server在内部使用该名称引用该文件。当需要修改数据库大小时,需要使用这个名称

图片 29ALTER DATABASE MYDB2
图片 30        ADD FILE(
图片 31                          NAME = MYDB2_P3_dat,
图片 32                FILENAME = ‘c:\mssql7\data\MYDB2_P3.ndf’,
图片 33                          SIZE = 10MB,
图片 34                          MAXSIZE = 100MB,
图片 35                          FILEGROWTH = 2MB
图片 36                          )
图片 37GO
图片 38

2.3  查看和修改索引

当在数据库上创建了索引之后,可以在SQL Server Management
Studio中查看和修改索引,为此,可以采用SQL Server Management
Studio的图形工具或者使用Transact-SQL语句修改索引。

(1)使用SQL Server Management Studio图形工具查看和修改索引

使用SQL Server Management Studio图形工具查看和修改索引步骤如下:

1. 打开SQL Server Management
Studio,在“对象资源管理器”视图中展开“Database”节点,然后选择某个数据库,如选择数据库“EAMS”,双击“表”节点,展开表,选择“索引”节点,如图21所示。

图片 39

 

 

 

 

图 21  选择“Index”节点

2. 展开“索引”节点,可以看到在表上所创建的索引,如图22所示。

 

 图片 40

 

 

图 22  展开“索引”

3. 选择某个索引,单击鼠标右键选择“属性”菜单项,打开“索引属性”对话框,用户可以查看和修改索引选项,如图23所示。

 

 图片 41

 

 

图 23  查看和修改索引

1. 当修改完成之后,单击“确定”按钮,保存索引修改。

(2)使用Transact-SQL语句查看和修改索引

当需要修改索引的选项时,可以使用ALTER INDEX语句完成,其语法格式如下:

 

 

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 
          | [ PARTITION = partition_number 
                [ WITH ( <single_partition_rebuild_index_option>
                        [ ,...n ] )
                ] 
            ]
        ]
    | DISABLE
    | REORGANIZE 
        [ PARTITION = partition_number ]
        [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ]
    table_or_view_name
}

<rebuild_index_option > ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = number_of_processors
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = number_of_processors
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS= { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

 

 

参数说明请参考Create Index语句语法的参数说明。

如果需要修改索引的名称,可以使用sp_rename存储过程完成,其语法格式如下:

sp_rename [ @objname = ] ‘object_name’ , [ @newname = ] ‘new_name’

    [ , [ @objtype = ] ‘object_type’ ] ;

其中:

l [ @objname =] ‘object_name‘,表示对象名;

l [ @newname =] ‘new_name‘,表示新的名称。

l [ @objtype =] ‘object_type**‘**,在此为“INDEX”。

返回0,表示成功,返回非零表示失败。

当需要查看索引时,SQL
Server提供了几个视图和函数,用于显示索引元数据信息。例如,可以显示特定表上的索引类型、索引选项和索引整个空间使用情况。

用于返回索引元数据的视图包括:

l sys.indexes ,包括索引类型、文件组或者分区模式ID、索引当前选项的设置。

l sys.index_columns ,包括列ID、在索引中的位置、类型和排序规则;

l sys.stats ,同索引相关的统计信息;

l sys.stats_columns ,同统计相关的列ID;

l sys.xml_indexes,XML索引类型、路径表达式、辅助类型和描述信息。

同时,SQL Sever还提供了几个函数,用于返回元数据:

l sys.dm_db_index_physical_stats (Transact-SQL)
,显示索引规模和分段统计信息;

l sys.dm_db_index_operational_stats (Transact-SQL)
,显示当前索引和表I/O统计信息;

l INDEXKEY_PROPERTY ,显示在索引中索引列的位置和列排序规则;

l INDEXPROPERTY ,显示索引类型、当前设置;

l INDEX_COL,显示索引的键列名。

注意如果nvarchar不设置长度,默认为1。

4.FILENAME

  实际的操作系统文件在磁盘的名字,如果不写默认放在安装SQL
Server的文件夹中,默认的数据库文件是.mdf后缀,日志是.ldf文件。

f.基于多个条件选择查询结果。
       
   在WHERE语句中,可以用逻辑运算符来连接多个条件,构成一个复杂条件  
           进行查询。主要有三个逻辑运算符:
           1 AND
:它连接两个条件,如果两个条件都成立,则组合起来的条件成立。
           2
OR  :它连接两个条件,如果其中一个条件成立,则组合条件成立。
           3 NOT :它引出一个条件,将该条件的值取反。         
             查询没有发生在’2002-04-01′ 和 ‘2002-05-22’之间的资料
             select *  from cwk where rq not between ‘2002-04-01’ and
‘2002-05-22’ and djbh like ‘xsa%’
             查询发生在’2002-04-01′ 和 ‘2002-05-22’之间的资料
             select  * from cwk  where rq  between ‘2002-04-01’ and
‘2002-05-22’ and djbh like ‘xsa%’
             查询发生在小于等于’2002-04-01′ 和大于等于
‘2002-05-22’的资料
              select * from cwk
                where  rq<=’2002-04-01′ or rq>=’2002-05-22′
   g.查询结果排序
     格式:
       SELECT select_list
              FROM   talbe_source
              WHERE  search_condition
              ORDER BY order_expression [ASC  | DESC]
         
其中,order_expression是排序依据的列名,可以有多个列名。ASC按升      
          序排序,DESC按降序排序,默认为ASC
     查询满足WHERE条件的资料,按降序排列。  
          select  * from cwk  where rq  between ‘2002-04-01’ and
‘2002-05-22’ and djbh like ‘xsa%’
                order by rq desc
 (3)资料分组(northwind数据库)
      a. GROUP BY
       
 利用GROUP BY可以按一定的条件对查询到的结果进行分组,再对每一组数   
         据计算统计。
     格式:SELECT select_list
               FROM table_source
               WHERE search_condition
               GROUP BY group_by_expression
          比如:查看每一种卖出商品的总数.
        select spid,sum(shl) from ywmxk
            group by spid
   b.HAVING
    
   HAVING子句用来向使用GROUP BY子句的查询中添加过滤准则,语法与WHERE 
 
        一样,但WHERE是针对单个行而言,HAVING是针对一组而言。
    区别:1、查询时,先滤掉不满足WHERE中条件的记录,而HAVING子句在分
                 组之后使用。
       2、HAVING可以在子句中包含聚合函数,但WHERE 不行。      
          比如:查看每一种卖出商品的总数,卖出数量大于30.
          select spid,sum(shl) as shl from ywmxk
                group by spid
                having sum(shl)>30
      c.COMPUTE 与COMPUTE BY
        COMPUTE子句可以用来计算汇总资料。
    比如:查看日期1994/09/13以后卖出商品的总数,
         select spid,shl as shl from ywmxk  where djbh like
‘jha%’
                compute sum(shl)
        COMPUTE BY可以按给定的条件将查询结果分组,并为每组计算汇总资料 
    
             select spid,shl from ywmxk where djbh like ‘jha%’
                     order by spid
                     compute sum(shl) by spid   
    若使用了COMPUTE BY,则必须使用ORDER BY,并且COMPUTE BY后面出现 
         
        的列的顺序必须与ORDER BY后出现的顺序相同。
 (4)T_SQL中的子查询
    子查询有两种类型:简单型和关联型。
          
 子查询是指SELECT子句嵌在另一个T-SQL语句中,一般情况下,子查      
    
   询用于另一个SELECT,INSERT,UPDATE或者DELETE语句中的WHERE或HAVING  
        短语中。
   a.简单子查询
    例如:查询销售商品数量大于100的销售出库单信息
select djbh,hsje from cwk where  djbh in
(select djbh as shl from ywmxk where djbh like ‘xsa%’
group by djbh  having sum(shl)>100 ))
      b.联结和子查询
         例如:查询销售单据信息同时显示出单位名称
           select a.djbh,a.hsje,b.dwmch from cwk a join mchk b  
              on a.dwbh=b.dwbh
               where a.djbh like ‘xsa%’
    
   在大多情况下,在SELECT语句中的联结比一个等效子查询便容易,效率高,我 
    
      们应尽可能地使用联结来代替子查询以取得更好的性能。
      c.为什么有时必须使用子查询。
    例如:select a.djbh,a.hsje,b.dwmch from cwk a join mchk b  
              on a.dwbh=b.dwbh where a.djbh like ‘xsa%’   
             and  a.hsje<(select avg(hsje) from cwk where
a.djbh  like ‘xsa%’)
       因为我们不能比较聚合值--avg(hsje),以及非聚合值hsje.
  
   (6)外联结,交叉联结和自联结。
   a外联结:
对于绝大多数的查询执行,当用户在多个表检索资料时标准的内部   
             
 联结就足够了。但有些情况是当用户要检索的信息在两个表中没有公  
               共资料时,使用外联结和交叉联结是很有用的。
    外联结的类型:左联结,右联结,全外联结。
           左联结和右联结的不同之处仅在于FROM短语中表排列次序不同。
全外联结是同时使用左联接和右联接。
   例1 使用左联结选择所有商品及其销量。如果没有卖出我们也想看一看。
       select a.spid,a.spmch,isnull(sum(b.shl),0) as shl
from spkfk a left join ywmxk b on a.spid=b.spid
               where b.djbh like ‘xsa%’
               group by a.spid,a.spmch
          如果使用内联结没有卖出的商品的信息就不会被显示出来,
     使用isnull函数的目的:把NULL变为0。ISNULL(check_expression, 
 
               replacement_value).
      例2 只选择没有卖出的商品名称。
             select a.spid,a.spmch,isnull(sum(b.shl),0) as shl
from spkfk a left join ywmxk b on a.spid=b.spid
                where b.djbh like ‘xsa%’ and b.spid is null
                group by a.spid,a.spmch

5.1  游标基础

游标是从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数据指针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数据进行各种操作,然后将操作结果写回数据表中。

由 SELECT 语句返回的行集包括所有满足该语句 WHERE
子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序,特别是交互式联机应用程序,并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。

游标通过以下方式扩展结果处理:

l 允许定位在结果集的特定行。

l 从结果集的当前位置检索一行或多行。

l 支持对结果集中当前位置的行进行数据修改。

l 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。

l 提供脚本、存储过程和触发器中使用的访问结果集中的数据的 Transact-SQL
语句。

Microsoft SQL Server支持ODBC、ADO 和 DB-Library
定义的四种游标类型。这些游标检测结果集变化的能力和消耗资源(如在
tempdb 中所占的内存和空间)的情况各不相同。游标仅当尝试再次提取行时才会检测到行的更改。数据源没有办法通知游标当前提取行的更改。游标检测这些变化的能力也受事务隔离级别的影响。因此,在使用的时候,需要加以注意。

Microsoft SQL Server 支持三种游标实现:

Transact-SQL 游标,基于 DECLARE CURSOR 语法,主要用在 Transact-SQL
脚本、存储过程和触发器中。Transac-SQL
游标在服务器上实现并由从客户端发送到服务器的 Transact-SQL
语句管理。它们还包含在批处理、存储过程或触发器中。

应用编程接口(API)服务器游标,支持 OLE DB、ODBC 和 DB-Library
中的 API 游标函数。API 服务器游标在服务器上实现。每次客户应用程序调用
API 游标函数时,SQL Server OLE DB 提供程序、ODBC 驱动程序或 DB-Library
动态链接库(DLL)就把请求传送到服务器,以便对 API 服务器游标进行操作。

客户端游标,由 SQL Server ODBC 驱动程序、DB-Library DLL 和实现 ADO
API 的 DLL
在内部实现。客户端游标通过在客户端高速缓存所有结果集行来实现。每次客户应用程序调用
API 游标函数时,SQL Server ODBC 驱动程序、DB-Library DLL 或 ADO DLL
就对高速缓存在客户端中的结果集行执行游标操作。

由于 Transact-SQL 游标和 API
服务器游标都在服务器端实现,它们一起被称为服务器游标。不要混合使用这些不同类型的游标。如果执行一个来自应用程序的
DECLARE CURSOR 和 OPEN 语句,首先需要把 API
游标的特性设置为默认值。如果将 API
游标的特性设置非默认值的其它值,然后又执行 DECLARE CURSOR 和 OPEN
语句,您事实上是在要求 SQL Server 在 Transact-SQL 游标上映射 API
游标。例如,不要将 ODBC
特性设置为调用将键集驱动游标映射为结果集,然后又使用语句句柄执行 DECLARE
CURSOR 和 OPEN 以调用 INSENSITIVE 游标。

删除约束: