(Ch 1, Lesson 1, Quick Check)

1.  What edition of Windows Server 2008 is not supported for SQL Server 2008 installations?

Windows Server 2008 Server Core is not supported for SQL server 2008 installations.

(Ch 1, Lesson 1, Quick Check)

2. Which operating systems are supported for all editions of SQL Server?

Windows Server 2003 Standard SP2 or higher, windows Server 2008 Standard RC0 or higher

(Ch 1, Lesson 1)

SQL Server 2008 is supported on both ___ bit and ___ bit operating systems

32, 64

(Ch 1, Lesson 1, Lesson Review)

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 2000 Server Enterprise SP4 or higher

b. Windows Server 2003 Enterprise

c. Windows Server 2003 Enterprise SP2

d. Windows Server 2008 Enterprise

c. Windows Server 2003 Enterprise SP2

d. Windows Server 2008 Enterprise

(Ch 1, Lesson 1, Lesson Review)

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

b. Windows Server 2008 Server Core

(Ch 1, Lesson 2, Quick Check)

1. 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?

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

(Ch 1, Lesson 2, Quick Check)

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

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 4GB

(Ch 1, Lesson 2, Lesson Review)

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

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 the storage engine for web based applications

(Ch 1, Lesson 2, Quick Check)

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

d. Correct: Only SQL Server 2008 Enterprise supports all the advanced analytics needed, such as OLAP and Data Mining.

(Ch 1, Lesson 3, Quick Check)

1. Which edition of SQL Server supports installing more than one instance of SQL Server on a machine?

Only SQL Server Enterprisesupports multiple instances on the same machine

(Ch 1, Lesson 3, Quick Check)

2. What are the authentication modes that SQL Server can be configured with?

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

(Ch 1, Lesson 3, Lesson Review)

1. 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 administration 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

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 usting the  sp_configure system stored procedure

(Ch 1, Lesson 4, Quick Check)

1. What are the two basic components of Database Mail?

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

(Ch 1, Lesson 4, Quick Check)

2. What are the two types of mail profiles that can be created

Mail profiles can be configured as either public or private

(Ch 1, Lesson 4, Lesson Review

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 an 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

a. Incorrect: Notification Services is a feature that was abailable with SQL Servr 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 activitiy on the server, it cannot be used to send messages

(Ch 1, Lesson 4, Lesson Review)

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 setting sneed to be configures to meet those 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

a. Incorrect: A public profile can be accessed by anyone with the authority  to send mail, which violates the security  requirements

b. Incorrect: Although configurating ta 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 explicityly

(Ch 1, Case Scenario)

Wide World Importers  is implementing a new set of applications to manage several lines of business. Within the corporate data center, they need the ability to store large volumes of data tha can be accessed from anywhere in the world.

Several business managers need to access large volumes of historical data to spot trends and optimize their staffing and inventory levels.

A large sales force makes customer calls all over the worl dand needs access to data on the customers that a sales rep is servicing, along with potential prospects. The data for the sales force needs to be available even when the salespeople are not connected to the Internet or the corporate network. Periodically sales reps will connect to the corporate network and sychronize their data with the corporate databases.

A variety of Windows applications have been created Visual Studio.NET and all data access is performed using stored procedures. The same set of applications are deployed for users connecting directly to the corporate database serer as well as for sales reps connecting to their own local database servers

1. What edition of SQL Server 2008 should be installed on the laptops of the sales force to minimize the cost?

Because sales reps need to be disconnected and synchronize data, you coiuld install either the Express or the Compact edition of SQL Server 2008. However, applications installed on the sales reps' laptops require stored procedure support. Express is the only edition that will minimize cost while also supporting replication and stored procedures

(Ch 1, Case Scenario)

Wide World Importers  is implementing a new set of applications to manage several lines of business. Within the corporate data center, they need the ability to store large volumes of data tha can be accessed from anywhere in the world.

Several business managers need to access large volumes of historical data to spot trends and optimize their staffing and inventory levels.

A large sales force makes customer calls all over the worl dand needs access to data on the customers that a sales rep is servicing, along with potential prospects. The data for the sales force needs to be available even when the salespeople are not connected to the Internet or the corporate network. Periodically sales reps will connect to the corporate network and sychronize their data with the corporate databases.

A variety of Windows applications have been created Visual Studio.NET and all data access is performed using stored procedures. The same set of applications are deployed for users connecting directly to the corporate database serer as well as for sales reps connecting to their own local database servers

2. What edition of SQL Server 2008 should be installed within the corporate data center

SQL Server 2008 Enterprise needs to be installed on the corporate database server. Enterprise can scale to handle any activity volume while also providing advanced analytical services

(Ch 1, Case Scenario)

Wide World Importers  is implementing a new set of applications to manage several lines of business. Within the corporate data center, they need the ability to store large volumes of data tha can be accessed from anywhere in the world.

Several business managers need to access large volumes of historical data to spot trends and optimize their staffing and inventory levels.

A large sales force makes customer calls all over the worl dand needs access to data on the customers that a sales rep is servicing, along with potential prospects. The data for the sales force needs to be available even when the salespeople are not connected to the Internet or the corporate network. Periodically sales reps will connect to the corporate network and sychronize their data with the corporate databases.

A variety of Windows applications have been created Visual Studio.NET and all data access is performed using stored procedures. The same set of applications are deployed for users connecting directly to the corporate database serer as well as for sales reps connecting to their own local database servers

3. What SQL Server services need to be installed to meet the needs of the business managers

You need to install at least SSAS and SSRS. SSAS handles the analytical applications required for trend analysis, and SSRS provides the reporting infrastructure required for all operational reports

(Ch 1, Case Scenario)

Wide World Importers  is implementing a new set of applications to manage several lines of business. Within the corporate data center, they need the ability to store large volumes of data tha can be accessed from anywhere in the world.

Several business managers need to access large volumes of historical data to spot trends and optimize their staffing and inventory levels.

A large sales force makes customer calls all over the worl dand needs access to data on the customers that a sales rep is servicing, along with potential prospects. The data for the sales force needs to be available even when the salespeople are not connected to the Internet or the corporate network. Periodically sales reps will connect to the corporate network and sychronize their data with the corporate databases.

A variety of Windows applications have been created Visual Studio.NET and all data access is performed using stored procedures. The same set of applications are deployed for users connecting directly to the corporate database serer as well as for sales reps connecting to their own local database servers

4. What version of Windows need to be installed on the corporate database server?

You need to install either Windows 2003 Server Enterprise SP2 and later or Windows Server 2008 Enterprise and later to support SQL Server 2008 Enterprise

(Ch 2 , Lesson 1, Quick Check)

1. What are the types of files that you create for databases and what are the commonly used file extensions?

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

(Ch 2 , Lesson 1, Quick Check)

2. What is the purpose of the transaction log?

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

(Ch 2 , Lesson 1, Lesson Review)

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 view to database users.

c. set the database to READ_ONLY

d. grand select permission on the database to all users and revoke insert, update, and delete permissions from all users on the database

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 are still able to change data in the database

d. Incorrect: Members of the db_owner role are still able to change data in the database

(Ch 2 , Lesson 2, Quick Check)

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

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

(Ch 2 , Lesson 2, Quick Check)

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

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 reocovery model

(Ch 2 , Lesson 2, Quick Check)

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 hten loaded into the database using the Bulk Copy Program (BCP) utility. You need to ensure tha tyou can recover the database to any point in time while also maimizing the performace 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

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

(Ch 2 , Lesson 3, Quick Check)

1. Which option should be enabled for all production databases?

You should set the PAGE_VERIFY CHECKSUM option for all production databases

(Ch 2 , Lesson 3, Quick Check)

2. What checks does DBCC CHECKDB perform?

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.

(Ch 2 , Lesson 3, Lesson Review)

Which commands are executed when you run the DBCC CHECKDB command? (check all that apply)

a. DBCC CHECKTABLE

b. DBCC CHECKIDENT

c. DBCC CHECKCATALOG

d. DBCC FREEPROCCACHE

a. Correct: A DBCC CHECKDB command executes DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKCATALOT

b. Incorrect:  DBCC CHECKIDENT is used to check, fix, or reseed an identity value

c. Corrrect: A DBCC CHECKDB command executes DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKCATALOG

d. Incorrect: DBCC FREEPROCCACHE clears the contents of the query cache.

(Ch 3 , Lesson 1, Quick Check)

1. How do you design a database?

1. The ruling principle for designing a database is "Put things 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 difines 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.

(Ch 3 , Lesson 1, Quick Check)

2.  What are three new options that you can configure for columns, rows, or pages within a table?

You can designate columns as SPARSE to optimize the storeage of NULLs. You can apply the FILESTREAM property to a VARBINARY(MAX) column to enable the storage of documents in a direactory 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 stoage space required for the table, inex, or indexed view.

(Ch 3 , Lesson 1, Lesson Review)

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

a. Incorrect: You can apply page compression to a table that have a VARCHAR(MAX) data type; however, the data with the VARCHAR(MAX) column i snot compressed

b. Correct: You cannot use either row or page compression with a table that has a sparse column

c. Corrrect: 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. Incorrect: YOu can apply row or page compression to a table that has a FILESTREAM colunn, but the data in the FILESTREAM column will not be compressed

(Ch 3 , Lesson 2, Quick Check)

What is the difference between a primary key aned a unique constraint?

A primary key does not allow NULLs

(Ch 3 , Lesson 2, Quick Check)

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

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 columsn, all the columns must exist in the child table for the foreign key to be created

(Ch 3 , Lesson 2, Lesson Review)

Columns with which properties cannot be sparse columns? (Choose two. Each forms a separate answer)

a. FILESTREAM

b. NULL

c. NOT FOR REPLICATION

d. COLLATE

a. Correct: ROWGUIDCOL, IDENTITY,  and FILESTREAM are not allowed to be used with SPARSE columsn. 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 addistion, a column must allow NULLs to be designated as a sparse column

b. Incorrect: Sparse columnns must allow NULLs

c. Correct: ROWGUIDCOL, IDENTITY, and FILESTREAM are not allwed to be used with SPARSE columns. Becasue 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 addistion, a column must allow NULLS in order to be deisnated as a sparse column.

d. Incorrect: A sparse column can have a collation sequence specified.

(Ch 4, Lesson 1, Quick Check)

1. What type of structure does SQL Server use to construct an index?

SQL Server uses a B-tree structure for indexes

(Ch 4, Lesson 1, Quick Check)

2. What are the three types of pages within an index?

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.

(Ch 4, Lesson 1, Quick Check)

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 ShortDescrip-tion. 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)

a. Incorrect: Because the Optimizer considers only the first column in an index when determining the query plan, unless the query returned only the ProductID, ProductName, and SKU, it is very unlikely that SQL Server would use this index over the primary key on the table

b. Incorrect: Although this index allows SQL Server to locate queries that searched on ProductName, SQL Server must access the table to return the remainder of the data for the result set

c. Correct: This index allows SQL Server to search on ProductNames and return the entire result set for the query from the index instead of the table. In addition, the index is kept small by having only the ProductName column define the B-tree and upper levels of the index

d. Incorrect: Although SQL Server could use this index to satisfy queries entirely from the index, every level of the index would be build on a 250 kilobye (KB) key, causing a much larger set of pages to be read.

(Ch 4, Lesson 2, Quick Check)

1. What is the difference between a clustered and a nonclustered index?

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

(Ch 4, Lesson 2, Quick Check)

2. How does the FILLFACTOR option affect the way an index is built?

The FILLFACTOR option reserves space on the leaf levels of the index

(Ch 4, Lesson 2, Lesson Review)

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 characcteristics of the products offered. Each prodcut 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 the 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

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: 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 Wood Speices 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 return data from the ProductAttributes table entirely from the index

(Ch 4, Lesson 3, Quick Check)

1. What is the difference between the REBUILD and REORGANIZE options of ALTER INDEX?

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

(Ch 4, Lesson 3, Quick Check)

2. What happens when an index is disabled?

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

(Ch 4, Lesson 3, Lesson Review)

1. You are in charge of building the process that loads approximately 150 GB of data into the enterprise data ware house every month. Every table in your data warehoue 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 effor?

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.

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 fo 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 indexxes 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 miantain 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 miantenance during a load operation.

(Chapter 1 Quiz)

1. Database mail is sent by a task managed by the ___
1. analysis Services
2. SQL Server Agent
3. Integration Services
4. SQL Server Browser Service

SQL Server Agent

(Chapter 1 Quiz)

1. Messages sent by Database Mail are logged into the ___
1. tempdb database
2. msdb database
3. recovery file
4. transaction log

msdb database

(Chapter 1 Quiz)

1. Which of the following statements about SQL Server instances is FALSE?
1. Each instance has its own full text search functionality
2. Each instance has its own occurrences of Reporting Services (if deployed)
3. Each instance has its own set of system and user database
4. Each instance shares the SQL Server Agent service

Each instance shares the SQL Server Agent service

(Chapter 1 Quiz)

1. The ___ determines how character data is treated for sorting and comparison by SQL Server 2008
1. collation sequence
2. recovery mode
3. data dictionary
4. thesaurus

collation sequence

(Chapter 1 Quiz)

1. Which of the following is used to start and stop SQL Server services?
1. SQL Server Analysis Services
2. SQL Server Configuration Manager
3. SQL Server Management Studio
4. SQL Server Integration Services

SQL Server Configuration Manager

(Chapter 1 Quiz)

1. The marketing manager of your company wants to be able to identify rules and patterns in the company data, to determine why things happen and to predict what will happen in the future. You realize that he wants to do data mining in the company’s SQL Server 2008 database. Which tool should you install to provide this capability?
1. Reporting Services
2. Database Engine
3. Integration Services
4. Analysis Services

Analysis Services

(Chapter 1 Quiz)

1. The OLAP engine is part of the ___
1. Analysis Services
2. Integration Services
3. Reporting Services
4. Profiler

Analysis Services

(Chapter 1 Quiz)

1. Which of the following statements is FALSE?
1. Both Standard and Enterprise editions of SQL Server support the installation of multiple instances on a single machine
2. FILESTREAM is not supported on Windows Server Core
3. Windows 2003 SP2 and higher supports all editions of SQL Server 2008
4. PowerPivot for SharePoint is available in SQL Server 2008R2 Enterprise edition

Both Standard and Enterprise editions of SQL Server support the installation of multiple instances on a single machine (Only Enterprise supports multiple instances)

(Chapter 1 Quiz)

1. Which SQL Server 2008 capability continuously streams transactions from the transaction log on a source server to a destination server, and allows applications to reconnect to the destination server if the source server fails?
1. Data mining
2. Online analytic processing
3. Log shipping
4. Database Mirroring
   
   

1. Database Mirroring

(Chapter 1 Quiz)

1. BCP stands for ___
1. Bulk compare
2. Block compare
3. Bulk copy
4. Block copy

1. Bulk copy

(Chapter 2 Quiz)

1. The ___ option can be enabled to detect damaged pages
1. AUTO_CLEANUP
2. CHECKPAGE
3. CHECKTABLE
4. PAGE_VERIFY CHECKSUM

PAGE_VERIFY CHECKSUM

(Chapter 2 Quiz)

1. It is customary to use the  ___ file extension for the data file that will contain all of the system objects necessary to a SQL Server database
1. .mdf
2. .ldf
3. .ndf
4. .mdb

.mdf

(Chapter 2 Quiz)

1. You are the DBA for a large database with many online users. A serious problem has been detected, and you must perform some administrative actions that require you to stop the online users. You need to disconnect the users, but wish to give them a one minute notice that they will be disconnected. Which options should you use?
1. RESTRICTED_USER ROLLBACK AFTER 1
2. SINGLE_USER ROLLBACK AFTER 1
3. RESTRICTED_USER ROLLBACK AFTER 60
4. SINGLE_USER ROLLBACK AFTER 60

RESTRICTED_USER ROLLBACK AFTER 60

(Chapter 2 Quiz)

The ___ feature allows you to associate unstructured files stored in a folder with a database

FILESTREAM

(Chapter 2 Quiz)

1. SQL Server uses ___ for worktables when performing sorting or grouping operations
1. the tempdb database
2. temporary disk files
3. the distribution database
4. the msdb database

the tempdb database

(Chapter 2 Quiz)

1. You are adding new data to a SQL Server 2008 database. Which of the following statements shows the correct order in which the new data is written?
1. Data files, buffer, transaction log
2. buffer, data files, transaction log
3. transaction log, buffer, data files
4. buffer, transaction log, data files

buffer, transaction log, data files

(Chapter 2 Quiz)

1. You have a reference database named OrderHistory. It is only used to store completed orders, and does not allow the users to modify any data. It is updated nightly by a batch program that reads orders from a file and adds them to the database. The batch program runs for several hours every night, and you must ensure that it will complete before 8 am on  the following morning. Which recovery model should be used for this database?
1. bulk-logged
2. full
3. No recovery
4. simple

(Chapter 2 Quiz)

1. A database that supports hundreds of online shoppers should use the ___ recovery model
1. bulk-logged
2. online
3. full
4. simple

(Chapter 2 Quiz)

1. An extent consists of ___ blocks of data
1. 512
2. 8
3. 16
4. 128

128

(Chapter 2 Quiz)

1. SQL Server 2008 writes ___ to the transaction log and ___ reads and writes to the data files.
1. sequentially, randomly
2. sequentially, sequentially
3. randomly, sequentially
4. randomly, randomly

1. sequentially, randomly

(Chapter 3 Quiz)

You wish to allow SQL Server to provide a value for a column automatically when a new row is added to the table. How should you accomplish this?

a. Set the IDENTITY property on this column

b. Set the AUTOGROWTH  property on this column

c. Set a primary key constraint on this column

d. Set a UNIQUE constraint on this column

a. Set the IDENTITY property on this column

(Chapter 3 Quiz)

(True or False)

The NUMERIC & DECIMAL data types are exactly equivalent in SQL Server 2008

True

(Chapter 3 Quiz)

(True or False)

A column defined with the UNIQUE constraint does not allow a NULL value

False

(Chapter 3 Quiz)

You are creating a table, and wish to ensure that a particular column will only accept values within a specific range. What should you do?

a. Create a FOREIGN KEY constraint for the column

b. Create a UNIQUE constraint for the column

c. Create a CHECK constraint for the column

d. Create a PRIMARY KEY constraint for the column

c. Create a CHECK constraint for the column

(Chapter 3 Quiz)

You have a table containing several columns that will frequently not have data in them. You want to optimize storage space. What should you do?

a. Set the SPARSE property on those columns

b. Set the COLLATE property on those columns

c. Set the FILESTREAM property on those columns

d. Set the CHECK property on those columns

a. Set the SPARSE property on those columns

(Chapter 3 Quiz)

XML data would be classified as ___ data

a. semistructured

b. BLOB

c. unstructured

d. structured

a. semistructured

(Chapter 3 Quiz)

Multiple teams will be working on a database application. The design team wants to maintain the integrity of the database tables that will be released for use with the application. The developers, however, need to create other database tables to use for extracting data and for testing purposes. What is the best way to achieve this?

a. Create separate databases, one for poduction and one for the developers. Set permissions to allow the developers to  use only their database

b. Create all of he tables in a single database. Partition the database so that the production tables are in one partition, and the developer tables are in another partition

c. Create one schema for all of the tables, and give control of the schema to the design team. Instruct the developers to request additional tables from teh design team.

d. Create two schemas. The first schema wil containt the tables or the production database, and will be controlled by the design team. The second schema will allow developers to create the tables they need for testing.

d. Create two schemas. The first schema wil containt the tables or the production database, and will be controlled by the design team. The second schema will allow developers to create the tables they need for testing.

(Chapter 3 Quiz)

(True or False)

If you do not specify it explicitly when creating a column in a table, the column will always be set to allow NULL values

False

(Chapter 3 Quiz)

Given the following SQL commands:

CREATE Table dbo.ClientJob
(ClientID       INT,
ClientName    VARCHAR(50)  NOT NULL,
QuotedPrice   Money, 
JobStartDate  DATE              NOT NULL
JobEndDate    DATE              NOT NULL
ContactPerson VARCHAR(50)
)
GO
ALTER TABLE dbo.ClientJob
ADD CONSTRAINT inx_ClientJob_ClientID PRIMARY KEY CLUSTERED (ClientID)
GO

What is wrong with these commands?
a. You cannot add a primary key to a table after it has been created

b. MONEY is not a valid SQL server data type

c. You cannot specify a primary key on a column that allows NULL values

d. A column of type MONEY cannot allow NULL values

c. You cannot specify a primary key on a column that allows NULL values

(Chapter 3 Quiz)

(True or False)

When you declare a column containing BLOB data to be a  FILESTREAM column, the BLOB data is not stored in the column

True

(Chapter 4 Quiz)

(True or False)

A table with a clustered index is called a heap

False

(Chapter 4 Quiz)

(True or False)

ALTER INDEX...REBUILD drops and recreates an index

True

(Chapter 4 Quiz)

You are preparing to create a nonclustered index on a column of type int. There are 6100 rows of data in the table on which the index will be built. What is the minimum number of pages that will be required for the index?

4

(Chapter 4 Quiz)

A table has a nonclustered index with two levels in its b-tree. It will require a minimum number of ___ I/O operations to reatd a single row of data in the table, using this nonclustered index.

(Chapter 4 Quiz)

You are creating a new, nonclustered index on a very large table that is heavily used by OLTP users. As soon as you start the index creation, your support desk begins to receive many calls from users complaining that they can query records but cannot update records in this table. What is the probable cause?

a. You specified ONLINE=OFF

b. You specified a low value for FILLFACTOR which is causing a lot of page splits

c. You specified a hight value for FILLFACTOR which is causing a lot of page splits

. You specified ONLINE=ON

a. You specified ONLINE=OFF

(Chapter 4 Quiz)

You are monitoring the performance of your database, and you notice that one of the indexes on a table is highly fragmented. You use the ALTER INDEX...REORGANIZE command. When it completes, you see that hte fragmentation level of this index is somewhat better, but the index is still quite fragmented. Why?

a. You did not set the FILLFACTOR value when you ran the reorganize command

b. a single pass of reorganization is not sufficient to completely defragment the index. You must run it several times on a highly fragmented index

c. the database table was blocked by online transactions while you were running the reorganize command

d. reorganization of an index only attempts to defragment the leaf level of an index

d. reorganization of an index only attempts to defragment the leaf level of an index

(Chapter 4 Quiz)

The ___ of an index increases as the number of unique values within the indexed column increases.

a. fragmentation

b. fill factor

c. uniqueness

d. selectivity

d. selectivity

(Chapter 4 Quiz)

A table has a clustered index with 4 levels in its b-tree. It will take a minimum number of __ I/O operations to read a row of data from this table, using this clustered index

(Chapter 4 Quiz)

(True or False)

By default, a primary key is both clustered and unique

True

(Chapter 4 Quiz)

You have a table with several indexes, including one clustered index. You need to run some batch update jobs that add new records to the table. To speed up the jobs, you disable one of the nonclustered indexes. You re-enable the index when the batch jobs complete. Two days later, users report that some of their queries do not contain recently added data, while other queries seem fine. Why is this happening?

a. The users have not constructed their queries correctly, so the new data is not included in the results

b. Disabling the nonclustered index made the table inaccessible, and the batch jobs failed to add the new data to the table

c. the disabled nonclustered index was not updated when the batch jobs added new data to the table

d. some of the data in the table is corrupted

c. the disabled nonclustered index was not updated when the batch jobs added new data to the table