Menu
Woocommerce Menu

数据库的所有资料、对象和数据库操作日志均存储在这些操作中,创建数据库

0 Comment


一.CREATE语句(创建)

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

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

SQL Server数据库基础

当创建了数据库之后,下一步就需要设计数据库对象。SQL
Server能够创建多种数据库对象,如表、索引、视图、存储过程、游标、触发器等。本章将对其基础知识、相关的操作进行详细介绍。

本章主要内容:

l 表

l 索引

l 视图

l 存储过程

l 游标

l 触发器

创建数据库:

1.创建DataBase

图片 1

在SQL Server中建立文件和文件组时,应注意以下两点:
?       
每个文件或文件组只能属于一个数据库,每个文件也只能成为一个文件组的成员,文件和文件组不能跨数据库使用;
?       
日志文件是独立的,它不能成为文件组的成员。也就是说,数据库的资料内容和日志内容不能存入相同的文件或文件组。

数据库文件

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 数据库时。

1  表

本节我们介绍数据表的基础知识,以及一些基本的操作:创建、修改、删除操作。需要提醒的是,创建数据表是创建数据库的一项基本操作。在实际的项目开发过程中,在创建数据表的时候,需要注意使用五种范式对表的数据列进行划分,得到表的逻辑结构,然后通过SQL
Server提供的工具加以实现其物理结构。

 1 CREATE DATABASE Test              --要创建的数据库名称
 2 ON PRIMARY
 3 (
 4     --数据库文件的具体描述
 5     NAME='Test_data',                        --主数据文件的逻辑名称
 6     FILENAME='E:\project\Test_data.mdf',    --主数据文件的物理名称
 7     SIZE=5MB,                                --主数据文件的初始大小
 8     MAXSIZE=100MB,                            --主数据文件的增长的最大值
 9     FILEGROWTH=15%                            --主数据文件的增长率
10 )
11 LOG ON
12 (
13     --日志文件的具体描述,各参数含义同上
14     NAME ='Test_log',                        
15     FILENAME='E:\project\Test_data.ldf',
16     SIZE=2MB,
17     FILEGROWTH=1MB
18 )
19 GO                                            --和后续的SQL语句分隔开

1.CONTAINMENT

  SQL Server 2012 新功能 , 默认值是OFF 。(太高级
书上也没有详细介绍)。

SQL Server中的数据库文件组有以下三种类型:
?       
主文件组:其中包数据库的主数据文件和不属于其它文件组的数据库文件,数据库系统表的所有页面存储在主文件组中;
?        用户定义文件组:数据库创建语句(CREATE
DATABASE)或修改语句(ALTER
DATABASE)中使用FILEGROUP关键词所指定的文件组;
?       
默认文件组:在创建数据库对象时,如果没有为它们指定文件组,它们将被存储在默认文件组中。可以使用ALTER
DATABASE语句修改数据库的默认文件组设置,但每个数据库同时最多只能有一个默认文件组。当数据库没有指定默认文件组时,主文件组将被作为默认文件
组使用。
由于默认文件组的特殊作用,所以在创建数据库对象时,即使不指定用户文件组,SQL
Server也能照常执行。

数据库文件组

为便于分配和管理,可以将数据库对象和文件一起分成文件组。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。
  • 一个文件只能是一个文件组的成员。
  • 事务日志文件不能属于任何文件组。

1.1  表基础

表是包含数据库中所有数据的数据库对象,表定义为列的集合,与电子表格相似,数据在表中是按行和列的格式组织排列的。每行代表唯一的一条记录,而每列代表记录中的一个域。例如,下面是SQL
Server提供的默认数据库Pubs中的sales表结构如图1所示。

图片 2

 

 

 

 

图1  “sales”表

该表包含行和列信息,其中行表示数据,列表示数据域(stor_id、ord_num、ord_date、qty、payterns、title_id)。

设计数据库时,应先确定需要什么样的表,各表中都有哪些数据以及各个表的存取权限等等。在创建和操作表过程中,对表进行更为细致的设计。创建一个表最有效的方法是将表中所需的信息一次定义完成,包括数据约束和附加成分。也可以先创建一个基础表,向其中添加一些数据并使用一段时间。这种方法可以在添加各种约束、索引、默认设置、规则和其它对象形成最终设计之前,发现哪些事务最常用,哪些数据经常输入。

最好在创建表及其对象时预先将设计写在纸上,设计时应注意:

l 表所包含的数据的类型。

l 表的各列及每一列的数据类型(如果必要,还应注意列宽)。

l 哪些列允许空值。

l 是否要使用以及何时使用约束、默认设置或规则。

l 所需索引的类型,哪里需要索引,哪些列是主键,哪些是外键。

当设计完成数据表之后,可以采用多种方式创建数据表,如在SQL Server
Management
Studio中使用图形界面创建数据库表,或者执行Transact-SQL语句创建数据库表。

表的每一列都有一组属性,如名称、数据类型、为空性和数据长度等。列的所有属性构成列的定义。可以使用数据库关系图在数据库表中直接指定列的属性。在数据库中创建表之前列应具备三个属性:列名、数据类型和数据长度。

其中:

2.ON

  ON用于两个地方,第一个是存储数据的文件的位置,第二个是存储日志的文件的位置。
ON 后面的 PRIMARY的概念:希望将所有的内容存放在一个文件里。

1.使用Transact-SQL语句建立数据库
  CREATE DATABASE 语句的语法格式为:

区(extent)

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

图片 3

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

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

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

1.2  创建企业信息平台的数据表

在了解了SQL Server数据表概念和列属性之后,下面就可以创建数据表。SQL
Server提供了多种方式创建数据表。如在SQL Server Management
Studio中采用图形向导创建数据表,也可以使用Transact-SQL语句创建数据表,下面分别加以介绍。

(1)使用SQL Server Management Studio创建数据库表

使用SQL Server Management Studio创建数据库表的步骤如下:

1. 打开SQL Server Management Studio,如图2所示。

 

 图片 4

 

 

图2  SQL Server Management Studio

2. 选择需要创建表的数据库,展开文件夹,选择“表”,单击鼠标右键,选择“新建表”,如图3所示。

 

 图片 5

 

 

图3  选择“新建表”菜单项

3. 输入列的名称、数据类型、长度、是否允许为空等属性,如图4所示。

 

 图片 6

 

 

图4 定义数据表

4. 当定义完成之后,单击工具栏上的按钮,保存该表,SQL Server Management
Studio将弹出对话框,要求输入表名,如图5所示。

 

 图片 7

 

 

图5  定义表名的对话框

5. 当定义了表名之后,单击“OK“按钮,保存该表,数据表创建完成。

(2)使用Transact-SQL创建数据库表

在SQL Server Management
Studio提供的查询分析器中,可以定义Transact-SQL的CREATE
TABLE语句创建数据表。其语法格式如下:

 

 

CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
    ( { <column_definition> | <computed_column_definition> }
        [ <table_constraint> ] [ ,...n ] ) 
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
        | " DEFAULT " } ] 
    [ { TEXTIMAGE_ON { filegroup | " DEFAULT " } ] 
[ ; ]

< column_definition > ::=
column_name <data_type>
    [ NULL | NOT NULL ]
    [ COLLATE collation_name ] 
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 

<data type> ::= 
[ type_schema_name . ] type_name 
    [ ( precision [ , scale ] | MAX | xml_schema_collection ) ] 
    sql_server_native_type | type_name 

<column_constraint> ::= 
[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor
          | WITH ( < index_option >[, ...n ] ) 


        ]
        [ ON { partition_scheme_name ( partition_column_name ) 
            | filegroup | " DEFAULT " } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<computed_column_definition> ::=
column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
            WITH FILLFACTOR = fillfactor 
          | WITH ( <index_option> [, ...n ] )
        ]
    | [ FOREIGN KEY ] 
        REFERENCES ref_table [ ( ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE } ] 
        [ ON UPDATE { NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK ( logical_expression ) 
    [ ON { partition_scheme_name ( partition_column_name ) 
        | filegroup | " DEFAULT " } ]
] 

< table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 


        (column [ ASC | DESC ] [ ,...n ] ) 
        [ 
            WITH FILLFACTOR = fillfactor 
           |WITH ( <index_option> [, ...n ] ) 
        ]
        [ ON { partition_scheme_name (partition_column_name)
            | filegroup | " DEFAULT " } ] 
    | FOREIGN KEY 
        ( column [ ,...n ] ) 
        REFERENCES ref_table [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression ) 
} 

<index_option> ::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY  = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS= { ON | OF} 
  | ALLOW_PAGE_LOCKS={ ON | OF} 
}

 

 

其中,各个参数的具体说明如下:

l database_name,是要在其中创建表的数据库名称。database_name 必须是现有数据库的名称。如果不指定数据库,database_name 默认为当前数据库。当前连接的登录必须在
database_name 所指定的数据库中有关联的现有用户 ID,而该用户 ID
必须具有创建表的权限。

l schema_name,是新表所属于的模式名。

l table_name,是新表的名称。表名必须符合标识符规则。数据库中的
owner.table_name 组合必须唯一。table_name 最多可包含 128
个字符,但本地临时表的表名(名称前有一个编号符 #)最多只能包含 116
个字符。

l column_name,是表中的列名。列名必须符合标识符规则,并且在表内唯一。以
timestamp 数据类型创建的列可以省略 column_name。如果不指定
column_name,timestamp 列的名称默认为 timestamp。

l computed_column_expression,是定义计算列值的表达式。计算列是物理上并不存储在表中的虚拟列。计算列由同一表中的其它列通过表达式计算得到。例如,计算列可以这样定义:cost
AS price *
qty。表达式可以是非计算列的列名、常量、函数、变量,也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。

l PERSISTED,定义SQL
Server物理存储计算值到表中,当计算列依赖的任何其他列值发生改变的时候,更新值。

l ON { <partition_scheme> | filegroup | “DEFAULT ” }
,定义表所在的分区模式或者文件组。如果选择< partition_scheme
>,表是一个分区表。如果定义为filegroup,表存储在文件组中。文件组必须位于数据库中。

l TEXTIMAGE_ON { filegroup| “DEFAULT “]} ,是表示 text、ntext 和
image 列存储在指定文件组中的关键字。如果表中没有 text、ntext 或
image 列,则不能使用 TEXTIMAGE ON。如果没有指定 TEXTIMAGE_ON,则
text、ntext 和 image 列将与表存储在同一文件组中。

l [ type_schema_name. ]
type_name,定义列的数据类型,以及所属于的模式,数据类型可以是:NativeSQL类型、CLR用户自定义类型。

l precision,定义数据类型的精度。

l scale,定义数据类型的刻度。

l MAX,只能用于varchar,
nvarchar和varbinary数据类型,存储2^31字节的字符串或者二进制数据,或者2^30的Unicode数据。

l xml_schema_collection
,只应用于XML数据类型,将XML模式同类型关联起来。在将一个XML列输入到模式中时,首先必须使用CREATE
XML SCHEMA COLLECTION语句在数据库中创建模式。

l DEFAULT,如果在插入过程中未显式提供值,则指定为列提供的值。DEFAULT
定义可适用于除定义为 timestamp 或带 IDENTITY
属性的列以外的任何列。除去表时,将删除 DEFAULT
定义。只有常量值(如字符串)、系统函数(如 SYSTEM_USER())或 NULL
可用作默认值。为保持与 SQL Server 早期版本的兼容,可以给 DEFAULT
指派约束名。

l constant_expression,是用作列的默认值的常量、NULL 或系统函数。

l IDENTITY,表示新列是标识列。当向表中添加新行时,Microsoft® SQL Server™
将为该标识列提供一个唯一的、递增的值。标识列通常与 PRIMARY KEY
约束一起用作表的唯一行标识符。可以将 IDENTITY 属性指派给
tinyint、smallint、int、bigint、decimal(p,0) 或
numeric(p,0) 列。对于每个表只能创建一个标识列。不能对标识列使用绑定默认值和
DEFAULT
约束。必须同时指定种子和增量,或者二者都不指定。如果二者都未指定,则取默认值
(1,1)。

l Seed,是装入表的第一行所使用的值。

l Increment,是添加到前一行的标识值的增量值。

l NOT FOR REPLICATION,表示当复制登录向表中插入数据时,不强制 IDENTITY
属性。复制的行必须保留发布数据库中所赋予的键值;NOT FOR REPLICATION
子句确保不向复制进程所插入的行赋予新的标识值。其它登录所插入的行仍然具有以通常的方式创建的新标识值。建议同时使用具有
NOT FOR REPLICATION 的 CHECK
约束,以确保赋予的标识值处于当前数据库所需的范围内。

l ROWGUIDCOL,表示新列是行的全局唯一标识符列。对于每个表只能指派一个
uniqueidentifier 列作为 ROWGUIDCOL 列。ROWGUIDCOL 属性只能指派给
uniqueidentifier 列。如果数据库兼容级别小于或等于 65,则 ROWGUIDCOL
关键字无效。ROWGUIDCOL
属性并不强制列中所存储值的唯一性。该属性也不会为插入到表中的新行自动生成值。若要为每列生成唯一值,那么或者在
INSERT 语句中使用 NEWID 函数,或者将 NEWID 函数指定为该列的默认值。

l collation_name,指定列的排序规则。排序规则名称既可以是 Windows
排序规则名称,也可以是 SQL
排序规则名称。collation_name 仅适用于数据类型为
char、varchar、text、nchar、nvarchar 及 ntext
的列。如果没有指定该参数,那么如果列的数据类型是用户定义的,则该列的排序规则就是用户定义数据类型的排序规则,否则就是数据库的默认排序规则。

l CONSTRAINT,是可选关键字,表示 PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN
KEY 或 CHECK
约束定义的开始。约束是特殊属性,用于强制数据完整性并可以为表及其列创建索引。

l constraint_name,是约束的名称。约束名在数据库内必须是唯一的。

l NULL | NOT NULL,是确定列中是否允许空值的关键字。从严格意义上讲,NULL
不是约束,但可以使用与指定 NOT NULL 同样的方法指定。

l PRIMARY
KEY,是通过唯一索引对给定的一列或多列强制实体完整性的约束。对于每个表只能创建一个
PRIMARY KEY 约束。

l UNIQUE,是通过唯一索引为给定的一列或多列提供实体完整性的约束。一个表可以有多个
UNIQUE 约束。

l CLUSTERED | NONCLUSTERED,是表示为 PRIMARY KEY 或 UNIQUE
约束创建聚集或非聚集索引的关键字。PRIMARY KEY 约束默认为
CLUSTERED,UNIQUE 约束默认为 NONCLUSTERED。在 CREATE TABLE
语句中只能为一个约束指定 CLUSTERED。如果在为 UNIQUE 约束指定 CLUSTERED
的同时又指定了 PRIMARY KEY 约束,则 PRIMARY KEY 将默认为 NONCLUSTERED。

l FOREIGN KEY …REFERENCES,是为列中的数据提供引用完整性的约束。FOREIGN
KEY 约束要求列中的每个值在被引用表中对应的被引用列中都存在。FOREIGN KEY
约束只能引用被引用表中为 PRIMARY KEY 或 UNIQUE 约束的列或被引用表中在
UNIQUE INDEX 内引用的列。

l  [ schema_name . ] referenced_table_name ],表示FOREIGN
KEY约束引用的表名及其所属于的模式名。

l ( ref_column[ ,… n] ),是 FOREIGN KEY
约束所引用的表中的一列或多列。

l ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT
},指定当要创建的表中的行具有引用关系,并且从父表中删除该行所引用的行时,要对该行采取的操作。默认设置为
NO ACTION。如果指定
CASCADE,则从父表中删除被引用行时,也将从引用表中删除引用行。如果指定 NO
ACTION,SQL Server 将产生一个错误并回滚父表中的行删除操作。

l ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT
},指定当要创建的表中的行具有引用关系,并且在父表中更新该行所引用的行时,要对该行采取的操作。默认设置为
NO ACTION。如果指定
CASCADE,则在父表中更新被引用行时,也将在引用表中更新引用行。如果指定 NO
ACTION,SQL Server 将产生一个错误并回滚父表中的行更新操作。

l CHECK,是通过限制可输入到一列或多列中的可能值强制域完整性的约束。

l NOT FOR REPLICATION,是用于防止在复制所使用的分发过程中强制 CHECK
约束的关键字。当表是复制发布的订户时,请不要直接更新订阅表,而要更新发布表,然后让复制进程将数据分发回订阅表。可以在订阅表上定义
CHECK 约束,以防用户修改订阅表。但是如果不使用 NOT FOR REPLICATION
子句,CHECK 约束同样会防止复制进程将修改从发布表分发给订阅表。NOT FOR
REPLICATION 子句表示对用户的修改(而不是对复制进程)强加约束。NOT FOR
REPLICATION CHECK
约束适用于被更新记录的前像和后像,以防在复制范围中添加记录或从复制范围中删除记录。将检查所有删除和插入操作;如果操作在复制范围内,则拒绝执行该操作。如果对标识符列使用此约束,则当复制用户更新标识列时,SQL
Server 将允许不必重新计算表标识列的种子值。

l logical_expression,是返回 TRUE 或 FALSE 的逻辑表达式。

l column
,是用括号括起来的一列或多列,在表约束中表示这些列用在约束定义中。

l [ASC | DESC],指定加入到表约束中的一列或多列的排序次序。默认设置为
ASC。

l n,是表示前面的项可重复 n 次的占位符。

l partition_scheme_name,表示定义文件组的分区模式名。

l  [ partition_column_name.],定义用于对一个表的数据进行分区的列名。

l WITH FILLFACTOR =fillfactor,定义SQL
Server如何使用索引页存储索引数据。用户自定义的填充因子值的范围为1到100。如果没有定义该值,默认为0。

l <index_option > ::=
,定义一个或者多个索引选项,对于选项的说明,请参考CREATE
INDEX语句的语法。

l PAD_INDEX = { ON | OFF }
,当设置为ON的时候,FILLFACTOR定义的空闲空间的百分比应用于索引的中间层页。当定义为OFF或者没有指定的时候,中间层页填充几乎整个空间,只保留存储最大索引一行数据的空间。默认值为OFF。

l FILLFACTOR =fillfactor,定义指示SQL
Server在创建和修改索引的时候,如何创建索引页的页层。取值范围为1到100,默认值为0。

l IGNORE_DUP_KEY = { ON | OFF }
,定义在对唯一集群索引执行多行INSERT事务的时候,出现重复键值的错误响应。当设置为ON时,如果行破坏了唯一索引,将显示一个警告消息,插入行失败。当设置为OFF的时候,如果行破坏唯一索引,将提供一个错误消息,整个INSERT语句回滚。当处理UPDATE语句的时候,IGNORE_DUP_KEY没有影响,默认值为OFF。

l STATISTICS_NORECOMPUTE = { ON | OFF }
,当设置为ON的时候,过期索引统计信息不会自动被重新计算。当设置为OFF的时候,自动更新统计信息,默认值为OFF。

l ALLOW_ROW_LOCKS = { ON | OFF }
,当设置为ON的时候,当访问索引的时候,支持行锁。数据库引擎确定何时使用行锁。如果设置为OFF,不使用行锁,默认值为ON。

l ALLOW_PAGE_LOCKS = { ON | OFF }
,当设置为ON的时候,当访问索引的时候,支持页锁。数据库引擎确定何时使用页锁。当设置为OFF的时候,不使用页锁。默认值为ON。

假设要求在企业信息平台数据库EAMS中创建数据表clCommunicationsList,如表1所示,表示个人的通信录信息,包括通信录ID、类别、人员ID、通信录人员姓名、性别、人员编码、Email地址、办公室电话、办公室传真、移动电话、地址等信息。

表1  clCommunicationsList表结构

字段名称

字段解释

数据类型

允许空

备注

CommunicationID

通信录ID

int

主键

CategoryName

通信录类别名称

Varchar(20)

 

EmpID

人员ID

int

外键

COMName

通信录人员名

Varchar(50)

 

Sex

性别

Bit

 

EmpCode

人员编码

Varchar(20)

 

Email

人员Email地址

Varchar(50)

 

OfficeTel

办公室电话

Varchar(20)

 

OfficeFax

办公室传真

Varchar(20)

 

Mobile

移动电话

Varchar(20)

 

Position

位置

Varchar(50)

 

Province

Varchar(32)

 

City

城市

Varchar(32)

 

District

Varchar(32)

 

Street

街道

Varchar(32)

 

PostCode

邮编

Varchar(20)

 

Tel1

电话1

Varchar(20)

 

Tel2

电话2

Varchar(20)

 

Note

注释

Varchar(1000)

 

创建clCommunicationsList表的Transact-SQL语句如下:

 

 

USE [EAMS]
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[clCommunicationsList](
    [CommunicationID] [int] IDENTITY(127,1) NOT NULL,
    [CategoryName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [EmpID] [int] NOT NULL,
    [COMName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [EmpCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Sex] [bit] NOT NULL,
    [Email] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [OfficeTel] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [OfficeFax] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Mobile] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Position] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
    [Province] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [City] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [District] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [Street] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
    [PostCode] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Tel1] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [Tel2] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [note] [varchar](1000) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_clCommunicationsList_DelFlag]  DEFAULT (0)
,
 CONSTRAINT [PK_clCommunicationsList] PRIMARY KEY CLUSTERED 
(
    [CommunicationID] ASC,
    [CategoryName] ASC,
    [EmpID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING ON
GO
SET ANSI_NULLS OFF

 

 

类似可以创建企业信息平台的企业表结构。

1.日志文件参数和最大容量为可选部分。

3.NAME

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

图片 8CREATE DATABASE database_name
图片 9[ ON [PRIMARY]
图片 10        [ <filespec> [,图片 11n] ]
图片 12        [, <filegroup> [,图片 13n] ]
图片 14]
图片 15[ LOG ON { <filespec> [,图片 16n]} ]
图片 17[ FOR LOAD | FOR ATTACH ]
图片 18<filespec> ::=
图片 19  ( [ NAME = logical_file_name, ]
图片 20  FILENAME = ‘os_file_name’
图片 21  [, SIZE = size]
图片 22  [, MAXSIZE = { max_size | UNLIMITED } ]
图片 23  [, FILEGROWTH = growth_increment] ) [,图片 24n]
图片 25<filegroup> ::=
图片 26FILEGROUP filegroup_name <filespec> [,图片 27n]
图片 28

页(data page)

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

图片 29

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

1.3  修改企业信息平台的数据表

当创建了表之后,根据特定情况,可能需要对所创建好的表进行修改操作,如修改列名、数据类型、类型长度、默认值等属性。修改表的列定义相对较为简单。可以在SQL
Server Management
Studio中采用图形方式,也可以采用Transact-SQL语句完成。例如,修改企业信息平台数据库表mrBaseInf,将办公室电话号码OfficeTel长度从50修改为20。

采用图形方式修改表的步骤如下:

1. 打开SQL Server Management
Studio,在“对象资源管理器”视图中,展开EAMS数据库,如图6所示。

 

 图片 30

 

 

图6  选择EAMS数据库

2. 选中“mrBaseInf”表,单击鼠标右键,选择“修改表”菜单项,如图7所示。

 

 图片 31

 

 

图7  修改表

3. 打开表的定义视图,如图8所示。

 

 图片 32

 

 

图8  mrBaseInf表结构

4. 选择“OfficeTel”列,在列属性视图中,将长度从50修改为20,如图9所示。

图片 33

 

 

 

 

图9  修改列定义

5. 如果还需要修改其它列定义,可以参考类似操作,单击工具栏上的按钮,保存修改即可。

2.数据库的名字最长为128个字符。

4.FILENAME

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

其中,database_name为新建数据库的逻辑名称,在一个SQL
Server上,必须保证各数据库名称是唯一的。
ON
子句显示指定存储数据库资料部分所使用的数据文件和文件组列表,PRIMARY关键词说明其后的数据文件属于主文件组。如果PRIMARY关键词未被指
定,则关键词CREATE
DATABASE后的第一个文件列表将成为主数据文件。<filespec>定义数据文件列表中各数据文件项,有多个数据文件项时,相互之间
以逗号分隔。
其中,logical_file_name参数指出数据文件的逻辑名称,数据文件的逻辑名称应用在Transact-SQL语句中。在同一个数据库中,必须保持数据文件的逻辑名称是唯一的。
os_file_name参数说明数据文件对应的操作系统文件名称,即数据文件的物理文件名称及其路径。
size
参数指定数据文件的初始长度,其单位为MB或KB,默认时为MB。对于主数据文件,其size参数的最小值应等于model数据库中主数据文件的长度。对
于其它数据文件,其长度最小为512KB。size参数默认时,对于辅数据文件和日志文件,SQL
Server将其长度设置为1MB,而对于主数据文件,SQL
Server将其长度设为model数据库中主数据文件的长度。
SQL
Server中,如果打开数据库的autoshrink选项,当数据库文件空间用尽时,系统将自动增加数据文件的大小。max_size参数定义数据文件
可以增加到的最大尺寸,其单位为MB或KB。如果未定义max_size参数,数据库文件的长度可根据需要一直增加,直到磁盘空间用尽为止。这时它等同于
MAXSIZE = UNLIMITED。
growth_increment参数说明数据文件空间的每次增加量,其单位为MB,KB或%,默认为
MB。使用%时说明数据文件每次增加的长度等于增加时文件现有长度的百分比,growth_increment参数的默认值为10%。用MB或KB表示
时,其增加值应为64KB或其倍数。
ON子句中的<filegroup>参数用于指出数据库的数据文件组,其中,filegroup_name为文件组名称。文件组中各文件的定义格式与上面介绍的数据文件的定义格式相同。
CREATE DATABASE语句中的LOG
ON子句用于定义数据库日志文件。各个日志文件的定义格式与数据文件相同。当未使用LOG
ON子句指定日志文件时,SQL
Server将自动为数据库建立一个日志文件,文件名称由系统产生,其长度等于数据库所有数据文件长度之和的25%。

管理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  

1.4  删除企业信息平台的数据表

当某些表不再需要的时候,可以删除数据库中的某些表。删除数据表的操作可以在SQL
Server Management Studio中,或者采用Transact-SQL语句完成。

(1)使用SQL Server Management Studio删除表

使用SQL Server Management Studio删除表的步骤如下:

1. 在SQL Server Management
Studio中的“对象资源管理器”视图中,选中需要删除的表,单击鼠标右键,如图10所示。

图片 34

 

 

 

 

图10 删除表

2. 选择“删除”菜单项,弹出“删除对象”对话框,单击“确定”按钮,删除表,如图11所示。

图片 35

 

 

 

 

图11  确定删除表

(2)使用Transact-SQL语句删除表

使用Transact-SQL语句删除表的语句是DELETE TABLE,语法如下:

DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]

    table_name [ ,…n ] [ ; ]

参数说明如下:

l database_name,表示表所在的数据库名。

l schema_name,表示表属于的模式名。

l table_name,需要删除的表名。

l n,表示在任何数据库中删除多个表。如果删除的表引用了另一个表的主键,则另一个表也被删除。

假定需要删除所创建的企业信息平台数据表mrBaseInf。可以采用Transact-SQL语句删除数据表,其语句如下:

USE [EAMS]

GO

DROP TABLE [dbo].[ mrBaseInf]

GO

USE [master]

GO

 

3.PRIMARY是一个关键字,指定主文件组中的文件。

5.SIZE

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

SQL Server创建一个数据库时要经过以下两个步骤:
使用model数据库拷贝初始化新建立的数据库,用户在model数据库中所建立的数据库对象也一并被拷贝到新建数据库中。此外,新建数据库还继承了
model中的各种数据库选项设置,如果model数据库选项设置被修改,它只影响修改后所建立的数据库,已经建立的数据库的各种选项不再发生变化了;
② 用空白页面填充数据库中的自由空间。
(1)在创建数据库时,如果省略了CREATE
DATABASE语句中的所有可选参数,即使用下面的语句格式,它所创建的数据库大小完全等同于model数据库:
CREATE DATABASE MYDB1
GO
 
 (2)下面例子在创建数据库MYDB2时指定文件组,其数据文件有主文件组和MYDB2_GROUP文件组组成。MYDB2数据库所包含的数据文件和日志文件,以及它们的参数如图所示:

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

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

dbcc extentinfo(数据库名,表名)

2  索引

数据库中90%的性能问题与索引/查询有关。索引机制是提升数据库性能的重要机制。SQL
Server提供了对索引的良好支持,提供了多种类型的索引机制,方便开发人员在适当的时候创建特定的索引。

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

6.MAXSIZE  

  允许数据库的最大大小。

图片 36CREATE DATABASE MYDB2
图片 37        ON
图片 38                PRIMARY(
图片 39                                NAME = MYDB2_P1_dat,
图片 40                                FILENAME = ‘c:\mssql7\data\MYDB2_P1.mdf’,
图片 41                                SIZE = 5,
图片 42MAXSIZE = 10,
图片 43FILEGROWTH = 20%
图片 44),
图片 45
图片 46(NAME = MYDB2_P2_dat,
图片 47                                  FILENAME = ‘c:\mssql7\data\MYDB2_P2.ndf’,
图片 48                                 SIZE = 5,
图片 49MAXSIZE = 10,
图片 50FILEGROWTH = 1MB
图片 51),
图片 52
图片 53FILEGROUP MYDB2_GROUP(
图片 54                 NAME = MYDB2_S1_dat,
图片 55                                  FILENAME = ‘c:\mssql7\data\MYDB2_S1.ndf’,
图片 56                 SIZE = 10,
图片 57                 MAXSIZE = 50,
图片 58                 FILEGROWTH = 10
图片 59                 ),
图片 60
图片 61(NAME = MYDB2_S2_dat,
图片 62FILENAME = ‘c:\mssql7\data\MYDB2_S2.ndf’,
图片 63SIZE = 20,
图片 64MAXSIZE = 100,
图片 65FILEGROWTH = 20
图片 66)
图片 67Go
图片 68

2.1  索引基础

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

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

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

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

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

 

 图片 69

 

图 12  索引结构

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

(1)非聚集索引

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

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

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

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

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

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

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

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

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

 

 图片 70

 

 

图 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说明聚集索引的结构。

 

 图片 71

 

 

图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索引可以提高查询性能。

5.NAME指定数据库的逻辑名称,这是在SQL
Server中使用的名称,是数据库在SQL Server中的标识符。

7.FILEGROWTH

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

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 语句的语法格式为:

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所示。

 

 图片 72

 

 

图 15   新建索引

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

 

 图片 73

 

 

图 16  “常规”视图

在该对话框中,定义:

l 索引的名称;

l 索引的类型;

l 索引列。

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

 

 图片 74

 

 

图 17  定义索引列

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

 

 图片 75

 

 

图 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索引时,无法添加列,当选择非聚集索引类型的时候,可以添加索引列。

 

 图片 76

 

 

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

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

 

 图片 77

 

 

图 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]

6.FILENAME指定数据库所在文件的操作系统文件夹名称和路径,该操作系统文件名和NAME的逻辑名称一一对应。

8.LOG ON   

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

图片 78ALTER DATABASE database
图片 79{    ADD FILE <filespec> [,图片 80n] [TO FILEGROUP filegroup_name]
图片 81    | ADD LOG FILE <filespec> [,图片 82n]
图片 83    | REMOVE FILE logical_file_name 
图片 84    | ADD FILEGROUP filegroup_name
图片 85    | REMOVE FILEGROUP filegroup_name
图片 86    | MODIFY FILE <filespec>
图片 87    | MODIFY FILEGROUP filegroup_name filegroup_property
图片 88}
图片 89<filespec> ::=
图片 90(NAME = logical_file_name
图片 91  [, FILENAME = ‘os_file_name’ ]
图片 92  [, SIZE = size]
图片 93  [, MAXSIZE = { max_size | UNLIMITED } ]
图片 94  [, FILEGROWTH = growth_increment] )
图片 95

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所示。

图片 96

 

 

 

 

图 21  选择“Index”节点

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

 

 图片 97

 

 

图 22  展开“索引”

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

 

 图片 98

 

 

图 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,显示索引的键列名。

7.SIZE指定数据库的初始容量大小。

9.COLLATE

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

 

 

图片 99图片 100

 

其中,database为待修改的数据库名称。
ADD FILE子句指出向数据库中添加数据文件,TO
FILEGROUP说明新添加数据文件所属的文件组名称。
ADD LOG FILE子句指出向数据库中新添加的日志文件项。
REMOVE FILE子句指出从数据库中删除文件,在删除后,SQL
Server将该文件对应的物理文件一并从操作系统中删除。
注意:① 当数据文件内容不为空时,不能将它们从指定数据库中删除;

当数据文件为主数据文件或数据库日志文件时,不能将它们从指定数据库中删除;

当数据文件所属的文件组为默认文件组并且此数据文件是该默认文件组的唯一成员时,不能将它从指定数据库中删除。

2.4  删除索引

使用索引可以提高数据库查询的性能,但索引同样需要数据库的开销。当某些索引不需要的时候,需要删除这些索引。删除索引可以直接在SQL
Server Management
Studio中采用图形方式完成,也可以通过执行Transact-SQL语句完成。

(1)使用SQL Server Management Studio图形工具删除索引

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

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

 

 图片 101

 

 

图 24  选择“索引”节点

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

 

 图片 102

 

 

图 25  展开“索引”

3. 选择某个需要删除的索引,单击鼠标右键,选择“删除”菜单项,打开“删除对象”对话框,如图26所示。

 

 图片 103

 

 

图 26  “删除对象”对话框

4. 当确定删除时,单击“确定”按钮,完成删除操作。

(2)使用Transact-SQL语句删除索引

当需要使用Transact-SQL语句删除索引时,采用DROP
INDEX语句,其语法格式如下:

 

 

DROP INDEX
{ <drop_relational_or_xml_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

<drop_relational_or_xml_index> ::=
    index_name ON <object> 
    [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]

<drop_backward_compatible_index> ::=
    [ owner_name. ] table_or_view_name.index_name

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

<drop_clustered_index_option> ::=
{
    MAXDOP = number_of_processors
  | ONLINE = { ON | OFF }
  | MOVE TO { partition_scheme_name ( column_name ) 
            | filegroup_name
            | default 
            }
}

 

 

其中:

index_name,表示需要删除的索引名;

database_name,表示索引所在的数据库名称;

schema_name,表示索引所在的模式名;

table_or_view_name,表示索引所在的表或者视图的名称;

l <drop_clustered_index_option>,控制聚集索引选项,这个选项不能同其他索引类型一起使用。

l MAXDOP = number_of_processors,重写“max degree of
parallelism
”选项。

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

l MOVE TO,定义聚集索引页级的数据行移动的位置。

partition_scheme_name (
column_name ),定义存储结果表的分区模式。

filegroup_name,定义存储结果表的文件组。

l DEFAULT,定义存储结果表的默认位置。

当某个索引不能明显改进查询效率时候,需要将索引删除,以节省资源,因为索引是需要耗费代价的。下面我们以删除2.3一节所创建的索引“ix_name”为例,使用Drop
Index命令删除索引,其Transact-SQL语句如下:

USE [EAMS];

GO

IF EXISTS (SELECT name FROM sys.indexes

            WHERE name = N’ ix_name ‘)

    DROP INDEX ix_name ON mrBaseInf;

GO

8.MAXSIZE指定操作系统文件可以增长到的最大值。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

相关文章

网站地图xml地图