微软MCITP SQL Server 2008数据库认证专家 考试试题

从给定的文件信息中，我们可以提炼出一系列与Microsoft SQL Server 2008数据库管理、性能优化以及安全控制相关的专业知识点。以下是对这些知识点的详细解析： ### 知识点一：数据库权限管理 #### 核心概念： 在SQL Server 2008中，权限管理是确保数据安全性和完整性的重要机制。它涉及到数据库对象的访问控制，包括表、存储过程、视图等。权限可以被授予或撤销，以控制用户对特定数据库资源的操作能力。 #### 实践案例： - **场景**：在一个数据库中，存在两个架构——`Marketing`和`Sales`，分别由`MarketingManager`和`SalesManager`用户拥有。为了使`John`用户能够访问`Sales.Orders`表的数据，但仅限于通过`Marketing.GetSalesSummary`存储过程，同时保持数据的安全性，需仔细配置权限。 - **解决方案**：创建存储过程`Marketing.GetSalesSummary`时，应使用`EXECUTE AS 'SalesUser'`子句，这样当`John`执行此存储过程时，将以`SalesUser`的身份运行，间接获取对`Sales.Orders`表的`SELECT`权限，而不直接暴露`Sales.Orders`表的访问权。 ### 知识点二：工作负荷文件格式的选择 #### 核心概念： 在SQL Server中，工作负荷文件用于记录数据库操作的模式，以便于性能调优和资源规划。不同格式的工作负荷文件适用于不同的分析工具和目的。 #### 实践案例： - **需求**：创建供数据库引擎优化顾问（DTA）使用的工作负荷文件。 - **解决方案**：应选择以下三种格式之一：XML文件、SQL Server Profiler跟踪文件、性能监视器日志文件。其中，XML文件通常包含详细的查询计划信息，而SQL Server Profiler跟踪文件则能捕捉数据库服务器上的各种活动，性能监视器日志文件用于记录系统的性能指标。 ### 知识点三：查询性能优化与执行计划 #### 核心概念： 执行计划是SQL Server优化查询执行方式的一种内部结构，它决定了数据如何从磁盘读取到内存，以及如何处理这些数据。通过分析执行计划，可以发现性能瓶颈并进行优化。 #### 实践案例： - **需求**：对SQL Server 2008的查询性能进行问题排查，并创建可保存的执行计划。 - **解决方案**：在SQL Server Management Studio中，使用`SET SHOWPLAN_ALL ON;`或`SET SHOWPLAN_XML ON;`命令，可以显示执行计划。其中，`SET SHOWPLAN_ALL`用于在查询前显示执行计划，而`SET SHOWPLAN_XML`则以XML格式返回执行计划，便于进一步分析和保存。 ### 知识点四：死锁信息捕获 #### 核心概念： 死锁是指两个或多个进程在执行过程中，因争夺资源而造成的一种僵局，若无外力作用，它们都将无法推进下去。在数据库环境中，死锁可能严重影响应用程序的响应时间。 #### 实践案例： - **需求**：使用SQL Server Profiler收集死锁信息。 - **解决方案**：应使用“DeadlockGraph”事件，这将捕获死锁的XML描述，其中包括参与死锁的所有线程的信息，以及它们请求和持有的锁详情。 ### 知识点五：聚集索引的评估与检测 #### 核心概念： 聚集索引是数据库中的一种特殊类型索引，它不仅提供了一种快速查找数据的方式，还决定了数据行在物理上的存储顺序。 #### 实践案例： - **需求**：评估一个数据库设计，确保所有表都有聚集索引。 - **解决方案**：使用`sys.tables`系统目录视图结合`OBJECTPROPERTY`函数，编写如下的Transact-SQL查询： ```sql SELECT name AS table_name FROM sys.tables WHERE OBJECTPROPERTY(object_id, 'TableHasClusteredIndex') = 0 ORDER BY name; ``` 这段代码将列出所有缺失聚集索引的表。 ### 知识点六：条件触发器的创建 #### 核心概念： 触发器是一种特殊类型的存储过程，当在指定的表上发生特定的事件（如INSERT、UPDATE或DELETE）时，自动执行预定义的SQL语句序列。 #### 实践案例： - **需求**：限制对数据库表的修改只能在每月的第一天进行。 - **解决方案**：创建一个条件触发器，例如`TRG_TABLES_ON`，并在其中加入逻辑判断当前日期是否为每个月的第一天，如果不是，则阻止修改操作的执行。 以上案例和解决方案详细阐述了SQL Server 2008数据库管理和优化的关键方面，包括权限管理、性能分析、死锁监控、索引评估及触发器创建，旨在帮助DBA和开发人员提高数据库的稳定性和效率。