What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance that contains a database. During monitoring, you notice that the following Transact-SQL query is running slow:
SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.VideoTitle WITH
(INDEX(0)) WHERE ReleaseDate BETWEEN ‘20050401’ AND ‘20050510’
With regards to the output, a clustered index exists on the VideoTitle column. Furthermore there is
a nonclustered index on the ReleaseDate column that has the UpcNum and RetailPrice columns.
You also notice that the avg_fragmentation_in_percent is 30 percent, when you explore the
sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table.
You have recievewd instructions to find the method to resolve the problem.
What should you do?
What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2005 database that contains a table named CK_Prod. Product, that has over 50 columns. The
normal Transact-SQL statement queries are as follows:
–QUERY 1
SELECT [Name], [ProductLine], [ListPrice], [Class], [Style] FROM [CK_Prod]. [Product]WHERE
[ProductLine] = ‘T’
–QUERY 2
SELECT * FROM [CK_Prod]. [Product] ORDER BY [Name]
You have received instructions to minimize the space for the indexes, however, you should keep
the most common searched to CK_Prod. Product.
What should you do?
What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance to manage and maintain its databases. The SQL Server 2008 instance contains a
database named CK_DB that has a table named CK_Tbl. You then add a Geography data type
column named CK_Colmn to CK_Tbl. The Domain.com management also wants CK_Colmn to
make allowance for the development of a spatial index.
What should you do?
What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance. The SQL Server 2008 instance contains a database named CK_DB that has a
table named CK_Tbl that is partitioned on the ProID column.
The partitioning characteristics are as follows:
Partition 1 has integer values between 5 and smaller than 30,000.
Partition 2 has integer values greater than 30,000.
Due to the growth in business operations the Domain.com management foresees the need to
have a third partition in the table. You receive instructions to create a third partition on the table.
This partition must be able to include the integer values greater than 60,000.
What should you do?
which is remote. What should you do?
You work as a database administrator at Domain.com. You have completed the installed
Microsoft SQL Server Management Studio (SSMS) on one of Domain.com’s workstations but not
the Microsoft Business Intelligence Development Studio (BIDS). However, you need to design a
package with the following characteristics:
* The package should be transactional.
* The package can use 10 tables.
* The package should be stored in the msdb database of a server which is remote.
What should you do?
What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance. You are using the SQL Server Import and Export Wizard to export information from
a SQL Server database to a Microsoft Excel file format. However, you need to save the package
definition to a file using the appropriate command line.
What should you do?
What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance that contains a database named CK_Prod. You have received instructions to
ensure that the entire SQL Server samples are set to comply with the following requirements.
* Security configurations should be accommodated
* Data fragmentation should be avoided
* Naming conventions should be accommodated
* Force index creation should be accommodated
What should you do?
What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 instance to administrate and maintain their database. The SQL Server 2008 instance
contains a database named CK_DB that contains a table named CK_Tbl.
You have to put the output of the entire information from CK_Tbl to a file. You have received
instructions from management to ensure that your solution will provide the desired output in a
Microsoft Office Open XML document format whilst making sure that the data export process is
protected for reuse.
What should you do?
What should you do?
You work as a database administrator at Domain.com. You have just installed Microsoft SQL
Server Management Studio (SSMS) on one of Domain.com’s workstations but not the Microsoft
Business Intelligence Development Studio (BIDS). However, you need to design a package with
the following characteristics:
* It must be transactional.
* It must be able to accommodate the use of 10 tables.
* It must be stored in the msdb database of server that is offsite.
What should you do?
What should you do?
You work as a database administrator at Domain.com. Domain.com is using a SQL Server
2008 sample that contains an On-Line Analytical Processing (OLAP) database and a dimension table named CK_TST.
CK_TST contains data that is not in use, but you refresh the table on an hourly basis.
Consequently this results in a large chunk of disk space being taken up by superfluous data.
You need to keep the disk space that will be used to reserve the CK_TST table.
What should you do?