Which action should you perform to achieve this?
You are a database administrator for a toy manufacturing company named NetFx. The company stores all itsproduct-related data in a database named Netfx_data that resides on a SQL Server 2008 server named Server1 . A table named Invoice_details exists in the database and contains the details of all the new invoices generatedby the company in the last month.
The table was created by using the following CREATE TABLE statement:
CREATE TABLE Invoice_details (
Invoice_id int NOT NULL,
Customer_id int NOT NULL,
Invoice_date datetime DEFAULT GETDATE () NULL,
Amount_total int NULL,
Serial_num int IDENTITY (1,1) NOT NULL)
A clustered index exists on the Serial_num column, and a composite nonclustered index exists on the Invoice_id and the Customer_id columns.
An application will be used to execute queries to retrieve all the invoices for a particular customer. The followingquery is used by the application to retrieve data:
SELECT Customer_id, Invoice_id, Invoice_date
FROM Invoice_details
WHERE Customer_id = 1234
ORDER BY Invoice_date DESC;
You must ensure optimal performance of the query. Which action should you perform to achieve this?
Which statements are true?
You manage an instance of SQL Server 2008. The server contains several databases that have differentcollations.
An assistant administrator named Paul wants to create a table in a database named Products , but heis unfamiliar with collation precedence rules.
You want to inform Paul about collation precedence rules that he should consider when specifying differentcollations for columns in the table.
Which statements are true? (Choose all that apply.)
Which permissions will you require to perform this task?
You manage an instance of SQL Server 2008 named SQL1. SQL1 contains a database named HumanResource. The HumanResource database contains a table named Employees.
You want to create aspatial index on a column named EmpAddress in the Employees table. Which permissions will you require to perform this task? (Choose all that apply.)
Which statement or tool will best suit your purpose in this scenario?
You are a database administrator for a financial firm. You receive data files from other financial firms.
Records inthese input files are delimited with a new line character, and column values are delimited with tab character.
You must validate the data in the input files, and insert the data into multiple tables.
To achieve this, you decide tomove the data to a local temporary table, and then read the data in the temporary table to insert the data into your database tables.
Which statement or tool will best suit your purpose in this scenario?
Which fixed server role or roles will allow you to perform this task?
You are responsible for managing a SQL Server 2008 computer. The server contains several databases thatcontain mission-critical information.
You configure daily backups for all databases. To ensure that the backupprocess takes less time, you want to configure backup compression on the server.
Which fixed server role or roles will allow you to perform this task? (Choose all that apply.)
Which statement should you use to create the Prod_details table as a partitioned table?
You are the database administrator for your company and manage all your company’s SQL Server 2008 databases.
A production database named Prod1 contains all the product and sales data of the company.
You must create a table named Prod_details to hold detailed data about products manufactured by yourcompany.
Company employees will frequently access data in this table in such a way that you want to create thetable as a partitioned table.
You use the following statements to create the partition function My_pf and the partition scheme My_ps1 :
CREATE PARTITION FUNCTION My_pf (int)
AS RANGE RIGHT FOR VALUES (1, 1000, 2000);
CREATE PARTITION SCHEME My_ps1
AS PARTITION My_pf
TO (FG1, FG2, FG3, FG4);
Which statement should you use to create the Prod_details table as a partitioned table?
Which dynamic management function should you use?
You are the database administrator of your company. The network contains an instance of SQL Server 2008 Enterprise Edition named SQL1.
SQL1 contains a database named EmpDetails.
You create a partition for acolumn named EmpID in the Employees table in the EmpDetails database.
You configure page compression on the partition.
You want to monitor the page compression statistics for the partition.
Which dynamic management function should you use?
What should you do to achieve this?
You manage a SQL Server 2008 database for a banking firm.
You currently perform a full database backup of thedatabase by using the Full Recovery model with transaction log backups.
This ensures that there is no data lossin case of a database failure. You regularly receive large files from other trading and investment firms.
The data in the files is imported intotemporary tables.
After the import, you insert this data into the appropriate tables in your database by using BULK INSERT statements.
This import process exhausts all the log space assigned to the database.
This bulk insert operation is not critical because the bulk operation can be restarted without affecting the databaseoperations if it fails.
You must prevent the log file from growing. Data loss is unacceptable.
What should you do to achieve this?
Which Transact-SQL statement should you run?
You are the database administrator of your company. The network contains an instance of SQL Server 2008. Yourun the following Transact-SQL statements to create a table named
PartnerInfo and a spatial index on the PartnerLocation column in the PartnerInfo table in a database named Partners :
CREATE TABLE PartnerInfo( id int primary key, PartnerLocation geography);
CREATE SPATIAL INDEX SI_PartnerInfo_PartnerLocation
ON PartnerInfo(PartnerLocation)
WITH (BOUNDING_BOX = (0, 0, 300, 100),
GRIDS = (LEVEL_3 = MEDIUM));
You want to rebuild the spatial index to change the grid density to high. You want to ensure that the table and itsassociated indexes are not available for queries while rebuilding the spatial index. Which Transact-SQL statement should you run?
Which statements are true for online indexes?
You are the database administrator for your company and manage all the SQL Server 2008 databases of thecompany.
A production database named Prod1 contains all the product and sales data for your company.
The Prod_details table contains detailed data for all of the products manufactured by your company and is frequentlyaccessed by the employees of the company.
Most of the employees’ queries include the Prod_id column of the Prod_details table in the WHERE clause.
You create an online index on the Prod_id column by using the following statement:
CREATE INDEX Prod_id_indx
ON Prod_details (Prod_id) WITH (ONLINE = ON);
You must understand the restrictions that apply to an online index. Which statements are true for online indexes? (Choose all that apply.)