|
发信人: reynolds(雷龙.DBA中) 整理人: reynolds(2002-04-11 10:31:32), 站内信件 |
| 116. You are the administrator of a SQL Server 2000 computer that contains multiple databases. One of these databases is named CorpSales. Your company hires a new employee named Helene. Part of Helene's job will be to create new tables in the CorpSales database. However she should not have access to the other databases on the server. You create a SQL Server login named Helene. Helene reports that she can log on to the server but is unable to access the CorpSales database. You need to allow Helene access to the CorpSales database. What should you do?
A. Create a new database user in the CorpSales database. Map the database user to Helene's login. Grant CREATE TABLE permissions to the database user. B. Create a new database role in the CorpSales database. Add Helene's login as a member of the role. Grant CREATE TABLE permissions to the database role. C. Change the server's authentication mode to Windows Authentication. D. Add Helene's login to the processadmin server role on the server. Answer: A Reason: 这一道题目我还真费了不少功夫，看上去AB似乎都可行。后来仔细操作才发现B操作是不可能的，因为Helene根本就无法访问CorpSales数据库，因此她根本不是CorpSales数据库里的用户，因此就算你创建新的角色并赋予权限，原不可能向里面添加Helene账号。 117. You are the administrator of a new multiprocessor SQL Server 2000 computer. You are migrating data from other database management systems to this server. You need to load data from two data files into a new table on the server. The table is empty and has no indexes. You want to complete the data load as quickly as possible. What should you do? (Each correct answer represents part of the solution. Choose all that apply.) A. Apply a bulk update lock to the table. B. Use separate client connections to load the files into the table simultaneously. C. Create a clustered index on the table. D. Specify the Full Recovery model for the database. E. Specify the Bulk-Logged Recovery model for the database. Answer: A B E Reason: B选项利用多进程加速。 ====================================================================== 大容量更新锁 当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新 (BU) 锁。大容量更新 (BU) 锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。 ====================================================================== 大容量日志记录恢复 大容量日志记录恢复模型提供对媒体故障的防范，并对某些大规模或大容量复制操作提供最佳性能和最少的日志使用空间。下列操作为最小日志记录操作： 〉SELECT INTO。 〉大容量装载操作（bcp 和 BULK INSERT）。 〉CREATE INDEX（包括索引视图）。 〉text 和 image 操作（WRITETEXT 和 UPDATETEXT）。 在大容量日志记录恢复模型中，这些大容量复制操作的数据丢失程度要比完全恢复模型严重。虽然在完全恢复模型下记录大容量复制操作的完整日志，但在大容量日志记录恢复模型下，只记录这些操作的最小日志，而且无法逐个控制这些操作。在大容量日志记录恢复模型中，数据文件损坏可能导致必须手工重做工作。 另外，当日志备份包含大容量更改时，大容量日志记录恢复模型只允许数据库恢复到事务日志备份的结尾处。不支持时点恢复。 在 Microsoft® SQL Server™ 2000 中，可以很容易地在完全恢复模型和大容量日志记录恢复模型之间进行切换。在大容量日志记录恢复模型中，不需要在完成大容量复制操作后执行完整数据库备份。在这个模型中，事务日志备份捕获日志和上次备份后执行的任何大容量操作的结果。 大容量日志记录恢复的备份策略包括： 〉数据库备份。 〉差异备份（可选）。 〉日志备份。 备份包含大容量日志记录操作的日志时，需要访问数据库内的所有数据文件。如果数据文件不可访问，则无法备份最后的事务日志，而且该日志中所有已提交的操作都将丢失。 在媒体发生故障时进行恢复 1、备份当前活动事务日志。有关更多信息，请参见事务日志备份。 2、还原最新的完整数据库备份。 3、如果有差异备份，则还原最新的那个备份。 4、按顺序应用自最新的差异备份或完整数据库备份后创建的所有事务日志备份。 5、手工重做最新日志备份后的所有更改。 重要 如果活动事务日志丢失（例如，由于包含该事务日志文件的磁盘发生硬件故障），则该日志中的所有事务都将丢失。若要防止活动事务日志丢失，请将事务日志文件放到镜像磁盘上 ====================================================================== 118. You are the administrator of a SQL Server 2000 computer in your company's personnel department. Employee data is stored in a SQL Server 2000 database. A portion of the database schema is shown below: See http://www.cheet-sheets.com/228/image19.jpg 
You want to create a text file that lists these data columns in the following format: title, firstName, lastName, workPhone, positionName, departmentName. You want to create the text file as quickly as possible. You do not expect to re-create this file, and you want to avoid creating new database objects if possible. What should you do? A. Use the bcp utility to export the data from each table to a separate text file. Use ‘format files’ to select the appropriate columns. Merge the data from each text file into a single text file. B. Create a view that joins data from all three tables. Include only the columns you want to appear in the text file. Use the bcp utility to export the data from the view. C. Create a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a local temporary table. Use the bcp utility to export the data from the local temporary table to a text file. D. Create a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a global temporary table. Use the bcp utility to export the data from the global temporary table to a text file. Answer: D Reason: 以上的bcp动作必须用全局临时表。 ====================================================================== 临时表 也可以创建临时表。临时表与永久表相似，但临时表存储在 tempdb 中，当不再使用时会自动删除。 有本地和全局两种类型的临时表，二者在名称、可见性和可用性上均不相同。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 Microsoft® SQL Server™ 2000 实例断开连接时被删除。全局临时表的名称以数学符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。 ====================================================================== 向临时表复制数据或从临时表向外复制数据 使用 bcp 或 BULK INSERT 大容量复制全局临时表的数据时，必须在命令提示符下指定该表名，包括开头的数字符号 (##)。例如，若要将数据从全局临时表 ##temp_authors 大容量复制到 Temp_authors.txt 数据文件，请在命令提示符下执行以下命令： bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -Ppassword 但是，使用全局临时表时不要指定数据库名，因为临时表仅存在于 tempdb 中。只有在使用 BULK INSERT 语句大容量复制数据时，才有可能使用局部临时表（例如 #temp_authors）。 ====================================================================== 119. You are the administrator of an SQL Server 2000 computer. The server contains a database that stores financial data. You want to use Data Transformation Services (DTS) packages to import numeric data from other SQL Server computers. The precision and scale values of this data are not defined consistently on the other servers. You want to prevent any loss of data during the import operations. What should you do? A. Use the ALTER COLUMN clause of the ALTER TABLE statement to change data types in the source tables. Change the data types so that they will use the lowest precision and scale values of the data that will be transferred. B. Use the ALTER COLUMN clause of the ALTER TABLE statement to change the data types in the destination tables. Change the data types to reflect the highest precision and scale values involved in the data transfer. C. Set a flag on each DTS transformation to require an exact match between source and destination columns. D. Set the maximum error count for each DTS transformation task equal to the number of rows of data you are importing. Use an exception file to store any rows of data that generate errors. E. Write a Microsoft ActiveX script for each DTS transformation. Use the script to recast data types to the destination's precision and scale values. Answer: B Reason: 有答案说是D，其实我觉得DTS已经提供了数据转换的功能又何必自己用ActiveX来做这一套过程，实在是多此一举。 120. You are the administrator of a SQL Server 2000 computer. You are creating a database named RetailImport to use as an intermediate data store for a data warehouse. Each night you must import daily data into the database from SQL Server 2000 computers in 120 retail locations. After the data is moved into the data warehouse, the tables are truncated. You want to configure the data import process so that you minimize the time needed to import the sales data and administer the database. What should you do? A. Use the Simple Recovery model and the FOR LOAD option to create the database. Create a Data Transformation Services (DTS) package that uses the BULK INSERT statement to copy the sales data. B. Index the foreign key fields in the child tables. C. Create a Data Transformation Services (DTS) package that uses a Data Driven Query task to copy the sales data. D. Collect the sales data by using a distributed transaction that inserts the data from all 120 retail locations into the RetailImport database as a single transaction. E. On the servers in the retail locations, create stored procedures that submit the updates to the RetailImport database each night as SQL-XML updategrams. Answer: A Reason: 和35题一样。 ---- / <>< o /| / <>< (o / |/| ) ) <@ < ( O )( \ |\| <>< ) <>< () \| \ \ 每天在网易游水的鱼 |
|
[关闭][返回] |