MCTS - Exam 70-431 - SQL Server 2005 - Implementation and Maintenance

MCTS - Exam 70-431 - SQL Server 2005

MCTS - Exam 70-431 - SQL Server 2005 - Implementation and Maintenance - Practice Test -1

Candidates for this exam are professionals who typically pursue careers as database administrators, database developers, or business intelligence developers. The actual test (as of Jun 2007) consists of about 35-40 MCQs and 12-15 simulation questions. For more information, please visit Microsoft Learning Website

1.	Your company has decided it is time to upgrade to SQL Server 2005. You currently run SQL Server 7.0 SP3. What do you need to do before you can upgrade?
	Nothing; you can upgrade directly to SQL Server 2005.
	Upgrade to SQL Server 2000, and then you can upgrade to SQL Server 2005
	Upgrade to SQL Server 2000, install SQL Server 2000 SP3, and then upgrade to SQL Server 2005
	Install SQL Server 7.0 SP 4, and then upgrade to SQL Server 2005
2.	One of the databases you will be using on your new SQL Server holds data in several different languages, including U.S. English, German, and Italian. Users will primarily search for data in their own language but occasionally search for data in other languages. You want to be able to sort through data as quickly as possible, and you are not concerned with sensitivity. Which sort order is best?
	Binary
	Binary code point
	Binary without the case-sensitivity option
	Binary code point without the case-sensitivity option
3.	You are the administrator of a SQL Server 2005 server that contains a development database. Your developers are concerned only with recovering the database schema in the event of a disaster, not the data. You are concerned with saving as much disk space as possible, and you do not want to back up anything unnecessarily. What recovery model should you use?
	Simple
	Bulk-Logged
	Full
4.	You have a table that contains employee data. One of the columns, named PayRate, contains the pay rate for each employee. You need to partition the table into three divisions, one for employees that make less than 65,000, one for employees that make 65,001 to 85,000, and one for employees that make 85,0001 and higher. Which function should you use?
	Use the following: CREATE PARTITION FUNCTION pfSalary (money) AS RANGE RIGHT FOR VALUES (65000,85000);
	Use the following: CREATE PARTITION FUNCTION pfSalary (money) AS RANGE RIGHT FOR VALUES (65001,85001);
	Use the following: CREATE PARTITION FUNCTION pfSalary (money) AS RANGE LEFT FOR VALUES (65000,85000);
	Use the following: CREATE PARTITION FUNCTION pfSalary (money) AS RANGE LEFT FOR VALUES (65001,85001);
5.	Your SQL Server resides in a different building on the campus where you work, and you are not always able to get there quickly when there is a problem with the server. You need to be able to connect to the dedicated administrator connection from your desktop. What should you do to enable this?
	Use sp_configure 'remote admin connections', 1
	Use sp_configure 'remote DAC', 1
	Use sp_configure 'remote admin connections', 0
	Nothing; you can access the DAC remotely by default
6.	You have a machine that has an 800MHz Pentium III processor with 256MB of RAM and a 400GB hard drive running Windows Server 2000 SP4. Which editions of SQL Server 2005 can you install? (Choose all that apply)
	Express Edition
	Workgroup Edition
	Standard Edition
	Enterprise Edition
	Developer Edition
7.	You are installing a new SQL Server 2005 instance on a machine in a small network. This is the only SQL Server on the network, and you want to make administration as simple as possible. Which service account type should you use?
	Built-in system account
	Local system account
	Domain account
8.	You have created a view with the following code: CREATE VIEW PayRate AS SELECT FirstName, LastName, Phone, Pay FROM HumanResources.dbo.Employees What changes do you need to make to this code to make this view indexable?
	No changes are needed; the view is already indexable
	Change the code to look like this: CREATE VIEW PayRate WITH SCHEMABINDING AS SELECT FirstName, LastName, Phone, Pay FROM HumanResources.dbo.Employees
	Change the code to look like this: CREATE VIEW PayRate WITH SCHEMABINDING AS SELECT FirstName, LastName, Phone, Pay FROM dbo.Employees
	Change the code to look like this: CREATE VIEW PayRate AS SELECT FirstName, LastName, Phone, Pay FROM dbo.Employees
9.	You have several developers on staff who need to be able to create objects in the development database as part of their regular duties. They should not be able to modify anything other than the database schema and the data in the database. What is the most efficient and secure way to give the developers the permissions they need?
	Add them to the db_owner fixed database role
	Add them to the db_ddladmin fixed database role
	Add them to the sysadmin fixed server role
	Grant each developer the permission to create objects in the database separately, and instruct them to create objects as DBO
10.	You have just installed two new 40GB hard disks in your server that you are going to use to hold a database named Inventory. You need to add, update, and delete data as fast as possible. How should you configure these hard disks? (Choose two)
	Configure the hard disks as a RAID-1 array
	Configure the hard disks as a RAID-0 array
	Configure the hard disks as a RAID-5 array
	Configure the hard disks as two independent drives
	Place the data files and log files on the same volume
	Place the data file on the first volume and the log file on the second volume

Top

www.mahipalreddy.com

Terms and Conditions of Use