|
发信人: reynolds(雷龙.DBA中) 整理人: reynolds(2002-04-11 09:51:23), 站内信件 |
| 81. You are the administrator of an SQL Server 2000 computer. The server contains a database named Acct. You purge the Acct database of old records and perform a full backup. The database now uses 4 GB of space. The database files are configured as shown below:
File Name Location Space Allocated Filegroup Acct_Data C:\data\acct_data.mdf 4000 PRIMARY Acct_Data1 D:\data\Acct_Data1_D… 4000 PRIMARY Acct_Data2 E:\data\Acct_Data2_D… 4000 PRIMARY The server has two hard disks that are configured as shown below: See http://www.cheet-sheets.com/228/image13.jpg 
The 3 GB transaction log file for the Acct database is stored on drive C. You need to make room for a new database that has a 3 GB data file and a 1 GB transaction log file. You want to optimize database performance on both databases. You also want to minimize administrative overhead. What should you do? A. Shrink the empty data file on drive E. Place the new data file and the new log file on drive E. B. Shrink the empty data file on drive E. Backup and shrink the log file on drive C to 2 GB. Place the new data file on drive E and the new log file on drive C. C. Shrink the data file on drive D to 1 GB, and then shrink the data file on drive E to 2 GB. Place the new data file on drive D and the new log file on drive E. D. Shrink and delete the data file on drive C, and then shrink the database files on drives D and E so that they are both 2 GB. Place the new data file and the new log file on drive C. Answer: B Reason: 有答案说是D，但是我怎么看也没有觉得D对，另外Shrink数据库可以从前面开始吗？有些疑惑。 82. You are the administrator of a SQL Server 2000 computer. The server contains your company's Accounts database. Hundreds of users access the database each day. Because you have had power interruptions in the past, you want to protect the physical integrity of the Accounts database. You do not want to slow down server operations. What should you do? A. Enable the ‘torn page detection’ database option for each database. B. Disable write caching on all disk controllers. C. Ensure that the write caching disk controllers have battery backups. D. Create a database maintenance plan to check database integrity and make repairs each night. Answer: C Reason: 做什么都白搭，不如买块电池。 ====================================================================== sp_dboption 显示或更改数据库选项。不能在 master 或 tempdb 数据库上使用 sp_dboption。向后兼容性支持 sp_dboption。使用 ALTER DATABASE 设置数据库选项。 TORN_PAGE_DETECTION 此恢复选项允许 SQL Server 检测由于电源故障或其它系统停歇导致的未完成的 I/O 操作。 当设置为 ON 时，只要一个 8 KB 的数据库页写入磁盘，该页的每个 512 字节扇区都有一位被翻转。当 SQL Server 以后读取该页时，如果有一个位发生错误，则该页将被错误写入；这样就会检测到一个残缺页。通常在恢复期间检测到残缺页，因为错误写入的任何页都可能在恢复时被读取。 虽然 SQL Server 数据库页是 8 KB，但是磁盘使用 512 字节的扇区执行 I/O 操作。因此，每个数据库页要写 16 个扇区。如果在操作系统将第一个 512 字节扇区写到磁盘和完成 8 KB I/O 操作之间系统失败（例如，由于电源故障），则可能出现残缺页。如果数据库页的第一个扇区在失败之前成功地写入磁盘，则磁盘上的数据库页将显示为更新过的，尽管可能没有更新成功。 说明 使用备用电池的磁盘高速缓存可以保证数据成功地写到磁盘上，或者根本就不写。 如果检测到残缺页，将出现一个 I/O 错误，并断开连接。如果在恢复过程中检测到残缺页，则数据库也会被标记为可疑。应该还原数据库备份，并应用所有事务日志备份，因为它在物理上是不一致的。 默认情况下，TORN_PAGE_DETECTION 为 ON。 此选项的当前设置可通过检查 DATABASEPROPERTYEX 的 IsTornPageDetectionEnabled 属性来确定。 ====================================================================== 83. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. Gus reports that he cannot modify data in the database. You use SQL Profiler to capture his activities as shown below: EventClass TextData TraceStart ExistingConnection -- network protocol: LPC set quoted identifier on set implicit transactions off… ExistingConnection -- network protocol: LPC set quoted identifier on set implicit transactions off… SQL:BatchCompleted set transaction isolation level serializable begin transaction select * from… You want Gus to be able to modify data. You also want the Transact-SQL statement to execute without compromising data integrity. What are two possible ways to achieve this goal? (Each correct answer represents a complete solution. Choose two.) A. Change the isolation level of the database connection. B. Add indexes to the Sales table. C. Remove the explicit transaction from the Transact-SQL batch. D. Create a stored procedure to execute the Transact-SQL batch. E. Add a cascading UPDATE trigger to the Products table. Answer: A E Reason: 前面解释过了，第35题。 84. You are the database administrator for a retail company. The company owns 250 stores. Every month, each store submits approximately 3,000 sales records, which are loaded into a SQL Server 2000 database at corporate headquarters. A Data Transformation Services (DTS) package transforms the sales records as they are loaded. The package writes the transformed sales records to the Sales table, which has a column for integer primary key values. The IDENTITY property automatically assigns a key value to each transformed sales record. After loading this month's sales data, you discover that a portion of the data contains errors. You stop loading data, identify the problem records, and delete those records from the database. You want to reuse the key values that were assigned to the records that you deleted. You want to assign the deleted key values to the next sales records you load. You also want to disrupt users' work as little as possible. What should you do? A. Export all records from the Sales table to a temporary table. Truncate the Sales table, and then reload the records from the temporary table. B. Export all records from the Sales table to a text file. Drop the Sales table, and then reload the records from the text file. C. Use the DBCC CHECKIDENT statement to reseed the Sales table's IDENTITY property. D. Set the Sales table's IDENTITY_INSERT property to ON. Add new sales records that have the desired key values. Answer: C Reason: ====================================================================== SET IDENTITY_INSERT 允许将显式值插入表的标识列中。 注释 任何时候，会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，并且为另一个表发出了 SET IDENTITY_INSERT ON 语句，则 Microsoft® SQL Server™ 返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON 并报告此属性已设置为 ON 的表。 如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。 SET IDENTITY_INSERT 的设置是在执行或运行时设置，而不是在分析时设置。 权限 执行权限默认授予 sysadmin 固定服务器角色和 db_owner 及 db_ddladmin 固定数据库角色以及对象所有者。 ====================================================================== DBCC CHECKIDENT 检查指定表的当前标识值，如有必要，还对标识值进行更正。 语法 DBCC CHECKIDENT ( 'table_name' [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ] ) 参数 'table_name' 是要对其当前标识值进行检查的表名。表名必须符合标识符规则。有关更多信息，请参见使用标识符。指定的表必须包含标识列。 NORESEED 指定不应更正当前标识值。 RESEED 指定应该更正当前标识值。 new_reseed_value 是在标识列中重新赋值时要使用的值。 注释 如有必要，DBCC CHECKIDENT 会更正列的当前标识值。然而，如果标识列是使用 NOT FOR REPLICATION 子句（在 CREATE TABLE 或 ALTER TABLE 语句中）创建的，则不更正当前标识值。 如果标识列上有主键或唯一键约束，无效标识信息可能会导致错误信息 2627。 对当前标识值所做的具体更正取决于参数规范。 ====================================================================== 85. You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database contains a table that is used to store information about equipment scheduling. The EquipmentSchedules table is configured as shown below: Column_name Type Computed Length Prec Scale Nullable EquipmentID int no 4 10 0 no CustomerID int no 4 10 0 no Location varchar no 50 yes StartDate datetime no 8 no EndDate datetime no 8 no Rate money no 8 19 4 no Identity Seed Increment EquipmentID 1 1 RowGuidCol No rowguidcoi column defined Data_located_on_filegroup PRIMARY Index_name index_description index_keys PK_EquipimentSchedules clusterd, unique, primary… EquipmentID Contraint_type contraint_name delete_action update_action PRIMARY KEY (clustered) PK_EquipmentSchedules na na Users report that some equipment schedules have an end date that is earlier than the start date. You need to ensure that the start date is always earlier than or equal to the end date. You also want to minimize physical I/O. You do not want users to change the Transact-SQL statements they use to modify data within the database. What should you do? A. Create a constraint that compares the start date to the end date. B. Create a trigger that compares the start date to the end date. C. Create a rule that compares the start date to the end date. D. Create a stored procedure that tests the start and end dates before inserting the row into the database. Answer: A Reason: 再次啰嗦一次约束、触发器等等的用途和用法吧！ ====================================================================== 约束 约束使您得以定义 Microsoft® SQL Server™ 2000 自动强制数据库完整性的方式。约束定义关于列中允许值的规则，是强制完整性的标准机制。使用约束优先于使用触发器、规则和默认值。查询优化器也使用约束定义生成高性能的查询执行计划。 触发器 触发器是一种特殊类型的存储过程，当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效：UPDATE、INSERT 或 DELETE。触发器可以查询其它表，而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。例如，可以控制是否允许基于顾客的当前帐户状态插入定单。 触发器还有助于强制引用完整性，以便在添加、更新或删除表中的行时保留表之间已定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。有关详细信息，请参见表关系。 规则 规则是一个向后兼容的功能，用于执行一些与 CHECK 约束相同的功能。CHECK 约束是用来限制列值的首选标准方法。CHECK 约束比规则更简明，一个列只能应用一个规则，但是却可以应用多个 CHECK 约束。CHECK 约束作为 CREATE TABLE 语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。 SQL 存储过程 存储过程是一组编译在单个执行计划中的 Transact-SQL 语句。 ====================================================================== ---- / <>< o /| / <>< (o / |/| ) ) <@ < ( O )( \ |\| <>< ) <>< () \| \ \ 每天在网易游水的鱼 |
|
[关闭][返回] |