微软认证考试70-461-Create Database Objects（创建数据库对象）-24%比重

附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第一节Create Database Objects创建数据库对象。

第一部分：Create and alter tables using T-SQL syntax (simple statements).May include but not limited to: create tables without using the built in tools; ALTER; DROP; ALTER COLUMN; CREATE 用T-SQL语句创建修改表(简易叙述)。可能包含并不仅限于：不使用内建工具创建，修改，删除表；创建，修改，删除列；

创建表：CREATE TABLE Test(ID INT IDENTITY(1,1) PRIMARY KEY,Name VARCHAR(100))

删除表：DROP TABLE Test3

修改表增加列：ALTER TABLE Test2 ADD Email VARCHAR(200)
修改表修改列：ALTER TABLE Test2 ALTER COLUMN Name VARCHAR(200)
重命名表：EXEC SP_RENAME 'Test2','Test3'
重命名列： EXEC SP_RENAME 'Test3.Name','Name2'

修改表删除列：alter table test drop column name

参考：http://msdn.microsoft.com/en-us/library/ms174979(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms173790(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms190273(v=sql.110).aspx

第二部分：Create and alter views (simple statements). May include but not limited to: create indexed views; create views without using the built in tools; CREATE, ALTER, DROP 创建修改视图（简易叙述）。可能包含并不仅限于：不使用内建工具创建，修改，删除视图；创建带索引视图。

创建修改视图：

CREATE/ALTER VIEW TestView
AS
SELECT id,testid FROM test

删除视图：

DROP VIEW TestView

为视图创建索引：

CREATE UNIQUE CLUSTERED INDEX NIX_TestView_testid
ON TestView
(
testid
)

注意：视图需要设置WITH SCHEMABINDING

ALTER VIEW TestView
WITH SCHEMABINDING
AS
SELECT id,testid,name FROM test
(error: Cannot schema bind view 'TestView' because name 'test' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
)

更改视图设置选项WITH SCHEMABINDING出错：此视图不能设置SCHEMABINDING。表名必须是两部分格式并且对象不能引用本身。

正确语句：

VIEW TestView
WITH SCHEMABINDING
AS
SELECT id,testid FROM dbo.test

删除视图索引：DROP INDEX 视图名.索引名，如DROP INDEX TestView.NIX_TestView_id

参考：http://msdn.microsoft.com/en-us/library/ms187956(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms173846(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms173492(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms191432(v=sql.110).aspx

第三部分：Design views.May include but not limited to: ensure code non regression by keeping consistent signature for procedure, views and function (interfaces); security implications 设计视图。可能包含但不仅限于：为存储过程，视图还有函数（接口）保持签名的一致性进而确保代码不会回归；浅在安全考虑。

解释："Ensure that a legacy, non-recompiled application (which accesses a database;OLTP) is not broken (becomes unusable) because table schema (such as data types which might have been upgraded in SQL Server 2012) is changed. This is accomplished by creating/modifying Views, Stored Procedures, and Functions to emulate the interface (signature) which the applications expects (to work successfully). So if tables, their columns, data types, constraints, etc. have been changed/updated, instead of doing what lazy programmers hate most (upgrading their applications), business continuity is ensured via the easier task of creating/modifying views to emulate (mimic) the data structure expected by the application (code).“

”确保一个遗留的未重新重编译的应用程序（用来访问数据库的;联机事务处理）没有被毁坏（可用），因为表架构（如某些数据类型可能已经为SQL Server 2012进行了升级）已经更改。更改操作的完成是基于创建更改视图，存储过程还有函数进而模拟应用程序预期的接口（签名）。因此，如果表，列，数据类型，约束等已经被更改或更新，可以通过更简单的创建修改视图任务去模拟应用程序预期的数据结构进而确保业务连续性，这样可以避免懒程序员最不愿意做的事：升级应用程序。

WITH SCHEMABINDING

When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.当为视图设置了WITH SCHEMABINDING，则不能对基表进行影响视图结构的操作。比如，视图里有name字段，在基表中偿试删除name字段将报错。

一个视图最多可以有1024个列。

WITH CHECKOPTION. 强制针对视图执行的所有数据修改语句都必须符合在 select_statement 中设置的条件。 通过视图修改行时，WITH CHECK OPTION 可确保提交修改后，仍可通过视图看到数据。

不可更新视图

只要满足下列条件，即可通过视图修改基础基表的数据：

• 任何修改（包括 UPDATE、INSERT 和 DELETE 语句）都只能引用一个基表的列。

• 视图中被修改的列必须直接引用表列中的基础数据。不能通过任何其他方式对这些列进行派生，如通过以下方式：

  • 聚合函数：AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR 和 VARP。

  • 计算。 不能从使用其他列的表达式中计算该列。使用集合运算符 UNION、UNION ALL、CROSSJOIN、EXCEPT 和 INTERSECT 形成的列将计入计算结果，且不可更新。

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

• TOP 在视图的 select_statement 中的任何位置都不会与 WITH CHECK OPTION 子句一起使用。

，否则此视图不可更新。如果想通过不可更新视图更新基表数据，需要使用INSTEAD OF 触发器。

参考：http://msdn.microsoft.com/en-us/library/ms187956.aspx

第四部分：Create and modify constraints (simple statements). May include but not limited to: create constraints on tables; define constraints; unique constraints; default constraints; primary and foreign key constraints. 创建修改约束（简易叙述）。可能包含并不仅限于：为表创表约束；声明约束；唯一约束；缺省约束；主键和外键约束。

创建表时创建主键约束：

create table test
(
id int constraint PK_test_id primary key,
name varchar(50)
)

或者

create table test
(
id int,
name varchar(50),
constraint PK_test_id primary key (id)
)

创建表时创建外键约束：

create table test2
(
id int primary key,
name varchar(50)
)

create table test
(
id int constraint PK_test_id primary key,
test2ID int,
name varchar(50),
foreign key(test2ID) references test2(id)
)

SQL语句创建主键PRIMARY KEY约束：alter table test add constraint PK_test_id primary key(id)

SQL语句创建外键FOREIGN KEY约束：alter table test add constraint FK_test_test2ID foreign key(test2id) references test2(id)

SQL语句创建唯一UNIQUE约束：alter table test add constraint U_Test_Name unique(name)

SQL语句创建缺省DEFAULT约束:alter table test add constraint DF_test_name Default '乐可乐可的部落格' for name

SQL语句创建检查CHECK约束:alter table test add constraint CK_test_name Check(name<>'乐可乐可的部落格')

SQL语句删除约束：alter table test drop constraint PK_test_id

参考：

http://msdn.microsoft.com/en-us/library/ms188066(v=sql.110).aspx

第五部分：Create and alter DML triggers. May include but not limited to: inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers. 创建修改DML触发器。可能包含并不仅限于：已插入和已删除数据的临时表；嵌套触发器；触发器类型；触发器更新函数；一个会话中的多行数据处理；浅在的触发器性能问题。

参考：

http://msdn.microsoft.com/zh-cn/library/ms189799(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms176072(v=sql.110).aspx

触发器限制

CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用于一个表。

触发器只能在当前的数据库中创建，但是可以引用当前数据库的外部对象。

如果指定了触发器架构名称来限定触发器，则将以相同的方式限定表名称。

在同一条 CREATE TRIGGER 语句中，可以为多种用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作。

如果一个表的外键包含对定义的 DELETE/UPDATE 操作的级联，则不能对为表上定义 INSTEAD OF DELETE/UPDATE 触发器。

在触发器内可以指定任意的 SET 语句。 选择的 SET 选项在触发器执行期间保持有效，然后恢复为原来的设置。

基本语法结构：

• Create/ALter trigger trigger_name
  on {table_name | view_name}
  {for | After | Instead of }
  [ insert, update,delete ]
  as
  sql_statement

示例：

create trigger TG_Student_Update
ON Test
for update
as
if update(name)
begin
update test2 set name=i.name from test2 t2, deleted d, inserted i where t2.name=d.name
end

注意其中的deleted和inserted临时表。

删除触发器：drop trigger TG_Student_Update

嵌套触发器

当触发器执行启动其他触发器的操作时，DML 和 DDL 触发器都是嵌套触发器。这些操作都可以启动其他触发器等。DML 触发器和 DDL 触发器最多可以嵌套 32 层。 可以通过 nested triggers 服务器配置选项来控制是否可以嵌套 AFTER 触发器。 但不管此设置为何，都可以嵌套 INSTEAD OF 触发器（只有 DML 触发器可以为 INSTEAD OF 触发器）。

http://msdn.microsoft.com/zh-cn/library/ms190739(v=sql.110).aspx

DDL 触发器与 DML 触发器

DDL 触发器和 DML 触发器的用处不同。

DML 触发器在 INSERT、UPDATE 和 DELETE 语句上操作，并且有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。

DDL 触发器在 CREATE、ALTER、DROP 和其他 DDL 语句上操作。它们用于执行管理任务，并强制影响数据库的业务规则。它们应用于数据库或服务器中某一类型的所有命令。

可以使用相似的 Transact-SQL 语法创建、修改和删除 DML 触发器和 DDL 触发器，它们还具有其他相似的行为。

http://msdn.microsoft.com/zh-cn/library/ms189599%28v=sql.90%29.aspx

触发器类型

http://msdn.microsoft.com/zh-cn/library/ms178110(v=sql.110).aspx

AFTER 触发器

在执行 INSERT、UPDATE、MERGE 或 DELETE 语句的操作之后执行 AFTER 触发器。如果违反了约束，则永远不会执行 AFTER 触发器；因此，这些触发器不能用于任何可能防止违反约束的处理。

INSTEAD OF 触发器

INSTEAD OF 触发器替代下列触发语句的标准操作。 因此，触发器可用于对一个或多个列执行错误或值检查，然后在插入、更新或删除行之前执行其他操作。

CLR 触发器

CLR 触发器可以是 AFTER 触发器或 INSTEAD OF 触发器。CLR 触发器还可以是 DDL 触发器。CLR 触发器将执行在托管代码（在 .NET Framework 中创建并在 SQL Server 中上载的程序集的成员）中编写的方法，而不用执行 Transact-SQL 存储过程。

触发器更新函数update functions

create trigger TG_Student_Update
ON Test
for update
as
if update(name)
begin
update test2 set name=i.name from test2 t2, deleted d, inserted i where t2.name=d.name
end

红色字体部分

一个会话中的多行数据处理handle multiple rows in a session

http://msdn.microsoft.com/zh-cn/library/ms190752(v=sql.110).aspx

为 DML 触发器编写代码时，请考虑导致触发器激发的语句可能是影响多行数据（而不是单行）的单个语句。这对于 UPDATE 和 DELETE 触发器很常见，因为这些语句经常影响多行。而这对于 INSERT 触发器比较少见，因为基本 INSERT 语句仅添加单行。 但是，由于 INSERT 触发器可以通过 INSERT INTO (table_name) SELECT 语句激发，所以插入许多行可能导致调用单个触发器。

我们建议不要在触发器中使用游标，因为它们可能会降低性能。 若要设计一个影响多行的触发器，请使用基于行集的逻辑，而不要使用游标。

浅在的触发器性能问题performance implications of triggers

The INSERT, DELETE, UPDATE, and SELECT statements that initiate triggers might appear to execute slowly because they execute additional SQL statements, and the user might not know that other actions are occurring.
In many situations, triggers can improve performance slightly because of the reduction in the number of messages passed from the client to the database server.
For example, if the trigger fires five SQL statements, the client saves at least 10 messages passed between the client and database server。

DML触发器或许会执行稍慢一些，因为它要执行除INSERT,DELETE,UPATE额外的SQL语句，并且用户可能不知道有其它的行为产生。在多数情况下，触发器可以提高些微的性能因为它减少了客户端和数据库服务器的信息传递。比如，一个触发器执行了5条SQL语句，客户端至少省去了传递在客户端和数据库服务器的10条信息。

http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_551.htm

http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_0593.htm

微软认证考试70-461 Create Database Objects（创建数据库库对象）部分至此已完结，占总内容24%