以下是小编整理的创建实体化视图的几个注意点数据库教程（共含7篇），欢迎阅读分享。同时，但愿您也能像本文投稿人“海的尽头”一样，积极向本站投稿分享好文章。
创建|视图
1，如果要创建基表是其它用户表的实体化视图，那么需要给实体化视图的owner赋予以下权限：
grant CREATE ANY MATERIALIZED VIEW to username;
grant SELECT ANY TABLE to username;
如果要创建refresh on commit的视图，那么还需要下面这个权限：
grant ON COMMIT REFRESH to username;
2。创建refresh on commit的语法如下，此类实体化视图在基表的事务commit之后，就会立刻刷新
CREATE MATERIALIZED VIEW MV_T1
REFRESH FAST ON COMMIT WITH PRIMARY KEY AS SELECT * FROM kamus.t1;
3。如果不指定on commit，那么默认是on demand，只有手工调用DBMS_MVIEW包中的刷新过程，实体化视图才会被刷新
4，
指定了start with ... next ...选项之后，第一次创建会有作一次完整刷新，然后在指定的时间间隔之后会定时刷新，本例中刷新间隔为1分钟。
语法如下：
CREATE MATERIALIZED VIEW MV_T1
REFRESH FAST START WITH SYSDATE NEXT sysdate+1/24/60 WITH PRIMARY KEY AS SELECT * FROM kamus.t1;
检查USER_REFRESH视图和USER_JOBS视图，我们可以发现start with... next ...语法也就是Oracle自动创建了一个刷新组，这个刷新组的名称跟实体化视图名称相同，并且IMPLICIT_DESTROY属性为Y，表示只要该组中的实体化视图删除该组也自动被删除。同时，创建了一个JOB，JOB中的waht属性是dbms_refresh.refresh('“SCOTT”.“MV_T1”');
自然，由于自动刷新是通过JOB完成的，那么初始化参数job_queue_processes必须大于0，这样JOB才会正常运行。
5。可以自己创建刷新组来定时刷新，我以前的这篇文章中有创建刷新组的方法：
blog.csdn.net/kamus/archive//09/18/108496.aspx
创建|错误|解决|视图
晚上测试实体化视图复制，测试环境中的master site是Oracle10g，MV site是Oracle9201，当在MV site上创建快速刷新的实体化视图时，报ORA-600错误，
SQL> CREATE MATERIALIZED VIEW KAMUS.ACCOUNT2004 REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM KAMUS.ACCOUNT2004@orcl;
CREATE MATERIALIZED VIEW KAMUS.ACCOUNT2004 REFRESH FAST WITH PRIMARY KEY AS SELECT * FROM KAMUS.ACCOUNT2004@orcl
ORA-00600: internal error code, arguments: [ksmovrflow], [kkznxddl.begin], [], [], [], [], [], []
查metalink，发现又是一个bug，这个bug只有当在Oracle8或者9中创建基于Oracle10g的实体化视图时才会发生，
原因：
Oracle10g的master table中创建主键时候显式指定了主键的名称。如下
alter table table_name add constraint < constraint name> primary key (< col>);
解决方法：
删除这个主键，然后创建一个不指定名称的主键，由Oracle自动命名，如下
alter table ACCOUNT2004 add primary key(OCCURTIME, ACCTID, CURRENCYID);
这样产生的主键名称就变成SYS_CXXXX。
之后重新在MV site上创建实体化视图，成功。
基本语法： CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [( column_list )] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] This statement creates a new view, or replaces an existing one if the
基本语法：
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEWview_name[(column_list)] ASselect_statement[WITH [CASCADED | LOCAL] CHECK OPTION]This statement creates a new view, or replaces an existing one if theOR REPLACEclause is given. Theselect_statementis aSELECTstatement that provides the definition of the view. The statement can select from base tables or other views.
This statement requires theCREATE VIEWprivilege for the view, and some privilege for each column selected by theSELECTstatement. For columns used elsewhere in theSELECTstatement you must have theSELECTprivilege. If theOR REPLACEclause is present, you must also have theDELETEprivilege for the view.
A view belongs to a database. By default, a new view is created in the current database. To create the view explicitly in a given database, specify the name asdb_name.view_namewhen you create it.
mysql>CREATE VIEW test.v AS SELECT * FROM t;
Tables and views share the same namespace within a database, so a database cannot contain a table and a view that have the same name.
Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by theSELECTstatement are used for the view column names. To define explicit names for the view columns, the optionalcolumn_listclause can be given as a list of comma-separated identifiers. The number of names incolumn_listmust be the same as the number of columns retrieved by theSELECTstatement.
Columns retrieved by theSELECTstatement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth.
Unqualified table or view names in theSELECTstatement are interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the proper database name.
A view can be created from many kinds ofSELECTstatements. It can refer to base tables or other views. It can use joins,UNION, and subqueries. TheSELECTneed not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns:
mysql>CREATE TABLE t (qty INT, price INT);mysql>INSERT INTO t VALUES(3, 50);mysql>CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;mysql>SELECT * FROM v;+------+-------+-------+| qty | price | value |+------+-------+-------+| 3 | 50 | 150 |+------+-------+-------+
A view definition is subject to the following restrictions:
TheSELECTstatement cannot contain a subquery in theFROMclause.
TheSELECTstatement cannot refer to system or user variables.
TheSELECTstatement cannot refer to prepared statement parameters.
Within a stored routine, the definition cannot refer to routine parameters or local variables.
Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. To check a view definition for problems of this kind, use theCHECK TABLEstatement.
The definition cannot refer to aTEMPORARYtable, and you cannot create aTEMPORARYview.
The tables named in the view definition must already exist.
You cannot associate a trigger with a view.
ORDER BYis allowed in a view definition, but it is ignored if you select from a view using a statement that has its ownORDER BY.
For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes aLIMITclause, and you select from the view using a statement that has its ownLIMITclause, it is undefined which limit applies. This same principle applies to options such asALL,DISTINCT, orSQL_SMALL_RESULTthat follow theSELECTkeyword, and to clauses such asINTO,FOR UPDATE,LOCK IN SHARE MODE, andPROCEDURE.
If you create a view and then change the query processing environment by changing system variables, that may affect the results you get from the view:
mysql>CREATE VIEW v AS SELECT CHARSET(CHAR(65)), COLLATION(CHAR(65));Query OK, 0 rows affected (0.00 sec)mysql>SET NAMES 'latin1';Query OK, 0 rows affected (0.00 sec)mysql>SELECT * FROM v;+-------------------+---------------------+| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |+-------------------+---------------------+| latin1| latin1_swedish_ci |+-------------------+---------------------+1 row in set (0.00 sec)mysql>SET NAMES 'utf8';Query OK, 0 rows affected (0.00 sec)mysql>SELECT * FROM v;+-------------------+---------------------+| CHARSET(CHAR(65)) | COLLATION(CHAR(65)) |+-------------------+---------------------+| utf8 | utf8_general_ci |+-------------------+---------------------+1 row in set (0.00 sec)
The optionalALGORITHMclause is a MySQL extension to standard SQL.ALGORITHMtakes three values:MERGE,TEMPTABLE, orUNDEFINED. The default algorithm isUNDEFINEDif noALGORITHMclause is present. The algorithm affects how MySQL processes the view.
ForMERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.
ForTEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.
ForUNDEFINED, MySQL chooses which algorithm to use. It prefersMERGEoverTEMPTABLEif possible, becauseMERGEis usually more efficient and because a view cannot be updatable if a temporary table is used.
A reason to chooseTEMPTABLEexplicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than theMERGEalgorithm so that other clients that use the view are not blocked as long.
A view algorithm can beUNDEFINEDthree ways:
NoALGORITHMclause is present in theCREATE VIEWstatement.
TheCREATE VIEWstatement has an explicitALGORITHM = UNDEFINEDclause.
ALGORITHM = MERGEis specified for a view that can be processed only with a temporary table. In this case, MySQL generates a warning and sets the algorithm toUNDEFINED.
As mentioned earlier,MERGEis handled by merging corresponding parts of a view definition into the statement that refers to the view. The following examples briefly illustrate how theMERGEalgorithm works. The examples assume that there is a viewv_mergethat has this definition:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) ASSELECT c1, c2 FROM t WHERE c3 > 100;
Example 1: Suppose that we issue this statement:
SELECT * FROM v_merge;
MySQL handles the statement as follows:
v_mergebecomest
*becomesvc1, vc2, which corresponds toc1, c2
The viewWHEREclause is added
The resulting statement to be executed becomes:
SELECT c1, c2 FROM t WHERE c3 > 100;
Example 2: Suppose that we issue this statement:
SELECT * FROM v_merge WHERE vc1 < 100;
This statement is handled similarly to the previous one, except thatvc1 < 100becomesc1 < 100and the viewWHEREclause is added to the statementWHEREclause using anANDconnective (and parentheses are added to make sure the parts of the clause are executed with correct precedence). The resulting statement to be executed becomes:
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
Effectively, the statement to be executed has aWHEREclause of this form.:
WHERE (select WHERE) AND (view WHERE)
TheMERGEalgorithm requires a one-to relationship between the rows in the view and the rows in the underlying table. If this relationship does not hold, a temporary table must be used instead. Lack of a one-to-one relationship oclearcase/“ target=”_blank“ >ccurs if the view contains any of a number of constructs:
Aggregate functions (SUM,MIN(),MAX(),COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
UNIONorUNION ALL
Refers only to literal values (in this case, there is no underlying table)
Some views are updatable. That is, you can use them in statements such asUPDATE,DELETE, orINSERTto update the contents of the underlying table. For a view to be updatable, there must be a one-to relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view non-updatable. To be more specific, a view is not updatable if it contains any of the following:
Aggregate functions (SUM(),MIN(),MAX(),COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
UNIONorUNION ALL
Subquery in the select list
Join
Non-updatable view in theFROMclause
A subquery in theWHEREclause that refers to a table in theFROMclause
Refers only to literal values (in this case, there is no underlying table to update)
ALGORITHM = TEMPTABLE(use of a temporary table always makes a view non-updatable)
With respect to insertability (being updatable withINSERTstatements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:
There must be no duplicate view column names.
The view must contain all columns in the base table that do not have a default value.
The view columns must be simple column references and not derived columns. A derived column is one that is not a simple column reference but is derived from an expression. These are examples of derived columns:
3.14159col1 + 3UPPER(col2)col3 / col4(subquery)A view that has a mix of simple column references and derived columns is not insertable, but it can be updatable if you update only those columns that are not derived. Consider this view:
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
This view is not insertable becausecol2is derived from an expression. But it is updatable if the update does not try to updatecol2. This update is allowable:
UPDATE v SET col1 = 0;
This update is not allowable because it attempts to update a derived column:
UPDATE v SET col2 = 0;
It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with theMERGEalgorithm. For this to work, the view must use an inner join (not an outer join or aUNION). Also, only a single table in the view definition can be updated, so theSETclause must name only columns from one of the tables in the view. Views that useUNION ALLare disallowed even though they might be theoretically updatable, because the implementation uses temporary tables to process them.
For a multiple-table updatable view,INSERTcan work if it inserts into a single table.DELETEis not supported.
TheWITH CHECK OPTIONclause can be given for an updatable view to prevent inserts or updates to rows except those for which theWHEREclause in theselect_statementis true.
In aWITH CHECK OPTIONclause for an updatable view, theLOCALandCASCADEDkeywords determine the scope of check testing when the view is defined in terms of another view.LOCALkeyword restricts theCHECK OPTIONonly to the view being defined.CASCADEDcauses the checks for underlying views to be evaluated as well. When neither keyword is given, the default isCASCADED. Consider the definitions for the following table and set of views:
mysql>CREATE TABLE t1 (a INT);mysql>CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2->WITH CHECK OPTION;mysql>CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0->WITH LOCAL CHECK OPTION;mysql>CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0->WITH CASCADED CHECK OPTION;
Here thev2andv3views are defined in terms of another view,v1.v2has aLOCALcheck option, so inserts are tested only against thev2check.v3has aCASCADEDcheck option, so inserts are tested not only against its own check, but against those of underlying views. The following statements illustrate these differences:
ql> INSERT INTO v2 VALUES (2);Query OK, 1 row affected (0.00 sec)mysql>INSERT INTO v3 VALUES (2);ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
The updatability of views may be affected by the value of theupdatable_views_with_limitsystem variable. (完)
原文转自：www.ltesting.net
视图与表具有相似的结构，当向视图中插入或更新数据时，实际上对视图所引用的表执行数据的插入和更新，
通过视图管理数据数据库教程
。但是通过视图插入、更新数据和表相比有一些限制，下面通过具体的例子来讲述通过视图插入、更新数据以及其使用的限制。使用SELECT 语句，可以在视图和表中查到该条记录。但是如果执行下面的语句，虽然仍可以成功执行，但只可以在表而不是视图中查到该条数据。
注意：由于向视图插入数据实质是向其所引用的基本表中插入数据，所以必须确认那些来包括在视图列但属于表的列允许NULL值或有缺省值。
若要执行INSERT 语句，则在同一个语句只能对属于同一个表的列执行操作，
所以，若向视图au_title 中插入一行数据，只能分别执行以下语句：
insert into au_title (author_au_id, au_lname, au_fname, contract)
values ('234-34-4611','John','Smith', 1)
insert into au_title (title_au_id, title_id, au_ord, royaltyper)
values ('234-34-4611','BU1111',1,50)
通过视图对数据进行更新与删除时需要注意到两个问题：
执行UPDATE DELETE 时，所删除与更新的数据，必须包含在视图结果集中；
如果视图引用多个表时，无法用DELETE 命令删除数据，若使用UPDATE 则应与INSERT 操作一样，被更新的列必须属于同一个表。
视图
大家知道
1：如下查询语句没问题
select * from sysobjects order by name
2：如果把该查询语句建成视图
create view v_test
as
select * from sysobjects order by name
会提示出错:
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
3: 既然提示除非在语句中使用top 才能用order by，那就好说了
create view v_test
as
select top 100 percent * from sysobjects order by name
一切正常
再用select * from v_test查一下，确实已经正确排序，
在视图中用order by数据库教程
，
在进行备份以前首先必须创建备份设备，
创建备份设备数据库教程
。备份设备是用来存储数据库、事务日志或文件和文件组备份的存储介质。备份设备可以是硬盘、磁带或管道。SQL Server 只支持将数据库备份到本地磁带机，而不是网络上的远程磁带机。当使用磁盘时，SQL Server 允许将本地主机硬盘和远程主机上的硬盘作为备份设备，备份设备在硬盘中是以文件的方式存储的。15.2.1 用SQL Server Enterprise Manager 管理备份设备
1 使用SQL Server Enterprise Manager 创建备份设备
（1）使用SQL Server Enterprise Manager， 创建备份设备的步骤为：
（2） 打开Management 文件夹，然后选择Backup 图标。
（3） 右击Backup 在弹出菜单中选择 New Backup Device 选项，然后弹出 BackupDevice Properties C New Device 对话框，
如图15-1 所示。
（4） 在Name 栏中输入设备名称该名称是备份设备的逻辑名。
（5） 选择备份设备类型，如果选择File name 表示使用硬盘做备份，只有正在创建的设备是硬盘文件时，该选项才起作用。如果选择Tape Drive name 表示使用磁带设备。只有正在创建的备份设备是与本地服务器相连的磁带设备时，该选项才起作用。
（6） 然后单击确定创建备份设备。
2 使用SQL Server Enterprise Manager 删除备份设备在创建备份设备的第二步，选中Backup 图标后，在右格对话框中右击要删除的备份设备，在弹出菜单中选择Delete 选项，则删除该备份设备。
15.2.2 使用系统过程管理备份设备
1 sp_addumpdevice
在SQL Server 中，使用sp_addumpdevice 来创建备份设备。其语法格式为：
server|sqlserver|创建
该文详细介绍实现分区表的过程以及有助于完成此过程的功能，sqlserver 2005 如何创建分区表数据库教程
。逻辑流程如下：图：创建分区表或索引的步骤
确定是否应为对象分区
虽然分区可以带来众多的好处，但也增加了实现对象的管理费用和复杂性，这可能是得不偿失的。尤其是，您可能不需要为较小的表或目前满足性能和维护要求的表分区。前面提到的销售方案使用分区减轻了移动行和数据的负担，但在决定是否实现分区时，您应考虑您的方案是否存在这种负担。
确定分区键和分区数
如果您正在尝试改善大型数据子集的性能和可管理性，并且已经定义了访问模式，则可以使用范围分区减少数据争用的情况，同时减少只读数据不需要分区时的维护工作。要确定分区数，应先评估您的数据中是否存在逻辑分组和模式。如果您通常一次只处理这些已定义子集中的少数几个，则应定义范围以隔离查询，使其只处理相应的数据（即，只处理特定的分区）。
确定是否应使用多个文件组
为了有助于优化性能和维护，应使用文件组分离数据。文件组的数目一定程度上由硬件资源决定：一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。但是，这主要适用于打算对整个数据集进行分析的系统。如果您有多个 CPU，SQL Server 则可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。这种情况下，可以获得并行处理以及在分区表中移入和移出分区的好处。
创建文件组
如果需要为多个文件放置一个分区表以获得更好的 I/O平衡，则至少需要创建一个文件组。文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用，但是为了更好地管理数据（例如，为了获得更精确的备份控制），应该对分区表进行设计，以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 ALTER DATABASE，可以添加逻辑文件组名，然后添加文件。要为 AdventureWorks 数据库创建名为 2003Q3 的文件组，请按以下方式使用 ALTER DATABASE：
ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]
创建文件组后，使用 ALTER DATABASE 将文件添加到该文件组中。
ALTER DATABASE AdventureWorks
ADD FILE
(NAME = N'2003Q3',
FILENAME = N'C:AdventureWorks2003Q3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]
通过在 CREATE TABLE 的 ON 子句中指定一个文件组，可以为文件创建一个表。但是，如果表未分区，则不能为多个文件组创建一个表。要为一个文件组创建表，请使用 CREATE TABLE 的 ON 子句。要创建分区表，必须先确定分区的功能机制。进行分区的标准以分区函数的形式从逻辑上与表相分离。此分区函数作为独立于表的定义存在，而这种物理分离将起到帮助作用，因为多个对象都可以使用该分区函数。因此，为表分区的第一步是创建分区函数。
为范围分区创建分区函数
范围分区必须使用边界条件进行定义。而且，即使通过 CHECK 约束对表进行了限制，也不能消除该范围任一边界的值。为了允许定期将数据移入该表，需要创建最后一个空分区。
在范围分区中，首先定义边界点：如果存在五个分区，则定义四个边界点值，并指定每个值是第一个分区的上边界 (LEFT) 还是第二个分区的下边界 (RIGHT)。根据 LEFT 或 RIGHT 指定，始终有一个空分区，因为该分区没有明确定义的边界点。
具体来讲，如果分区函数的第一个值（或边界条件）是 '20001001'，则边界分区中的值将是：
对于 LEFT
第一个分区是所有小于或等于 '20001001' 的数据
第二个分区是所有大于 '20001001' 的数据
对于 RIGHT
第一个分区是所有小于 '20001001' 的数据
第二个分区是所有大于或等于 '20001001' 数据
由于范围分区可能在 datetime 数据中进行定义，因此必须了解其含义。使用datetime具有某种含义：即总是同时指定日期和时间。未定义时间值的日期表示时间部分为“0”的 12:00 A.M。如果将 LEFT 与此类数据结合使用，则日期为 10 月 1 日 12:00 A.M. 的数据将位于第一个分区，而 10 月份的其他数据将位于第二个分区。从逻辑上讲，最好将开始值与 RIGHT 结合使用，而将结束值与 LEFT 结合使用。下面的三个子句将创建逻辑上相同的分区结构：
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
或
RANGE RIGHT FOR VALUES ('20001001 00:00:00.000', '20010101 00:00:00.000', '20010401 00:00:00.000', '20010701 00:00:00.000')
或
RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')
注意：此处使用 datetime 数据类型确实增加了一定的复杂性，但您需要确保设置正确的边界情况。请注意使用 RIGHT 的简单性，因为默认时间为 12:00:00.000 A.M。对于 LEFT，复杂性增加是因为 datetime 数据类型具有精度。必须选择 23:59:59.997 的原因在于，datetime 数据无法保证毫秒级别的精度。相反，datetime 数据的精度在 3.33 毫秒内。使用 23:59:59.999 这个确切的时间值是不行的，因为该值将被舍入到最接近的时间值，即第二天的 12:00:00.000 A.M。由于进行了这种舍入，将无法正确定义边界，
对于 datetime 数据，必须对明确提供的毫秒值加倍小心。
注意：分区函数还允许将函数作为分区函数定义的一部分。您可以使用 DATEADD(ms,-3,'20010101')，而不是使用 '20001231 23:59:59.997' 明确定义时间。
要在四个活动分区（每个分区代表一个日历季度）中存储四分之一的 Orders 数据，并创建第五个分区以备将来使用（还是作为占位符，用于在分区表中移入和移出数据），请将 LEFT 分区函数与以下四个边界条件结合使用：
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
记住，定义四个边界点将创建五个分区。通过查看以下数据集检查此分区创建的数据集：
边界点 '20000930 23:59:59.997' 作为 LEFT（设置模式）：
最左侧的分区将包含所有小于或等于 '20000930 23:59:59.997' 的值
边界点 '20001231 23:59:59.997'：
第二个分区将包含所有大于 '20000930 23:59:59.997' 但小于或等于 '20001231 23:59:59.997' 的值
边界点 '20010331 23:59:59.997'：
第三个分区将包含所有大于 '20001231 23:59:59.997' 但小于或等于 '20010331 23:59:59.997' 的值
边界点 '20010630 23:59:59.997'：
第四个分区将包含所有大于 '20010331 23:59:59.997' 但小于或等于 '20010630 23:59:59.997' 的值
最后，第五个分区将包含所有大于 '20010630 23:59:59.997' 的值。
创建分区架构
创建分区函数后，必须将其与分区架构相关联，以便将分区定向至特定的文件组。定义分区架构时，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。对于前面创建的范围分区 (OrderDateRangePFN)，存在五个分区；最后一个空分区将在 PRIMARY 文件组中创建。因为此分区永远不包含数据，所以不需要指定特殊的位置。
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])
注意：如果所有分区都位于同一个文件组中，则可以使用以下更简单的语法：
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
ALL TO ([PRIMARY])
创建分区表
定义分区函数（逻辑结构）和分区架构（物理结构）后，即可创建表来利用它们。表定义应使用的架构，而架构又定义函数。要将这三者结合起来，必须指定应该应用分区函数的列。范围分区始终只映射到表中的一列，此列应与分区函数中定义的边界条件的数据类型相匹配。另外，如果表应明确限制数据集（而不是从负无穷大到正无穷大），则还应添加 CHECK 约束。
CREATE TABLE [dbo].[OrdersRange]
(
[PurchaseOrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL ,
[ModifiedDate] [datetime] NULL ,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] <= '20040630 11:59:59.997'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO
建立索引：是否分区？
默认情况下，分区表中创建的索引也使用相同的分区架构和分区列。如果属于这种情况，索引将与表对齐。尽管未作要求，但将表与其索引对齐可以使管理工作更容易进行，对于滑动窗口方案尤其如此。
例如，要创建唯一的索引，分区列必须是一个关键列；这将确保对相应的分区进行验证，以保证索引的唯一性。因此，如果需要在一列上对表进行分区，而必须在另一个列上创建唯一的索引，这些表和索引将无法对齐。在这种情况下，可以在唯一的列（如果是多列的唯一键，则可以是任一关键列）中对索引进行分区，或者根本就不进行分区。请注意，在分区表中移入和移出数据时，必须删除和创建此索引。
注意：如果您打算使用现有数据加载表并立即在其中添加索引，则通常可以通过以下方式获得更好的性能：先加载到未分区、未建立索引的表中，然后在加载数据后创建分区索引。通过为分区架构定义群集索引，可以在加载数据后更有效地为表分区。这也是为现有表分区的不错方法。要创建与未分区表相同的表并创建与已分区群集索引相同的群集索引，请用一个文件组目标位置替换创建表中的 ON 子句。然后，在加载数据之后为分区架构创建群集索引。
