70_432_Book_Questions

Front 1

Quick Check: Which editions support the entire feature set available within the SQL Server data platform? Of these editions, which editions are not licensed for production use?

Back 1

Enterprise, Developer, and Evaluation editions have the entire set of features available within the SQL Server 2008 data platform. Developer and Evaluation editions are not licensed for use in a production environment.

Front 2

Quick Check: Which editions of SQL Server are designed as storage engines for embedded applications with limited hardware and feature support?

Back 2

Express and Compact editions are designed as storage engines for embedded applications and support only a single CPU, up to 1 GB of RAM, and a maximum database size of 4 GB.

Front 3

Quick Check: Which edition of SQL Server supports installing more than one instance of SQL Server on a machine?

Back 3

Only SQL Server Enterprise supports multiple instances on the same machine.

Front 4

Quick Check: What are the authentication modes that SQL Server can be configured with?

Back 4

You can configure SQL Server to operate under either Windows Only or Windows and SQL Server authentication modes.

Front 5

Quick Check: What are the two basic components of Database Mail?

Back 5

Database mail uses mail profiles which can contain one or more mail accounts.

Front 6

Quick Check: What are the two types of mail profiles that can be created?

Back 6

Mail profiles can be configured as either public or private.

Front 7

Quick Check: What are the types of files that you create for databases and what are the commonly used file extensions?

Back 7

You can create data and log files for a database. Data files commonly have either an .mdf or .ndf extension, whereas log files have an .ldf extension.

Front 8

Quick Check: What is the purpose of the transaction long?

Back 8

The transaction log records every change that occurs within a database to persist all transactions to disk.

Front 9

Quick Check: How do you restrict database access to members of the db_owner role and terminate all active transactions and connection at the same time?

Back 9

You would execute the following command: ALTER DATABASE <database name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

Front 10

Quick Check: What backups can be executed for a database in each of the recovery models?

Back 10

You can create full, differential, and file/filegroup backups in the Simple recovery model. The bulk-logged recovery model allows you to execute types of backups, but you cannot restore a database to a point in time during an interval when a minimally logged transaction is executing. All types of backups can be executed in the Full recovery model.

Front 11

Quick Check: Which option should be enabled for all production databases?

Back 11

You should set the PAGE_VERIFY CHECKSUM option for all production databases.

Front 12

Quick Check: What checks does DBCC CHECKDB perform?

Back 12

DBCC CHECKDB checks the logical and physical integrity of every table, index, and indexed view within the database, along with the contents of every indexed view, page allocations, Service Broker data, and database catalog.

Front 13

Quick Check: How do you design a database?

Back 13

The ruling principle for designing a database is "Put thing where they belong." If the need is to store multiple rows of information that link back to a single entity, you need a separate table for those rows. Otherwise, each table defines a major object for which you want to store data and the columns within the table define the specific data that you want to store.

Front 14

Quick Check: What are three new options that you can configure for columns, rows, or pages within a table?

Back 14

You can designate columns as SPARSE to optimize the storage of NULLs. You can apply the FILESTREAM property to VARBINARY(MAX) column to enable the storage of documents in a directory on the operating system that exceed 2 GB. Rows can be compressed to fit more rows on a page. Pages can be compressed to reduce the amount of storage space required for the table, index, or indexed view.

Front 15

Quick Check: What is the difference between a primary key and a unique constraint?

Back 15

A primary key does not allow NULLs.

Front 16

Quick Check: What restrictions does the parent table have when creating a foreign key?

Back 16

The parent table must have a primary key that is used to define the relationship between the parent and child tables. In addition, if the parent's primary key is defined on multiple columns, all the columns must exist in the child table for the foreign key to be created.

Front 17

Quick Check: What type of structure does SQL Server use to construct an index?

Back 17

SQL Server uses a B-Tree structure for indexes.

Front 18

Quick Check: What are the three types of pages within an index?

Back 18

An index can contain root, intermediate and leaf pages. An index has a single root page defined at the top of the index structure. An index can have one or more levels of intermediate pages, but it is optional. The leaf pages are the lowest-level page within an index.

Front 19

Quick Check: What is the difference between and clustered and non clustered index?

Back 19

A clustered index imposes a sort order on the data pages in the table. A nonclustered index does not impose a sort order.

Front 20

Quick Check: How does the FILLFACTOR option affect the way an index is built?

Back 20

The FILLFACTOR option reserves space on the intermediate and leaf levels of the index.

Front 21

Quick Check: What is the difference between the REBUILD and REORGANIZE options of the ALTER INDEX?

Back 21

REBUILD defragments all levels of an index. REORGANIZE defragments only the leaf level of the index.

Front 22

Quick Check: What happens when an index is disabled?

Back 22

An index that is disabled is no longer used by the optimizer. In addition, as data changes in the table, any disabled index is not maintained.

Front 23

Quick Check: Before you can create a full text index, which structure do you need to create?

Back 23

Full text indexes are contained within full text catalogs. Therefore, you must create a full text catalog prior to creating a full text index.

Front 24

Quick Check: What do you need to specify to create a full text index on documents stored within a FILESTREAM?

Back 24

SQL Server stores the documents within a VARBINARY(MAX) column with the FILESTREAM property enabled. In order to create a full text index, you need to also specify a type column that designates what type of document is stored in the VARBINARY(MAX) column to load the appropriate filter for the word breaker to use.

Front 25

Quick Check: Which predicate performs fuzzy searching by default?

Back 25

FREETEXT and FREETEXTTABLE predicates perform wildcard searches by default.

Front 26

Quick Check: Which predicate is used to perform proximity and synonym searches?

Back 26

CONTAINS and CONTAINSTABLE are used for proximity, thesaurus, and inflectional searches.

Front 27

Quick Check: Which type of files enable searching based on synonyms?

Back 27

A thesaurus file allows you to configure synonyms for search arguments

Front 28

Quick Check: What do you configure to exclude words from your index and search arguments?

Back 28

A stop list contains the list of words that you want excluded from a full text index as well as from search arguments.

Front 29

Quick Check: What data types cannot be used with partition functions?

Back 29

You cannot use text, ntext, image, xml, varbinary(max), varchar(max) or any CLR data types.

Front 30

Quick Check: What is the maximum number of partitions allowed for a table?

Back 30

The maximum number of partitions for a table is 1,000.

Front 31

Quick Chick: What is the maximum number of boundary points allowed for a partition function?

Back 31

The maximum number of boundary points for a partition function is 999.

Front 32

Quick Check: How many filegroups can you specify if you use the ALL keyword when defining a partition scheme?

Back 32

You can specify exactly one filegroup when using the ALL keyword.

Front 33

Quick Check: Can you create a new filegroup at the same time that you are creating a partition scheme?

Back 33

No. Any filegroups that you specify in the CREATE PARTITION SCHEME statement must already exist in the database.

Front 34

Quick Check: What property must be set to use a computed column as a partitioning key?

Back 34

A computed column must be PERSISTED

Front 35

Quick Check: Which clause of the CREATE TABLE or CREATE INDEX statements is used to partition the object?

Back 35

The ON clause is used to specify the storage structure, filegroup or partition scheme, for the table or index.

Front 36

Quick Check: Which operators are used to add or remove boundary points from a partition function?

Back 36

The SPLIT operator is used to introduce a new boundary point. The MERGE operator is used to remove a boundary point.

Front 37

Quick Check: Which operators is used to move partitions between tables?

Back 37

The SWITCH operator is used to move partitions between tables.

Front 38

Quick Check: What are the data formats that BCP supports and the command line switches for each format?

Back 38

BCP can work with data in either a character or native format. The -c switch designates character mode while the -n switch is used for native mode.

Front 39

Quick Check: Which parameter do you specify to export data using a query?

Back 39

The queryout parameter is used to export the result set of a query.

Front 40

Quick Check: The import and Export Wizard is based on which feature of SQL Server?

Back 40

The Import and Export Wizard uses a subset of the SSIS feature.

Front 41

Quick Check: Which sources and destinations is the Import and Export Wizard capable of using?

Back 41

You can define any source or destination for which you have an OLE DB Provider.

Front 42

Quick Check: What are the five objects that are used within Policy Based management?

Back 42

The objects that are used with PBM are facets, conditions, policies, policy targets and policy categories.

Front 43

Quick Check: What are the allowed execution modes for a policy?

Back 43

On demand, on schedule, on change - log only, on change - prevent.

Front 44

Quick Check: which object has a property that allows you to mandate checking for all databases on an instance?

Back 44

Policy category -- policy categories allow you to mandate checking of all databases within an instance.

Front 45

Quick Check: How many facets can be checked within a single condition?

Back 45

A condition can be defined with only one facet.

Front 46

Quick Check: How many conditions can be checked within a single policy?

Back 46

A policy can check only a single condition.

Front 47

Quick Check: What are the four type of backups?

Back 47

You can execute full, differential, transaction log, and file/filegroup backups. A full backup is required before you can create a differential or transaction log backup.

Front 48

Quick Check: How can you detect and log corrupt pages?

Back 48

Execute ALTER DATABASE <database name> SET PAGE_VERIFY CHECKSUM

Front 49

Quick Check: Which recovery model always allows you to restore to the point of fail so long as you can back up the tail of the log?

Back 49

The Full recovery model.

Front 50

Quick Check: What is the first operation that should be performed for any restore operation?

Back 50

Back up the tail of the log.

Front 51

Quick Check: Which two features are incompatible with Database Snapshots?

Back 51

FILESTREAM and full-text indexes

Front 52

Quick Check: Prior to reverting a database using a Database Snapshot, what must you do?

Back 52

You must drop all Database Snapshots except the Database Snapshot being used as the source for the restore command.

Front 53

Quickcheck: If a job fails, where can you look to diagnose the problem?

Back 53

The first place to look is in the job history, which can be accessed from SQL Server management Studio (SSMS) by right-clicking a job and selecting View History. You can also look in the logging files that are configured for each job step. In some cases, you might find additional information in the Microsoft Windows event logs.

Front 54

Quickcheck: What types of job steps can be executed?

Back 54

You can create jobs that execute T-SQL, ActiveX Scripts, OS commands, or executive feels. You can also configure specific tasks for replication, Analysis Services, and Integration Services.

Front 55

Quick Check: What are the three types of alerts that can be created?

Back 55

You can create alerts on performance counters, SQL Server Errors and WMI queries.

Front 56

Quick Check: What are the two response actions that can be configured for an alert?

Back 56

You can have an alert send a notification or execute a job in response to the alert condition.

Front 57

Quick Check: What are the two parts of an endpoint?

Back 57

Payload and Transport. An endpoint ahs transport defined as either TCP or HTTP and ahs a payload defined as TSQL, SERVICE_BROKER, DATABASE_MIRRORING, or SOAP.

Front 58

Quick Check: What are the three states of an endpoint, and what is the difference between each state?

Back 58

The three states are STARTED, STOPPED, AND DISABLED. An endpoint that is STARTED listens for and allows connections. An endpoint that is STOPPED listens for connection request and returns an error message. An endpoint that is DISABLED does not respond to any request.

Front 59

Quick Check: What authority must be granted before an endpoint allows a connection request?

Back 59

To allow a connection request, the login that is being used must have been granted the CONNECT permission on the endpoint.

Front 60

Quick Check: How do you configure an instance so that only local connections are allowed?

Back 60

The TCP/IP provider enables connections to be created to the instance remotely. By disabling the TCP/IP provider, you can create only local connections to the instance.

Front 61

Quick Check: What do you use to enable or disable features for an instance?

Back 61

The sp_configure system stored procedure is used to enable or disable features.

Front 62

Quick Check: Which logins cannot be used to authenticate to an instance?

Back 62

You cannot use logins that are mapped to a certificate or asymmetric key to authenticate to an instance

Front 63

Quick Check: What database principal was created as a replacement for an application role?

Back 63

loginless users are the replacement for an application role.

Front 64

Quick Check: How are principals, securables and permissions related?

Back 64

You can GRANT, REVOKE, or DENY permissions ON a securable TO a principal

Front 65

Quick Check: what is an ownership chain, and how can you have a broken ownership chain?

Back 65

An ownership chain applies to objects that reference other objects within a database. The owner of the schema that contains the object is considered the owner of the object. SQL Server checks permissions for the first object that you access, as well as each time the owner changes within the calling stack. The chain of object owners within a calling stack is called an ownership chain. You have a broken ownership chain when the object owner changes within a calling stack and you have not been granted sufficient permissions to continue accessing objects within the call stack.

Front 66

Quick Check: Which object can be used to audit as well as prevent most object changes?

Back 66

DDL Triggers can audit any DDL command. If the DDL command executes within a transaction, a DDL trigger can be used to rollback the ddl and prevent the change from occurring.

Front 67

Quick Check: Which object is required before you can create a server or database audit specification?

Back 67

You must create a server audit object before a server or database audit specification can be created.

Front 68

Quick Check: What object is required to implement TDE?

Back 68

You must create a certificate in the master database that is used to encrypt the database encryption key.

Front 69

Quick Check: What do you need to do to a hash algorithm to increase the complexity when the range of possible encryption value is so small?

Back 69

If the range of possible values to encrypt is small, you need to salt the hash value in order to default brute force attacks.

Front 70

Quick Check: What are the items that you can capture data for with the System Monitor?

Back 70

You can capture object, counters, and counter instances.

Front 71

Quick Check: What types of data can System Monitor capture?

Back 71

System monitor captures numberic data for performance counters that are defined for hardware or software components.

Front 72

Quick Check: What are the three counters that, by themselves, indicate a system problem?

Back 72

System:Processor Queue Llength, Network Interface: output Queue Length, and Physical Disk: Avg. Disk Queue Length.

Front 73

Quick Check: What are the three items that you define within a trace?

Back 73

You define events, data columns, and filters within a trace.

Front 74

Quick Check: Which events are commonly used to establish a performance baseline?

Back 74

The RPC:Completed and SQL:BatchCompleted events are used to establish a performance baseline.

Front 75

Quick Check: What are the main places to find error and informational messages about the database engine?

Back 75

you can use the SQL Server error log and Windows Application Event log for messages about the database engine. If you are auditing logins and logouts from the instance, you can use SQL Server error log and Windows Security log. The Windows System Event log can also provide hardware and operating system information useful to troubleshooting a database engine issue.

Front 76

Quick Check: What are the three error codes that are raised when you run out of space for a database?

Back 76

You receive a 9002 error when you run out of transaction log space and either an 1101 or 1105 error when the data files are out of space.

Front 77

Quick Check: What does a device a activation error means?

Back 77

A device activation errors means that SQL Server either cannot find or cannot access a data or log file.

Front 78

Quick Check: Errors in which three databases prevent SQL Server from starting?

Back 78

Errors in the master, tempdb, and mssqlsystemresource databases can prevent an instance from starting. Errors in all other database just make the problem database inaccessible until you can fix the problems.

Front 79

Quick Check: What errors do you see if your disk storage goes off-line underneath a database?

Back 79

When a disk volume that databases are stored on goes off-line, SQL Server begins logging device activation errors.

Front 80

Quick Check: What errors do you see if there is a fault in either the memory or processor?

Back 80

If you are encountering memory or process problems, you see STOP errors. If there is a memory error encountered when the computer is booting, you see a POST error. Both STOP and POST are accompanied by a blue screen with additional diagnostic information.

Front 81

1. You are deploying a new server within Wide World Importers that will be running a SQL Server 2008 Instance in support of a new application. Because of the feature support that is needed, you will be installing SQL Server 2008 Enterprise. Which operating systems will support your installation? (Choose all that apply)
(A) Windows 2008 Server Enterprise SP4 or Higher
(B) Windows Server 2003 Enterprise
(C) Windows Server 2003 Enterprise SP2
(D) Windows Server 2008 Enterprise

Back 81

Correct Answers: C and D
(A) Incorrect: SQL Server 2008 is not supported on Windows 2000
(B) Incorrect: SQL Server 2008 is supported on Windows Server 2003 Enterprise, but you must have the SP2 version or later installed.
(C) Correct: SQL Server 2008 Enterprise is supported on Windows Server 2003 Enterprise SP2 and later or Windows Server 2008 Enterprise.
(D) Correct: SQL Server 2008 Enterprise Edition is supported on Windows Server 2003 Enterprise SP2 and later or Windows Server 2008 Enterprise.

Front 82

2. You are deploying SQL Server 2008 Express in support of a new Web-based application that will enable customers to order directly from Coho Vineyards. Which operating system does NOT support your installation.
(A) Windows XP Home Edition SP2
(B) Windows Server 2008 Server Core
(C) Windows Server 2003 Enterprise SP2
(D) Windows XP Tablet Edition SP2

Back 82

Correct Answer: B
(A) Incorrect: SQL Server 2008 Express is supported on Windows XP Home Edition SP2
(B) Correct: SQL Server 2008 Express is not supported on Windows Server 2008 Server Code.
(C) Incorrect: SQL Server 2008 Express is supported on Windows Server 2003 Enterprise SP2.
(D) Incorrect: SQL Server 2008 Express is supported on Windows XP Tablet Edition SP2, but it is not the optimal operating system to use.

Front 83

1. Margie's Travel is opening a new division to offer online travel bookings to their customers. Managers expect the traffic volume to increase rapidly, to the point where hundreds of users will be browsing offerings and booking travel at any given time. Management would also like to synchronize multiple copies of the database of travel bookings to support both online and face-to-face operations. Which editions of SQL Server 2008 would be appropriate for Margie's Travel to deploy for their new online presence? (Choose all that apply).
(A) Express
(B) Standard
(C) Enterprise
(D) Compact

Back 83

Correct Answers: B and C
(A) Incorrect: Although Express could probably handle the user load for Margie's Travel, it is not capable of acting as a publisher to synchronize multiple copies of a travel bookings database.
(B) Correct: Standard can handle the user load needed and supports the replication features needed to synchronize databases.
(C) Correct: Enterprise can scale to handle any load as well as provide any capability needed by Margie's travel.
(D) Incorrect: Compact is not designed to be used as a storage engine for Web-Based applications.

Front 84

2. Margie's Travel decided to minimize the cost and deploy SQL Server 2008 Standard to support the new online division. After a successful launch, managers are having a hard time managing business operations and need to deploy advanced analytics. A new server running SQL Server will be installed. Which edition of SQL Server needs to be installed on the new server to support the necessary data analytics?
(A) SQL Server 2008 Standard
(B) SQL Server 2008 Express with Advanced Services
(C) SQL Server 2008 Workgroup
(D) SQL Server 2008 Enterprise

Back 84

Correct Answer: D
(A) Incorrect: Only SQL Server 2008 Enterprise supports all the advanced analytics needed, such as OLAP and Data Mining.
(B) Incorrect; Only SQL Server 2008 Enterprise supports all the advanced analytics needed, such as OLAP and Data Mining.
(C) Incorrect: Only SQL Server 2008 Enterprise supports all the advanced analytics needed, such as OLAP and Data Mining.
(D) Correct: Only SQL Server 2008 Enterprise supports all the advanced analytics needed, such as OLAP and Data Mining.

Front 85

1. Wide World Importers will be using the new FILESTREAM data type to store scanned images of shipping manifests. Which command must be executed against the SQL Server instance before FILESTREAM data can be stored?
(A) ALTER DATABASE
(B) DBCC
(C) sp_configure
(D) sp_filestream_configure

Back 85

Correct Answer: D
(A) Incorrect: An ALTER DATABASE command does not enable FILESTREAM support
(B) Incorrect: A DBCC command does not enable FILESTREAM support
(C) Incorrect: While sp_configure can be used to change the support level for FILESTREAM data, it does not allow full configuration of options such as the share name to be used.
(D) Correct: sp_filestream_configure is used to enable the FILESTREAM access level as well as configure the Windows share to be used.

Front 86

2. Contoso has implemented a new policy that requires the passwords on all service accounts to be changed every 30 days. Which tool should the Contoso database administrators use to change the service account passwords so that SQL Server services comply with the new policy?
(A) Windows Service Control applet
(B) SQL Server Management Studio
(C) SQL Server Configuration Manager
(D) SQL Server Surface Area Configuration Manager

Back 86

Correct Answer: C
(A) Incorrect: Although the Windows Service Control applet can be used to change service account passwords, you should not use this utility. Only the SQL Server Configuration Manager has the code to decrypt and re-encrypt the service master key used by SQL Server services correctly when the service account or password is changed.
(B) Incorrect: SQL Server Management Studio cannot be used to change service account passwords.
(C) Correct: Only the SQL Server Configuration Manager has the code to decrypt and re-encrypt the service master key used by SQL Server services correctly when the service account or password is changed.
(D) Incorrect: SQL Server Surface Area Configuration Manager was a utility that existed in SQL Server 2005 and was removed in SQL Server 2008 in favor of making configuration changes directly using the sp_configure system stored procedure.

Front 87

1. As part of the implementation of the new Web-based booking system at Margie's Travel, customers should receive notices when a travel booking has been successfully saved. What technologies or features can the developers at Margie's Travel use to implement notifications? (Choose all that apply)
(A) Notification Services
(B) Database Mail
(C) Microsoft Visual Studio.NET Code Libraries
(D) Activity Monitor

Back 87

1. Correct Answers: B and C
(A) Incorrect: Notification Services is a feature that was available with SQL Server 2005 that is no longer available in SQL Server 2008.
(B) Correct: Database Mail can be used to send messages to customers.
(C) Correct: A Visual Studio .NET application can be created to use the mail libraries available within the .NET Framework.
(D) Incorrect: Activity Monitor is a feature of SSMS that displays query activity on the server; it cannot be used to send messages.

Front 88

2. The developers at Margie's Travel have decided to utilize Database Mail to send messages to their customers. The ability to send mail messages through a given profile needs to be restricted, but it must not require an approved user to specify a mail profile when sending messages. What settings need to be configured to meet these requirements? (Choose all that apply)
(A) Set the mail profile to public.
(B) Set the mail profile to private.
(C) Set the mail profile to private and grant access to approved users.
(D) Designate the mail profile as the default

Back 88

2. Correct Answers: C and D
(A) Incorrect: A public profile can be accessed by anyone with the authority to send mail, which violates the security requirements.
(B) Incorrect: Although configuring a mail profile as private will restrict access, the profile cannot be used unless a user has been granted access to the profile.
(C) Correct: You need to configure the mail profile to be private along with granting access to the mail profile for approved users.
(D) Correct: Designating a mail profile as the default allows approved users to send mail using the profile without the need to specify the profile explicitly.

Front 89

1. You have a reference database named OrderHistory, which should not allow any data to be modified. How can you ensure, with the least amount of effort, that users can only read data from the database?
A. Add all database users to the db_datareader role.
B. Create views for all the tables and grant select permission only on the views to database users.
C. Set the database to READ_ONLY
D. Grant select permission on the database to all users and revoke insert, update, and delete permissions from all users on the database.

Back 89

1. Correct Answer: C
A. Incorrect: members of the db_owner role are still able to change data in the database.
B. Incorrect: members of the db_owner role are still able to change data in the database.
C. Correct: Unless the database is in READ_ONLY mode, members of the db_owner role can still change data in the database.
D. Incorrect: members of the db_owner role are still able to change data in the database.

Front 90

1. You are the database administrator at Blue Yonder Airlines and are primarily responsible for the Reservations database, which runs on a server running SQL Server 2008. In addition to customers booking flights through the company's Web site, flights can be booked with several partners. Once an hour, the Reservations database receives multiple files from partners, which are then loaded into the database using the Bulk Copy Program (BCP) utility. You need to ensure that you can recover the database to any point in time while also maximizing the performance of import routines. How would you configure the database to meet business requirements?
A. Enable AUTO_SHRINK
B. Set PARAMETERIZATION FORCED on the database
C. Configure the database in the Bulk-logged recovery model
D. Configure the database in the Full recovery model

Back 90

1. Correct Answer: D
A. Incorrect: The AUTO_SHRINK option does not ensure that the database can be recovered to any point in time.
B. Incorrect: Forced parameterization does not ensure that the database can be recovered to any point in time.
C. Incorrect: While the bulk-logged recovery model allows maximum performance and you can still create transaction log backups, you cannot recover a database to a point in time during which a minimally logged operation is executing.
D. Correct: The full recovery model ensures that you can always recover the database to any point in time.

Front 91

1. Which commands are executed when you run the DBCC CHECKDB command? (Check all the apply)
A. DBCC CHECKTABLE
B. DBCC CHECKIDENT
C. DBCC CHECKCATALOG
D. DBCC FREEPROCCACHE

Back 91

Correct Answers: A and C
A. Correct: A DBCC CHECKDB command executes DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKCATALOG.
B. Incorrect: DBCC CHECKIDENT is used to check, fix, or reseed an identity value.
C. Correct: A DBCC CHECKDB command executes DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKCATALOG.
D. Incorrect: DBCC FREEPROCCACHE clears the contents of the query cache.

Front 92

1. Which options are not compatible with row or page compression? (Choose two. Each forms a separate answer.)
A. A column with a VARCHAR(MAX) data type
B. A sparse column
C. A table with a column set
D. A VARBINARY(MAX) column with the FILESTREAM property

Back 92

Which options are not compatible with row or page compression? (Choose two. Each forms a separate answer.)
A. A column with a VARCHAR(MAX) data type INCORRECT: You can apply page compression to a table that have a VARCHAR(MAX) data type, however, the data within the VARCHAR(MAX) column is not compressed.
B. A sparse column CORRECT: You cannot use either row or page compression with a table that has a sparse column.
C. A table with a column set CORRECT: A column set is constructed for a group of sparse columns, so because row and page compression is incompatible with sparse columns, it is also incompatible with a column set.
D. A VARBINARY(MAX) column with the FILESTREAM property INCORRECT: You can apply row or page compression to a table that has a FILESTREAM column, but the data in the FILESTREAM column will not be compressed.

Front 93

1. Columns with which properties cannot be sparse columns? (Choose 2. Each forms a separate answer)
A. FILESTREAM
B. NULL
C. NOT FOR REPLICATION
D. COLLATE

Back 93

Columns with which properties cannot be sparse columns? (Choose 2. Each forms a separate answer)
Correct answers: A and C
A. FILESTREAM CORRECT: ROWGUIDCOL, IDENTITY and FILESTREAM are not allowed to be used with SPARSE columns. Because the NOT FOR REPLICATION option is applied to an identity column, you cannot have a column that is SPARSE, which also has the NOT FOR REPLICATION option. In addition, a column must allow NULLs to be designated as a sparse column.
B. NULL. INCORRECT: Sparse columns must allow NULLs.
C. NOT FOR REPLICATION CORRECT: ROWGUIDCOL, IDENTITY and FILESTREAM are not allowed to be used with SPARSE columns. Because the NOT FOR REPLICATION option is applied to an identity column, you cannot have a column that is SPARSE, which also has the NOT FOR REPLICATION option. In addition, a column must allow NULLs to be designated as a sparse column.
D. COLLATE INCORRECT: A sparse column can have a collation sequence specified.

Front 94

1. Fabrikam stores product information in the following table:
CREATE TABLE Products.Product
(ProductID INT IDENTITY (1,1),
ProductName Varchar(30) NOT NULL,
SKU CHAR(8) NOT NULL,
Cost MONEY NOT NULL,
ListPrice MONEY NOT NULL,
ShortDescription VARCHAR(200) NOT NULL,
LongDescription VARCHAR(MAX) NULL,
CONSTRAINT pk_product PRIMARY KEY CLUSTERED (ProductID))

The table is queried either by ProductID, ProductName or SKU. The application displays ProductName, SKU, ListPrice, and ShortDescription. The ProductID is also returned to facilitate any subsequent operations. Several thousand new products were recently added and now you have performance degradation. Which index should you implement to provide the greatest improvement in query performance?
A. CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductID, ProductName, SKU)
B. CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductName)
C. CREATE NONCLUSTERED INDEX idx_product on Products.Product (ProductName) INCLUDE (SKU, ListPrice, ShortDescription, ProductID)
D. CREATE NONCLUSTERED INDEX idx_product ON Products.Product (ProductName, SKU, ProductID, ListPrice, ShortDescription)

Back 94

Correct Answers: B and C
A. Incorrect: You can apply page compression to a table that have a VARCHAR(MAX) data type; However, the data within the VARCHAR(MAX) column is not compressed.
B. Correct: You cannot use either row or page compression with a table that has a sparse column.
C. Correct: A column set is constructed for a group of sparse columns, so because row and page compression is compatible with sparse columns, it is also incompatible with a column set.
D. Incorrect: you can apply row or page compression to a table that has FILESTREAM column, but the data in the FILESTREAM column will not be compressed.

Front 95

1. You are the database administrator at a retail company that supplies blanks and kits to pen turners. You are designing a database to store characteristics of the products offered. Each product has a variety of characteristics, but not all products have the same set of characteristics. you are planning the index strategy for the database. The most common query will be following:
SELECT a.ProductName, b.ProductType, b.WoodSpecies, b.Color
FROM Products a INNER JOIN ProductAttributes b ON a.ProductID = b.ProductID
WHERE b.Color = "X"
Not all products have a Color attribute. Which index strategy would be the most efficient?
A. A nonclustered index on Color
B. A nonclustered index on Color that includes the ProductType and WoodSpecies columns
C. A filtered, nonclustered index on Color
D. A filtered, nonclustered index on Color that includes the ProductType and WoodSpecies columns

Back 95

1. Correct Answer: D
A. Incorrect: Because the Color column can be null, a filtered index is more efficient
B. Incorrect: Because the Color column can be null, a filtered index is more efficient
C. Incorrect: Although the filtered index on the Color column is more efficient than an unfiltered index, SQL Server must perform an additional read operation to retrieve the information in the ProductType and WoodSpecies columns
D. Correct: Because the Color column is nullable, the most efficient index for this query includes only the values that were not nullable. In addition, by including the ProductType and WoodSpecies columns, the query could be satisfied entirely by the index.

Front 96

1. You are in charge of building the process that loads approximately 150 GB of data into the enterprise data warehouse every month. Every table in your data warehouse has at least eight indexes to support data analysis routines. You want to load the data directly into the tables as quickly as possible. Which operation provides the best performance improvement with the least amount of administrative effort?
A. Use a BULK INSERT command
B. Drop and re-create the indexes
C. Disable and enable the indexes
D. Use Integration Services to import the data

Back 96

Correct Answer: C
A. Incorrect: Although a BULK INSERT statement can load data quickly, if the indexes exist on the table during the data load operation, you incur a very large overhead for the writes to the indexes.
B. Incorrect: Loading data to a table without any indexes and then creating the indexes after the load is more efficient than loading with the indexes in place. However, dropping and recreating the indexes takes more effort than disabling and re-enabling the indexes.
C. Correct: By disabling the indexes prior to the load, you avoid all the overhead required to maintain the indexes. By using the disable/enable method, you do not have to maintain scripts to recreate the indexes following the data load.
D. Incorrect: It is more efficient to load data into tables that do not have indexes. Integration services cannot overcome the overhead of index maintenance during the load operation.

Front 97

1. You are the database administrator at your company. You need to enable the sales support team to perform fuzzy searches on product descriptions. Which actions do you need to perform to satisfy user needs with the least amount of effort? (Choose two. Each forms part of the correct answer.)
A. Create a full text catalog specifying the filegroup for backup purposes and the root path to store the contents of the catalog on the file system.
B. Create a full text catalog and specify the filegroup to store the contents of the catalog.
C. Create a full text index on the table of the product descriptions for the description column and specify NO POPULATION
D. Create a full text index on the table of product descriptions for the description column and specify CHANGE_TRACKING AUTO.

Back 97

1. Correct Answers: B and D
A. Incorrect: SQL Server 2005 stored full text indexes on the file system. SQL Server 2008 stores full text indexes within a filegroup in the database.
B. Correct: Full text catalogs contain full text indexes and the contents of the indexes are stored within the database in SQL Server 2008.
C. Incorrect: The NO POPULATION option enables SQL Server to create the full text index but does not populate the index. Therefore do no return any results.
D. Correct: CHANGE_TRACKING AUTO option enables SQL Server to populate the full text index upon initial creation and migrate changes automatically to underlying data into the index.

Front 98

1. You want to configure your full text indexes such that SQL Server Migrates changes into the index as quickly as possible with the minimum amount of administrator effort. Which command should you execute?
A. ALTER FULLTEXT INDEX ON <table_name> START FULL POPULATION
B. ALTER FULLTEXT INDEX ON <table_name> START INCREMENTAL POPULATION
C. ALTER FULLTEXT INDEX ON <table_name> SET CHANGE_TRACKING AUTO
D. ALTER FULLTEXT INDEX ON <table_name> START UPDATE POPULATION.

Back 98

Correction Answer C:
A. Incorrect: The START (FULL | INCREMENTAL | UPDATE) POPULATION argument executes a population run for the full text index, but it must either be executed manually or configured to run in a SQL Server Agent job.
B. Incorrect: The START (FULL | INCREMENTAL | UPDATE) POPULATION argument executes a population run for the full text index, but it must either be executed manually or configured to run in a SQL Server Agent job.
C. Correct: When the CHANGE_TRACKING argument is set to AUTO, SQL Server automatically updates the full text index as changes to underlying data occur. In AUTO mode, no administrator intervention is required either manually or via a scheduled job.
D. Incorrect: The START (FULL | INCREMENTAL | UPDATE) POPULATION argument executes a population run for the full text index, but it must either be executed manually or configured to run in a SQL Server Agent job.

Front 99

1. You want to search for two terms based on proximity within a row. Which full text predicates can be used to perform proximity searches? (choose two. Each forms a separate answer)
A. CONTAINS
B. FREETEXT
C. CONTAINSTABLE
D. FREETEXTTABLE

Back 99

1. Correct Answers: A and C
A. Correct: CONTAINS allows proximity searches by using the NEAR keyword.
B. Incorrect: FREETEXT does not allow proximity searches.
C. Correct: CONTAINSTABLE allows proximity searches by using the NEAR keyword.
D. Incorrect: FREETEXTTABLE does not allow proximity searches.

Front 100

2. You want to perform a proximity search based on a weighting value for the search arguments. Which options for the CONTAINSTABLE predicate should you use?
A. FORMSOF with the THESAURUS keyword
B. FORMSOF with the INFLECTIONAL keyword
C. ISABOUT
D. ISABOUT with a WEIGHT keyword

Back 100

2. Correct Answer: D
A. Incorrect: The FORMSOF argument allows you to search based on a thesaurus or inflectional forms of a search term but dos not perform proximity searches.
B. Incorrect: The FORMSOF argument allows you to search based on a thesaurus or inflectional forms of a search term but dos not perform proximity searches.
C. Incorrect: The FORMSOF argument allows you to search based on a thesaurus or inflectional forms of a search term but dos not perform proximity searches.
D. Correct: ISABOUT performs proximity searches and it also applies weighting if the WEIGHT keyword and weighting value are supplied.

Front 101

1. You have a list of words that should be excluded from search arguments. Which action should you perform in SQL Server 2008 to meet your requirements with the least amount of effort?
A. Create a stop list and associate the stop list to the full text index.
B. Create a noise word file and associate the noise word file to the full text index.
C. Populate a thesaurus file and associate the thesaurus file to the full text index.
D. Parse the inbound query and remove any common words from the search arguments.

Back 101

1. Correct Answer: A
A. Correct: Stop lists are created in SQL Server 2008 to exclude words from a full text index as well as search arguments. After the stop list is associated to a full text index, any queries that use the index automatically have any stop words removed from the search arguments.
B. Incorrect: Noise word files were used in SQL Server 2005. SQL Server 2008 uses stop lists for the purpose of excluding words from search arguments.
C. Incorrect: A thesaurus allows terms to be replaced such as common abbreviations or misspellings but does not exclude words from being searched upon.
D. Incorrect: Although you could alter your application to remove search arguments, you would require more effort than creating, populating, and managing a stop list.

Front 102

1. Contoso has a very high-volume transaction system. There is not enough memory on the database server to hold the active data set, so a very high number of read and write operations are hitting the disk drives directly. After adding several additional indexes, the performance still does not meet expectations. Unfortunately, the DBAs cannot find any more candidates for additional indexes. There isn't enough money in the budget for additional memory, additional servers, or a server with more capacity. However, a new storage area network (SAN) has recently been implemented. What technology can Contoso use to increase performance?
A. Log shipping
B. Replication
C. Partitioning
D. Database Mirroring

Back 102

1. Correct Answer: C
A. Incorrect: Although log shipping allows additional copies of the database to be created, Contoso does not have any additional hardware to use.
B. Incorrect: Although replication allows additional copies of the database to be created, Contoso does not have any additional hardware to use.
C. Correct: You could partition the most heavily used tables, thus allowing you to spread the data across multiple files, which improves performance.
D. Incorrect: Although database mirroring creates an additional copy of the database, Contoso does not have any additional hardware to use.

Front 103

1. Margie's travel wants to keep orders in their online transaction processing database for a maximum of 30 days from the date an order is placed. The orders table contains a column called OrderDate that contains the date an order was placed. How can the DBAs at Margie's Travel move orders that are older than 30 days from the orders table with the least amount of impact on user transactions? (Choose two. Each answer represents a part of the solution)
A. Use the SWITCH operator to move data partitions containing data that is older than 30 days.
B. Create a stored procedure that deletes any orders that are older than 30 days.
C. Partition the order table using the partition function defined for a datetime data type using the OrderDate column.
D. Create a job to delete orders that are older than 30 days.

Back 103

1. Correct Answers: A and C
A. Correct: The SWITCH operator removes orders that are older than 30 days without causing any blocking.
B. Incorrect: you could execute a DELETE operation, but exclusive locks would be acquired that would affect the ability of customers to place orders.
C. Correct: Partitioning the OrderDate column allows you to use the SWITCH operator to move data that is older than 30 days off the table without causing any blocking.
D. Incorrect: You could execute a DELETE operation, but exclusive locks would be acquired that would affect the ability of customers to place orders.

Front 104

1. Wide World Importers has a very large and active data warehouse that is required to be accessible to users 24 hours a day, 7 days a week. The DBA team needs to load new sets of data on a weekly basis to support business operations. Inserting large volumes of data would affect users unacceptably. Which feature should be used to minimize the impact while still handling the weekly data loads?
A. Transactional replication
B. The SWITCH operator within partitioning
C. Database mirroring
D. Database snapshots

Back 104

1. Correct Answer: B
A. Incorrect: Transactional replication has the capability to move data into the tables within the data warehouse; however, locks are acquired during the insert process that would affect users.
B. Correct: The SWITCH operator is designed to move partitions of data into a table without causing blocking.
C. Incorrect: Database mirroring requires the mirror database to be offline and would not be a valid technology in this scenario.
D. Incorrect: Database snapshots provide a point in time, read only copy of a database, and would not reflect any new data that is added.

Front 105

1. Contoso Limited has a very high-volume order entry system. Management has determined that orders should be maintained in the operational system for a maximum of six months before being archived. After data is archived from the table it is loaded into the data warehouse. The data load occurs once per month. Which technology is the most appropriate choice for archiving data from the order entry system?
A. Database mirroring
B. Transactional replication
C. Database snapshots
D. Partitioning

Back 105

1. Correct Answer: D
A. Incorrect: Database mirroring keeps a secondary database synchronized, but the mirror is inaccessible. Therefore, it is inappropriate for archiving.
B. Incorrect: Transactional replication could be used to move the data to another system for loading into the data warehouse; however, you still need to delete data from the order entry system. This affects the concurrency and performance of the order entry system
C. Incorrect: Database snapshots maintain a read-only copy of the data at that point in time and is inappropriate for archiving.
D. Correct: By designing the table with partitioning, you can remove order data from the order entry system without affecting performance or concurrency. After you remove the partition from the table, you can load the data into the data warehouse.

Front 106

1. You want to import data into the Order table. The table has triggers and check constraints that you want to be checked to guarantee integrity. You choose to use the BCP utility and specify the -h "CHECK_CONSTRAINTS, FIRE_TRIGGERS" hint to accomplish your task. Which of the following permissions must be in place?
A. SELECT permission on the Orders table
B. ALTER TABLE on the Orders table
C. INSERT permission on the Orders table
D. A member of the bulkadmin role

Back 106

Correct Answers: A and C
A. Correct: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do no disable triggers and check constraints, you do not need ALTER TABLE permission.
B. Incorrect: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do no disable triggers and check constraints, you do not need ALTER TABLE permission.
C. Correct: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do no disable triggers and check constraints, you do not need ALTER TABLE permission.
D. Incorrect: If you are importing data, you need SELECT, INSERT, and ALTER TABLE permissions on the destination. However, if you do no disable triggers and check constraints, you do not need ALTER TABLE permission.

Front 107

2. You are performing a migration on the Order database at Contoso from Oracle to SQL server. The Order database contains several hundred tables. The CustomerAddress table has an XML column named AddressBook. What is the most efficient, least intrusive way to move the data to the new SQL Server database?
A. Move the Order database from Oracle to SQL Server using replication
B. Unload the data using Oracle utilities and load the data into SQL Server using BCP
C. Move the Order database using the Import and Export wizard
D. Move the data from Oracle to SQL Server using the OPENROWSET function.

Back 107

2. Correct Answer: C
A. Incorrect: Although it is possible to publish the tables from Oracle to SQL Server, it is more intrusive than using the Import and Export Wizard to move the data.
B. Incorrect: You need to code a BCP command for each table that you are importing data into, which is less efficient than using the Import and Export Wizard.
C. Correct: The Import and Export Wizard allows you to connect to Oracle, select multiple tables and move the data to SQL Server in a single operation.
D. Incorrect: The OPENROWSET can move a single table at a time, which is less efficient than using the Import and Export Wizard.

Front 108

1. You have defined several policies that you want applied to all databases within an instance. How do you ensure that a database owner is not allowed to avoid the policy check with the least amount of administrative effort?
A. Create a condition that checks all databases.
B. Add the policy to a user-defined policy category and set the Mandate property.
C. Add the policy to the default policy category
D. Check the policies manually against the instance.

Back 108

1. Correct Answer: C
A. Incorrect: Even in you create a condition that checks all databases, a database owner can choose not to subscribe to a policy unless you have mandated compliance.
B. Incorrect: While you could create policy category that has the Mandate property enabled and add the policy to the category, it requires more effort than just adding the policy to the default policy category.
C. Correct: The default category is configured with the Mandate property enabled. In addition, you cannot disable the Mandate property.
D. incorrect: While you could check the policies manually, this requires more effort that adding the policy to the default category.

Front 109

1. You are the DBA for Fabrikam. The Orders database is critical to company operations and is set to the Full recovery model. You are running full backups daily at 1 a.m., differential backups every four hours beginning at 5 a.m., and transaction log backups every five minutes. if the Orders database were to become damaged and go offline, what is the first step in the restore process?
A. Restore the most recent full backup with the NORECOVERY option.
B. Restore the most recent differential backup with the NORECOVERY option.
C. Back up the transaction log with the NO_TRUNCATE option.
D. Back up the transaction log with the TRUNCATE_ONLY option.

Back 109

1. Correct Answer: C
A. Incorrect: Even if you create a condition that checks all databases, a database owner can choose not to subscribe to a policy unless you have mandated compliance.
B. Incorrect: While you could create a policy category that has the Mandate property enabled and add the policy to the category, it requires more effort than just adding the policy to the default policy category.
C. Correct: The default category is configured with the Mandate property enabled. In addition, you cannot disable the Mandate property.
D. Incorrect: While you could check the policies manually, this requires more effort that adding the policy to the default category.

Front 110

1. The server that Customers database is running on fails and needs to be replaced. You build a new server and install SQL Server 2008. When you built the new server, you decided that instead of configuring the new server exactly like the old one, you implement a new drive letter and folder structure for data and log files. Which option do you need to use when you restore the Customers database to the new server?

Back 110

A. Incorrect: While the NORECOVERY option allows you to restore subsequent differential and transaction log backups, the restore fails because SQL Server does not have a valid directory structure to restore the database.
B. Incorrect: The CONTINUE_AFTER_ERROR option is used to handle damaged backup media, not move files during a restore.
C. Correct: Because you have a new storage structure on the file system, you need to move the data and log files to new locations. The MOVE option allows you to specify a new location for files when you restore the full backup.
D. Incorrect: The PARTIAL option allows you to perform a partial restore, but does not move data files.

Front 111

1. A Database Snapshot can be created again which database? (Choose all that apply. Each answer is a complete solution)
A. master
B. A database with full text indexes
C. A database with FILESTREAM data
D. Distribution

Back 111

1. Correct Answers: B and C
A. Incorrect: You cannot create a database snapshot against a system database.
B. Correct: Although you cannot execute full-text queries against a database snapshot, you can create a snapshot against a database that contains full-text indexes.
C. Correct: Although FILESTREAM is inaccessible through a database snapshot, you can create a database snapshot against a database that is enable for FILESTREAM.
D. Incorrect: You cannot create a database snapshot against a system database.

Front 112

1. Where would you look to retrieve a list of jobs that have failed?
A. The Windows Event Log
B. The job history in SSMS
C. The SQL Server Agent error log
D. The SQL Server error log

Back 112

1. Correct Answer: B
A. Incorrect: The Windows event log might contain error information about a failed job, but you cannot get a list of failed jobs from the event log.
B. Correct: The job history within SSMS lists all the jobs that have executed, along with their execution status.
C. incorrect: The SQL Server Agent event log might contain error information about a failed job, but you cannot get a list of failed jobs from the error log.
D. Incorrect: The SQL Server error log might contain error information about a failed job, but you cannot get a list of failed jobs from the error log.

Front 113

1. Your Orders database crashed last night, and you have determined that the crash was caused by a data file running out of space. What tool do you use to send a notification to an administrator as well as expand the data file before it runs out of space?
A. SQL Server Agent
B. System Monitor
C. Event View
D. Network Monitor

Back 113

1. Correct Answer: A
A. Correct: You can configure a SQL Server Agent alert that notifies an operator as well as execute a job that can increase the space in the data file.
B. Incorrect: System Monitor cannot execute a process to expand the data file.
C. Incorrect: Event Viewer cannot execute a process to expand the data file.
D. Incorrect: Network Monitor cannot execute a process to expand the data file.

Front 114

1. You are the DBA for A. Datum Corporation. Users are complaining that applications cannot connect to the SQL Server. You have verified all the application settings and you can connect to the server from your desktop using SSMS, but users' applications keep returning an "Access denied" error message. What could be the problem?
A. The TCP endpoint for TSQL is DISABLED
B. The TCP endpoint for TSQL is STOPPED
C. Remote Connections are not enabled
D. Users do not have CONNECT permissions on the endpoint.

Back 114

1. Correct Answer: D
A. Incorrect: If the TCP endpoint for TSQL were disabled, you would not be able to connect to the SQL Server using SSMS, and users would not receive any error messages.
B. Incorrect: If the TCP endpoint for TSQL were stopped, you would not be able to connect to the SQL Server using SSMS, and users would not receive any error messages.
C. Incorrect: Remote connections were enabled because you can connect to SQL Server from your desktop.
D. Correct: If users do not have CONNECT permission on the TCP endpoint for TSQL, they receive "Access Denied" error message.

Front 115

2. You have configured a Database Mirroring Session within your environment. The Principal and Mirror endpoints were created successfully with a ROLE setting of PARTNER and then started. You have verified that you can connect to and authenticate to each endpoint. However, Database Mirroring fails to configure properly. What might be the problem?
A. The authentication mode is set to NTLM
B. The authentication mode is set to NEGOTIATE
C. The encryption setting is different on each endpoint
D. The encryption is set to AES on each endpoint

Back 115

Correct Answer: C
A. Incorrect: If the authentication mode is set the same on each endpoint, it does not prevent configuration.
B. Incorrect: If the authentication mode is set the same on each endpoint, it does not prevent configuration.
C. Correct: If you have verified the connection and authentication, only a mismatch of encryption sections prevents Database Mirroring from configuring.
D. incorrect: If the encryption is set the same on each endpoint, it does not prevent configuration.

Front 116

1. Which tool would you use to enable or disable SQL Server features?
A. SQL Server Configuration Manager
B. The sp_configure tool
C. SQL Server Surface Area Configuration Manager
D. SQL Server Installation Center

Back 116

Correct Answer: B
A. Incorrect: You can use SQL Server Configuration Manager to enable and disable remote connection, but you cannot use it to enable an disable features
B. Correct: The sp_configure tool is used to enable and disable features.
C. Incorrect: The SQL Server Surface Area Configuration Manager could be used to enable and disable features in SQL Server 2005. However, the utility has been removed from SQL Server 2008.
D. Incorrect: The SQL Server Installation Center is used to install, uninstall, and manage installations.

Front 117

1. Wide World Importers has a Windows Server 2003 domain and all the servers running SQL Server are running on Windows Server 2003 Enterprise edition. The SQL Server instance is configured for Windows-only authentication. Database roles have been created for each group of permissions within an database. Logins are added to the database roles. The DBAs want to move the security assignment of users to the owners of each application without giving up control of the accounts or permissions inside the SQL Server instance. How can the DBAs accomplish their goals? (Choose two. Each answer represents part of the solution.)
A. Have the Windows administrator allow application owners to manage the Windows groups associated to their applications.
B. Add the logins for application owners to the securityadmin role.
C. Map SQL Server logins to the Windows group corresponding to each application
D. Add the logins for application owners to the sysadmin role.

Back 117

Correct answers: A and C
A. Correct: You should map SQL Server logins to each Windows group corresponding to an application, add the login as a user to the appropriate database, and then add the user to the appropriate database role. After you complete these steps, all the application owners need is the ability to manage the appropriate Windows group or groups to meet your needs.
B. Incorrect: Members of the securityadmin role can execute CREATE LOGIN, ALTER LOGIN, and DROP LOGIN. However, role members can manage any login (except logins that are members of the sysadmin role) , not just the logins associated to a specific application.
C. Correct; You should map SQL Server logins to each Windows group corresponding to an application, add the login as a user to the appropriate database, and then add the user to the appropriate database role. After you complete these steps, all the application owners need is the ability to manage the appropriate Windows group or groups to meet your need.
D. Incorrect: If you add the application owners to the sysadmin role, you have given up control of the instance and inappropriately elevated permissions.

Front 118

2. Tina needs to be able to back up database on an instance without having to the authority to restore or access the contents of the database. How would you accomplish this business requirement with the least amount of effort?
A. Add Tina to the diskadmin role.
B. Add Tina to the db_owner role.
C. Add Tina to the backupoperator role
D. Add Tina to the sysadmin role.

Back 118

Correct Answer: C
A. Incorrect: members of the diskadmin role can manage disk resources, but they cannot backup a database.
B. Incorrect: members of the db_owner role can back up a database, but they also have the authority to perform any other action within the database.
C. Correct: members of the db_backupoperator role can back up the database, but they are not allowed to restore a database or access any objects within the database.
D. Incorrect: members of the sysadmin role can back up a database, but they can also perform any other action within the instance.

Front 119

1. Wide World importers has just implemented a new order inquiry system. All users with access to the database need to be able to issue a SELECT statement against any table within the database. How can you accomplish this functionality with the least amount of effort?
A. Add the users to the db_datawriter owner role.
B. Grant the users SELECT permission on every table in the database
C. Grant the users SELECT permission on the database
D. Grant the users SELECT permission on every schema in the database.

Back 119

Correct answer C:
A. Incorrect: the db_datawriter role does not grant SELECT permission
B. Incorrect: Although users would have SELECT permission on all tables, granting permissions individuals requires more effort than granting SELECT permission on the database.
C. Correct: When you grant SELECT permission on the database, the user is able to issue a SELECT statement against any table within the database.
D. Incorrect: Although users would SELECT permissions on all tables, granting permissions to each schema requires more effort than granting SELECT permission on the database.

Front 120

2. Which statement prevents users from viewing metadata about objects in a single database, even if the user has access to the objects?
A. DENY VIEW DEFINITION
B. DENY VIEW ANY DEFINITION
C. DENY VIEW SERVER STATE
D. REVOKE VIEW DEFINITION

Back 120

Correct answer A:
A. Correct: DENY VIEW DEFINITION prevents a user from viewing object metadata to which the user would otherwise have access
B. Incorrect: DENY VIEW DEFINITION is an instance level permission that applies to all database within instance.
C. Incorrect: VIEW SERVER STATE allows a connection to view executive statistics for the instance and does not deal with metadata.
D. Incorrect: REVOKE moves a GRANT or DENY that has been issued, but it does not prevent the implicit metadata access for objects a user has permissions on.

Front 121

1. The HR director at Contoso needs to ensure that only authorized users are accessing employee pay records. What do you need to implement to satisfy these auditing needs?
A. Database audit specification
B. A DDL trigger
C. A DML Trigger
D. Server audit specification

Back 121

A. Correct: You can create a database audit specification to log any SELECT, INSERT, UPDATE, DELETE, BCP or BULK INSERT statements executed against the employee pay records table for just the group of users who have access.
B. Incorrect: A DDL trigger fires when a DDL even occurs and cannot log data access.
C. Incorrect: Although a DML trigger can log INSERT, UPDATE and DELETE, you cannot log a SELECT using a DML trigger
D. Incorrect: A server audit specification cannot target an object within a database.

Front 122

1. The DBA's at Fabrikam have implemented log shipping for the Orders database. To ensure that log shipping cannot break, you need to prevent anyone from changing the recovery model of the database to Simple. How can you accomplish this task?
A. A DDL trigger
B. A DML trigger
C. You can't prevent the change of the recovery model
D. Server audit specification.

Back 122

Correct Answer: C
A. Incorrect: Although a DDL trigger can audit an ALTER DATABASE statement, because ALTER DATABASE does not run in the context of a transaction, you cannot prevent the change in the recovery model.
B. Incorrect: A DML trigger fires when data is changed, not when the recovery model changes.
C. Correct: Because ALTER DATABASE can make changes to the file system, which is non-transactional, you cannot prevent ALTER DATABASE from executing. Therefore, the requirement to prevent the change is not possible to implement.
D. Incorrect: A server audit specification log can log the change in recovery model, but it cannot prevent the change in the recovery model from executing.

Front 123

1. The DBAs at Woodgrove Bank manage several sensitive databases containing credit card and customer information. They need to encrypt the entire contents of the database so that an attacker cannot read information off the disk. How can they meet their requirement with the least amount of effort?
A. Create a certificate in the database that is used to encrypt the data.
B. Create a database encryption key and enable the database for encryption.
C. Create a symmetric key in the database that is used to encrypt the data.
Create an asymmetric key in the database that is use to encrypt the data.

Back 123

1. Correct Answer: B
A. Incorrect: You could use a certificate to encrypt every column in the database, but it requires changes to the code to encrypt and decrypt the applications.
B. Correct: TDE uses a certificate in the master database that is employed to encrypt a database encryption key. After you enable it, SQL Server encrypts data on the disk so that it cannot be read by an attacker, without requiring any changes to application.
C. Incorrect: You could use a symmetric key to encrypt every column in the database, but it requires changes to the code to encrypt and decrypt for the applications.
D. Incorrect: You could use an asymmetric key to encrypt every column in the database, but it requires changes to the code to encrypt and decrypt for applications.

Front 124

2. the DBAs at Woodgrove Bank manage several sensitive databases containing credit card and customer information. Due to recent data thefts at other banks that have made headlines, the business wants to ensure that all data within backups is encrypted. how can they accomplish the encryption requirement without needing to change applications?
A. Create a certificate in the database that is used to encrypt the data.
B. Create a symmetric key in the database that is used to encrypt the data.
C. Create a database encryption key and enable the database for encryption.
D. Create an asymmetric key in the database that is used to encrypt the data.

Back 124

1. Correct Answer: B
A. Incorrect: You could use a certificate to encrypt every column in the database, but it requires changes to the code to encrypt and decrypt the applications.
B. Incorrect: You could use a symmetric key to encrypt every column in the database, but it requires changes to the code to encrypt and decrypt for the applications.
C. Correct: TDE uses a certificate in the master database that is employed to encrypt a database encryption key. After you enable it, SQL Server encrypts data on the disk so that it cannot be read by an attacker, without requiring any changes to application.
D. Incorrect: You could use an asymmetric key to encrypt every column in the database, but it requires changes to the code to encrypt and decrypt for applications.

Front 125

1. What does the System:Processor Queue Length Measure?
A. The number of system requests waiting for a processor
B. The number of SQL Server requests waiting to be processed
C. The number of processors actively performing work
D. The amount of time that a processor is in use

Back 125

A. Correct: The System:Processor Queue Length indicates the number of processes at a machine level that are waiting for a processor to be allocated.
B. Incorrect: The System:Processor Queue Length includes any SQL Server requests that are waiting for processor resources to be allocated. The counter also includes request from any other applications and the operating system that are waiting on processor resources.
C. Incorrect: You can derive the number of processors actively performing work by using the Processor:%Processor Time counter with all associated interfaces.
D. Incorrect: The amount of time a given processor is in use is retrieved from an instance of the Processor:% Processor Time counter.

Front 126

1. You are trying to troubleshoot a performance issue at Fabrikam. At about 15 minutes past the hour, on a recurring basis, query performance declines for about 1 minute before application performance returns to normal. What tools can you use to diagnose the cause of the performance problems? (Choose all that apply)
A. System Monitor
B. Database Engine Tuning Advisor
C. Resource Governor
D. Profiler

Back 126

1. Correct Answers: A and D
A. Correct: System Monitor allows you to capture performance counters for the state of the hardware, operating system, and various SQL Server subsystems, which can be correlated to a Profiler trace to diagnose a performance issue.
B. Incorrect: Database Engine Tuning Advisor is used to suggest indexes and partitions that can be optimize the performance of a query.
C. Incorrect: Resource Governor allows you to limit the resources available to one or more connections.
D. Correct: Profiler allows you to capture the query activity on the instance and then correlate the queries with performance counters captured using System Monitor.

Front 127

1. Which types of SQL Server events are logged to the Windows Application Event log? (Choose all that apply)
A. Stack dumps
B. Startup configuration message
C. Job failures
D. Killed process

Back 127

Correct Answers: A and D
A. Correct: A stack dump is a critical error and would be logged to both the SQL Server error log and the Windows Application Event log.
B. Incorrect: Startup configuration messages are logged only to the SQL Server Error log
C. Incorrect: Job failures are logged to the SQL Server Agent log
D. Correct: A killed process is a major even and would be logged to both the SQL Server error log and the Windows Application Event Log

Front 128

1. You are the DBA at Blue Yonder Airlines and the phone rings. The main ticket booking application has just gone off-line and cannot be reconnected to the database. You attempt to connect to the SQL Server and find that it is unreachable. You find that the service has stopped, and upon inspecting the error logs, you find a large number of device activation errors. What is the most likely cause of the problem?
A. Someone deleted the ticketing database files
B. The disk storage system underneath the master or tempdb databases when off-line
C. The disk storage system underneath the ticket booking database went off-line
D. The SQL Server service account was locked out.

Back 128

1. Correct Answer: B
A. Incorrect: when a database is online and has a connection, SQL Server has the files under the database open You cannot delete a file that is open.
B. Correct: If a disk storage system suddenly goes offline, any databases with files in the offline storage log device activation errors. if the device activation errors occur for the master, tempdb or mssqlsystemresource database, the entire instance shuts down.
C. Incorrect: A device activation error for files underneath the thicket booking database takes the booking database offline, but it does not cause the entire instance to shut down.
D. Incorrect: If the service account was locked out, any subsequent attempts to use the account would be refused by Windows. however, any applications currently running under the account remain running. So a service account lockout would prevent the SQL Server from starting, but it does not cause a SQL Server to go offline or throw device activation errors.

Front 129

1. Humongous Insurance has hired you to evaluate its SQL Server infrastructure, make recommendations, and manage projects to improve the production environment. During your evaluation, you have learned that database files are stored across dozens of different drives and mount points Data and log files are mixed with backup files. System databases exist on the same disk drives as user databases. your first project is to move all the system databases to separate drives from the user databases. you will be moving the tempdb database to dedicated storage for each instance because many very poorly written queries move massive quantities of data through tempdb. The on-call DBA is performing the maintenance and has tested the procedures several times in a lab environment. You receive a call that the first instance, following the database moves, does not start. You have verified service account permissions for the folder containing the master database files, that the master database files are in the correct location, and the startup parameters are correct. What is most likely cause of the problem?
A. The master database is corrupted
B. The mssqlsystem resource database is corrupted
C. The service account does not have permissions to the folder containing the tempdb database files
D. You have a bad memory module in the server

Back 129

Correct Answer: C
A. Incorrect: It is possible that the master database is corrupted, but the most likely cause is one of the changes that you made. Because the master database files appear to have been moved properly, the permissions are correct, and the startup parameters are changed correctly, it is not very likely that the master database files have become corrupted simply by moving them to another location.
B. Incorrect. It is possible that the mssqlsystemresource database is corrupted, but the most likely cause is due to one of the changes you made. It is not very likely that the mssqlsystemresource database files have become corrupted by moving them to another location.
C. Correct: One of the changes that you made was to move the tempdb database to a new location. The first step in the move process is to alter the tempdb database and change the location of the data log files. After shutting down the instance, you need to move the files to the new location before starting the instance backup. The SQL Service account also needs Read and Write Permissions on the folder for the tempdb database files. You should check to see if you are getting any "File not found" errors for the tempdb database. it is much more likely that SQL Server either cannot find or cannot access the folder for tempdb database because this is one of the configuration changes that was made.
D. Incorrect: A bad memory module in the server might cause the instance from starting up, although it is not very likely.

Front 130

1. You are setting up security for your new SQL Server 2008 installation. Management is concerned about security. What approach should you take to ensure security settings are optimal?
1. Use the Surface Area Configuration Tool to secure the installation
2. Use the new Security Analysis tool to secure the installation
3. Use SQL Server Configuration Manager to secure the installation
4. Use Windows Service Manager to enable and disable the appropriate services

Back 130

The correct answer is C. On the Start menu, point to All Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager.
Answer A is incorrect because the Surface Area Configuration Tool is no longer used to secure SQL Server 2008.Answer B is incorrect because there is no Security Analysis tool in SQL Server 2008.While Answer D may work, it’s not the best answer.Your answer should be the most “correct” using the new features, even though some of the other answers may technically work.

Front 131

2. You have been tasked with setting up standards for your SQL Server 2008 installation.You need to enforce a table naming convention.What is the best way to accomplish this in your SQL Server 2008 environment?
1. Use Windows Group Policy
2. Create DDL Triggers
3. CreateDMLTriggers
4. Create a Declarative Management Framework policy

Back 131

The correct answer is D.This is the most efficient way to accomplish the task of enforcing a table naming convention.Answer A is incorrect— Windows Group Policy does not provide a mechanism for enforcing table naming conventions. While Answer B could would, it’s not the best solution. Answer C is incorrect as DML triggers provide no means to control
DDL code.

Front 132

3. You have been asked to create a backup of your production database and restore it on a development server.Your production server is using the full recovery model. Full backups are taken Monday and Wednesday.Transaction log backups are taken every hour.Today is Friday.The backup needs to be created as quickly as possible.What’s the fastest way to get the latest database copy while minimizing impact to production?
1. Create a normal backup. Use that to restore to development.
2. Create a Copy Only Backup. Use that to restore to development.
3. Use the Wednesday backup. Restore the transaction log backups since Wednesday.
4. Copy the .mdf and log files and use SP_attach.

Back 132

The ideal situation would be to use B, the Copy Only Backup. Copy Only doesn’t affect the backup chain and will provide the quickest solution while minimizing interruptions. Answer A will affect the backup chain, so it’s somewhat disruptive. Answer C is not the fastest method, and there would be quite a few transaction logs to restore. Answer D would require stopping the SQL service on the production server, rendering it unusable during
the copy.

Front 133

4. You have a SQL Server 7.0 database and would like to move it to a new SQL Server 2008 instance.The database is part of an FDA-validated system and cannot be changed at all? How can this be accomplished?
1. Restore the 7.0 database on your new server and set the compatibility mode to 7.
2. You must upgrade the database to SQL 2005 or greater.
3. Restore the 7.0 database on your new server and set the compatibility mode to 6.5.
4. Copy the .mdf and log files and use SP_attach.

Back 133

The correct answer is B.Answers A and C are incorrect as SQL Server 2008 does not support 7 or 6 compatibility.Answer D is incorrect as the database must be upgraded and cannot just be attached.

Front 134

5. You have an application that is being upgraded from SQL Server 2005 to SQL Server 2008.You notice that some stored procedures are not working correctly. An excerpt is as follows:
SELECT *
FROM Territories, Region
WHERE territories.regionid *= region.regionid
What should you do to resolve the issue?
A. There is no issue.The problem lies elsewhere.
B. The join syntax is incorrect. Replace with left join.
C. The select is incorrect.You need to enumerate the fields. D. The where clause should be = not ∗=.

Back 134

The answer is B.The join syntax is no longer supported.Answer A is incorrect, as the old join syntax (*= and =* ) will no longer work. Answer C is not cor- rect; although Select ∗ will work, it is not good practice. Answer D is incorrect as it will change the left join to an inner join, which would change the overall function of the select statement.

Front 135

6. Your disk is almost full on the transaction log drive for a database server. How can you resolve this issue?
A. UseBACKUPLOGWITHTRUNCATE_ONLY B. Change the mode to simple and shrink the log
C. Reinstall SQL
D. Drop the database and restore from the last backup

Back 135

The correct answer is B.Answer A would be the answer with SQL Server 2005; however, this command is no longer available. Answer C will not solve the problem, and Answer D would result in data loss and could take a while.

Front 136

7. You want to enforce a standard naming convention for stored procedures. What’s the best way to do this in SQL Server 2008?
A. Create a DDL trigger
B. Use the performance data warehouse
C. Create DML triggers
D. Use the SQL Server 2008 Declarative Management Framework

Back 136

The correct answer is D.The DMF will allow you to perform the task at hand.Answer B has nothing to do with enforcing naming conventions.While Answer A could work, it’s not the best approach. Answer D will not allow you to perform the task of enforcing naming conventions.

Front 137

8. You want to enforce a standard naming convention for tables and stored procedures.Your company has two SQL Server 2008 servers and 60 SQL Server 2005 servers.You need to use the same solution on all servers.What’s the best way to do this in SQL Server 2005 and SQL Server 2008?
A. Create a DDL trigger for all servers
B. Use the performance data warehouse
C. Create DML triggers
D. Use the SQL Server 2008 Declarative Management Framework

Back 137

The correct answer is A.This will work on SQL Server 2005 and SQL Server 2008 servers.While Answer D is correct, the DMF will allow you to perform the task at hand only on SQL Server 2008 servers.Answer B has nothing to do with enforcing naming conventions. D will not allow you to perform the task of enforcing naming conventions.

Front 138

9. You have a database table with a varchar(600) field in it. Most of the records in the table have a null value for this field. How can you save space?
A. Move the data into a second table
B. Use sparse columns
C. Install a third-party tool on the machine to compress the data D. Use the SQL Server 2008 Declarative Management Framework

Back 138

The correct answer is A. Sparse columns will take up less space for varchar columns. Sparse columns will take up less space for most columns, but cannot be used for text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinary (max) with the FileStream attribute can be sparse. Answer C is incorrect; this is not the best approach. Answer D is incorrect, as the Declarative Management Framework will not save
you space.

Front 139

10. You have a database table with a FileStream field in it. Most of the records in the table have a null value for this field.What’s the best way to save space?
A. Move the data into a second table
B. Use sparse columns
C. Use the SQL Server 2008 Declarative Management Framework D. None of the above

Back 139

The correct answer is D.Answer A is incorrect;this will not save space.Answer B is incorrect, as sparse columns will take up less space for most columns, but cannot be used for text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinary (max) with the FileStream attribute can be sparse.

Front 140

11. You need to store images in for a Web site using SQL Server 2008. How can you accomplish this?
A. Use a FileStream data type, and the images will be stored on disk B. Use a varchar data type and store the images in that field
C. Use an int data type and store the images in that field
D. Use an nchar data type and store the images in that field

Back 140

The correct answer is A.The FileStream data type is meant for storing files on disk.You could also store the image in a varbinary field, but moving forward the recommended approach is to use the FileStream data type.
Answers B, C, and D all reference data types that cannot be used to store binary data like image files. One approach is to use a varchar field as a pointer to the disk, but this is not recommended after the introduction of the FileStream data type.

Front 141

12. You are responsible for a system that is used for both online transaction processing (OLTP) and reporting.When reports run on the server, the OLTP process slows way down. How can you allow reports to be run on the server and minimize impact to the OLTP processes?
A. Use the Resource Governor
B. Use a DDL trigger
C. Use a DML trigger
D. Use processor affinity masks

Back 141

A is the correct answer.The Resource Governor allows you to assign workloads to different groups, then assign a priority to those groups.
Answers B and C are incorrect.Triggers cannot be used for this purpose.
Answer C is incorrect. Processor affinity masks cannot be used for this purpose.

Front 142

13. You are creating an application to track crime in different locations throughout a large city.What data type could prove useful for storing location data (longitude and latitude)?
A. Varchar B. int
C. Char D. Spatial

Back 142

The correct answer is D. Spatial data types are ideal for this type of data. Answer A could work, but is not ideal. Answers B and C are not ideal for storing this type of information.

Front 143

14. You are running out of space on the drive used to store backups.All of the servers use the same network location.What can you do to save space with your backups while maintaining the same number of backups?
A. Use data compression
B. Use compressed backups C. Use full backups
D. Use a third-party tool

Back 143

B is the correct answer. Compressed backups take up less space. Answer A is incorrect; this will save space in the data files. Answer C is incorrect; this will not save space. Answer D will work, but it’s not the best solution.

Front 144

15. You need to store sensitive data in your SQL Server database.The application has already been written and works fine.What’s the easiest way to do this without having to change your application?
A. Modify the stored procedures to use xp_encryptstring B. Use transparent data encryption
C. Use a third-party tool
D. Use a trigger

Back 144

The correct answer is B.Transparent data encryption enables data to be stored securely by encrypting the database files. Answer A is incorrect, as this is not the best approach. Answer C is incorrect; while there are third-party encryption tools, this is not the best approach. Answer D is incorrect; triggers by themselves are not a correct solution.

Front 145

16. Within your application, you need to log all changes to one table. DDL and DML changes must be logged.What’s the best approach to solve this problem?
A. Use the built-in auditing capability B. Create a DDL trigger
C. Create a DML trigger
D. This cannot be accomplished

Back 145

A is the correct answer. SQL Server 2008 has built-in auditing capability. Answers B and C are incorrect as they are not a complete solution.Answer D is incorrect, as this can be accomplished with Answer A.

Front 146

17. You have a server that supports Hot Add CPU.The current CPU utilization is 95 to 100 percent most of the time.The server is mission-critical and cannot be shut down. SQL Server 2008 Standard Edition is installed.What should you do?
A. Use the Hot Add CPU feature to add another CPU
B. Use the Hot Add CPU feature to add two CPUs
C. Add more memory to the server
D. Schedule an outage and add another CPU to the server

Back 146

The correct answer is D. SQL Server 2008 Standard Edition does not support Hot Add CPU, which makes Answers A and B incorrect. Answer C is incorrect—the issue is CPU, so adding more memory will in all likelihood not solve the problem.

Front 147

18. You are contemplating using data compression on a table.You would like to know how much space this will save. How can you determine the savings?
1. View the table properties
2. Enable compression, then check the table size
3. Use sp_estimate_data_compression_savings
4. Use sp_check_compression

Back 147

The correct answer is C. sp_estimate_data_compression_savings will help you determine if the table is worth compressing.
This procedure accepts five parameters. 1. @schema_name
2. @object_name
3. @index_id
4. @partition_number
5. @data_compression
The two critical parameters are @object_name and @data_compression.
Answer A is incorrect; compression information is not shown in the table properties.Answer B will not solve the problem before applying compression. Answer D is incorrect, as there is no stored procedure by that name.

Front 148

19. You have a server that supports Hot Add Memory. Performance is sluggish, and you believe adding more memory will help.The server is mission-critical and cannot be shut down. SQL Server 2008 Standard Edition is installed. What should you do?
A. Use the Hot Add CPU feature to add another CPU B. Use the Hot Add CPU feature to add two CPUs C. Add more memory to the server.
D. Schedule an outage and add memory to the server.

Back 148

The correct answer is D. SQL Server 2008 Standard Edition does not support Hot Add Memory, which makes Answer C incorrect. Answers A and B are incorrect—the issue is memory, so adding more CPU will in all likelihood not solve the problem.

Front 149

20. You have a SQL Server 2008 installation, and you want to create a high- availability solution.What are the ideal approach(es) to solve this problem?
A. Backup and restore B. Replication
C. Mirroring
D. Clustering

Back 149

The correct answers are C and D. C and D are automated high-availability solutions.Answer A is not necessarily a good solution as it is not automated. Answer B will create a copy of a database, but will require intervention in order for the replicated database to become the active database.