You design a maintenance plan for a SQL Server 2008 instance that contains a database
named SalesDB.
The SalesDB database includes spatial indexes to support queries on spatial data.
You need to perform physical consistency checks on SalesDB. You also need to ensure that
the performance effect on the SalesDB database is minimized.
Which Transact-SQL statement should you execute?

You administer a SQL Server 2008 instance. You need to identify the network protocol used
by the current connection. What should you do?

You administer a Microsoft SQL Server 2008 R2 database that hosts an order-processing
application. You need to ensure that the database allows full-text searches on the
Customers table. You also need to ensure that the full-text index is ready for use by the
users.
You execute a Transact-SQL statement to create the full-text index on the Customers table
by using the CHANGE_TRACKING OFF and NO POPULATION clauses in the CREATE
FULLTEXT INDEX statement. You execute an ALTER FULLTEXT INDEX. Which command
or commands should you use next?

You install a 64-bit version of a SQL Server 2008 instance by using the default setup and
configuration settings. You receive the following error message:
“Your SQL Server installation completed with \a: ures.1
You close the error window before noting the location where additional information about the
error is recorded.

You need to locate the additional information.
Where should you search?

You administer a SQL Server 2008 instance that contains a database named
AdventureWorks. You plan to use data compression to conserve disk space. You compress
a table named
VeryLarge that has a clustered index named Index1 and a non-clustered index named
Index2. Both indexes are partitioned. You execute the following Transact-SQL statement.
ALTER INDEX Index2 ON VeryLarge REBUILD WITH (DATA_COMPRESSION = ROW);
You need to ensure that all the indexes of the VeryLarge table are compressed by using
row-level compression. You need to accomplish this goal by using minimum resources.
Which Transact-SQL statement should you execute?

You administer a SQL Server 2008 instance that contains a database named Sales. The
Sales database contains a table named Customers that has a column named
CompanyName.
The current database collation is SQL_Latin1_General_Cp1_CI_AS. You need to ensure
that the indexes which include the CompanyName column are case-sensitive.
You also need to ensure that other case-insensitive queries are unaffected.
What should you do?

You maintain a SQL Server 2008 instance that contains a database named CustomerDB.
The CustomerDB database stores customer data for the company. The customers use a
Web application to access their profile data.
You need to protect the customer data such that data files, log files, and subsequent
backups are as secure as possible even if the backup media is lost. You want to achieve this
goal without affecting the Web application.
What should you do?

You administer a Microsoft SQL Server 2008 R2 database. You configure the disk drives
according to the following table:

You discover that the Windows application log is being filled with entries from a SQL Server
Audit process named DatabaseAudit. The volume of these events is causing older events to
be removed from the log.
You need to ensure that the following requirements are met:
-SQL Server Audit information is stored in the D:\AuditLogs folder. -No data is currently lost
in the Windows application log.
What should you do?

You administer a SQL Server 2008 instance named SQL1. SQL1 contains a database
named DB1.
You create a new user named User1 in the DB1 database. No additional permissions have
been assigned to User1 or the public role. User1 will interact with database objects stored in
a new schema named Reporting. The other objects in the DB1 database are owned by the
dbo and are contained in a schema named Operations.
You need to grant the minimum necessary permission to User1 to create tables and stored
procedures in the Reporting schema.
You also need to ensure that User1 cannot alter any of the objects in the Operations
schema.

Which Transact-SQL statements should you execute?

You administer a Microsoft SQL Server 2008 R2 database that contains an OrderItems
table. The table has the following definition: Currently, the table is partitioned by Quarter.

You need to make the table unpartitioned.
What should you do?